Я использую Google Sheet как базу данных. С помощью ChatGPT написал скрипт, который создает уроки в Tutor LMS и результат записывает в Google sheets. Уроки создаются, но не обновляется Google Sheet, в котором должны проставляться ID уроков. Т.е. нужно исправить обновление Topic ID в Google Sheet
Документ в котором должно обновляться: https://docs.google.com/spreadsheets/d/1zc7Fcl7lV7G5vpghQEBV2vbRldPh0xMh0OQx-ahuuqs/edit?usp=sharing вкладка Topics
Вот скрипт root@Ubuntu-2204-jammy-amd64-base ~/sh/course_dev # cat 4_yodo_create_topics.py
import json
import requests
import argparse
import base64
from google.oauth2 import service_account
from googleapiclient.discovery import build

def get_google_sheets_data(sheet_id, sheet_name, credentials_file):
try:
credentials = service_account.Credentials.from_service_account_file(credentials_file)
service = build('sheets', 'v4', credentials=credentials)
sheet = service.spreadsheets()
range_name = f'{sheet_name}!A2:A'
result = sheet.values().get(spreadsheetId=sheet_id, range=range_name).execute()
values = result.get('values', [])
print(f"Fetched data from Google Sheets: {values}") # Debug output
return values
except Exception as e:
print(f"Error fetching Google Sheets data: {e}")
return []

def update_google_sheets_data(sheet_id, sheet_name, values, credentials_file):
try:
credentials = service_account.Credentials.from_service_account_file(credentials_file)
service = build('sheets', 'v4', credentials=credentials)
sheet = service.spreadsheets()

# Only update the 'B' column with the Topic IDs
body = {
'values': values
}
print(f"Updating Google Sheets with values: {body}") # Debug output
range_to_update = f'{sheet_name}!B2:B{len(values) + 1}' # Specify the range to update
result = sheet.values().update(
spreadsheetId=sheet_id, range=range_to_update,
valueInputOption="RAW", body=body).execute()
print(f"Update result: {result}") # Debug output
return result
except Exception as e:
print(f"Error updating Google Sheets data: {e}")
return None

def get_existing_topics(api_url, consumer_key, consumer_secret, course_id):
try:
url = f"{api_url}/courses/{course_id}/topics"
auth = f"{consumer_key}:{consumer_secret}"
headers = {
'Content-Type': 'application/json',
'Authorization': 'Basic ' + base64.b64encode(auth.encode()).decode('utf-8')
}
response = requests.get(url, headers=headers)
response.raise_for_status()
topics = {topic['name']: topic['id'] for topic in response.json().get('data', [])}
print(f"Fetched existing topics: {topics}") # Debug output
return topics
except requests.exceptions.RequestException as e:
print(f"Error fetching existing topics: {e}")
return {}

def create_tutor_topic(api_url, consumer_key, consumer_secret, course_id, topic_title, topic_author):
try:
url = f"{api_url}/topics"
auth = f"{consumer_key}:{consumer_secret}"
headers = {
'Content-Type': 'application/json',
'Authorization': 'Basic ' + base64.b64encode(auth.encode()).decode('utf-8')
}
payload = {
"topic_title": topic_title,
"topic_course_id": course_id,
"topic_author": topic_author
}

response = requests.post(url, headers=headers, json=payload)
response.raise_for_status()

if response.status_code == 201:
data = response.json()['data']
print(f"Topic created: {data}") # Debug output
return data
elif response.status_code == 200 and response.json().get('code') == 'tutor_create_topic':
data = response.json()['data']
print(f"Topic creation (code 200): {data}") # Debug output
return data
else:
error_message = f"Error creating topic: {response.status_code} - {response.text}"
print(error_message)
return error_message
except requests.exceptions.RequestException as e:
error_message = f"Error creating topic: {e}"
print(error_message)
return error_message

def main(config_file, course_id):
try:
with open(config_file) as file:
config = json.load(file)
except Exception as e:
print(f"Error reading config file: {e}")
return

api_url = config['tutor_lms']['api_url']
consumer_key = config['tutor_lms']['consumer_key']
consumer_secret = config['tutor_lms']['consumer_secret']
topic_author = config['tutor_lms']['user_id']
sheet_id = config['google_sheets']['sheet_id']
sheet_name = 'Topics' # Name of the sheet
credentials_file = config['google_sheets']['credentials_file']

topics = get_google_sheets_data(sheet_id, sheet_name, credentials_file)

if not topics:
print('No data found.')
return

existing_topics = get_existing_topics(api_url, consumer_key, consumer_secret, course_id)
updated_values = []
topic_ids = []

for row in topics:
topic_title = row[0]
if topic_title in existing_topics:
topic_id = existing_topics[topic_title]
updated_values.append([topic_title, topic_id])
topic_ids.append([topic_id])
print(f"Topic '{topic_title}' already exists with ID: {topic_id}")
else:
topic_id = create_tutor_topic(api_url, consumer_key, consumer_secret, course_id, topic_title, topic_author)
if isinstance(topic_id, int):
updated_values.append([topic_title, topic_id])
topic_ids.append([topic_id])
print(f"Topic '{topic_title}' created with ID: {topic_id}")
else:
updated_values.append([topic_title, ""])
topic_ids.append([""])
print(topic_id)

print(f"Updated values: {updated_values}") # Debug output
print(f"Topic IDs: {topic_ids}") # Debug output
update_google_sheets_data(sheet_id, sheet_name, topic_ids, credentials_file)

if __name__ == '__main__':
parser = argparse.ArgumentParser(description='Create topics in Tutor LMS from Google Sheets')
parser.add_argument('--config_file', type=str, required=True, help='Path to the configuration file')
parser.add_argument('--course_id', type=int, required=True, help='ID курса, к которому будут добавлены темы')

args = parser.parse_args()

main(args.config_file, args.course_id)