Hello everyone,
I’ve been struggling with an issue in my Python code for some time now. The script works well in terms of retrieving the assistant’s name, sending the prompt, and displaying the ChatGPT response in my terminal. However, the problem arises when trying to save the response back to my Excel file.
I’m not sure if the issue is due to an incorrect response format or if it’s a structural problem with the code. Could someone please help me figure this out?
Thank you in advance.
Here is the script:
import openpyxl
import os
from openai import OpenAI, AssistantEventHandler
# Define the API key for OpenAI
api_key = os.getenv('OPENAI_API_KEY', '■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■cg9BmsOT')
client = OpenAI(api_key=api_key)
# Load the workbook and sheet
workbook_path = r'C:\Users\rtazi\Desktop\cocon-semantique\Python\scripts\scraping_gpt\main.xlsx'
workbook = openpyxl.load_workbook(workbook_path)
sheet = workbook["chat gpt scraping"]
# Define input and output column indices
input_indices = [1, 5, 8, 11, 14, 17, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47, 50, 53, 56, 59, 62]
output_offset = [2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
output_indices = [input_indices[i] + output_offset[i] for i in range(len(input_indices))]
# Read the names and IDs of the assistants from the specified columns
assistant_indices = [2, 5, 8, 11, 14, 17, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47, 50, 53, 56, 59, 62]
assistant_ids = [sheet.cell(row=1, column=assistant_indices[i]).value for i in range(len(input_indices))]
# Optional: Define a default temperature (adjustable as needed)
default_temperature = 0.2
# EventHandler class to handle output from the assistant
class EventHandler(AssistantEventHandler):
def on_text_created(self, text):
print(f"\nassistant > ", end="", flush=True)
def on_text_delta(self, delta, snapshot):
print(delta.value, end="", flush=True)
def on_tool_call_created(self, tool_call):
print(f"\nassistant > {tool_call.type}\n", flush=True)
def on_tool_call_delta(self, delta, snapshot):
if delta.type == 'code_interpreter':
if delta.code_interpreter.input:
print(delta.code_interpreter.input, end="", flush=True)
if delta.code_interpreter.outputs:
print(f"\n\noutput >", flush=True)
for output in delta.code_interpreter.outputs:
if output.type == "logs":
print(f"\n{output.logs}", flush=True)
# Process each line in the Excel sheet
for row_index in range(3, sheet.max_row + 1):
thread = client.beta.threads.create()
print(f"Created thread for row {row_index}: {thread.id}")
for i, input_col in enumerate(input_indices):
output_col = output_indices[i]
assistant_id = assistant_ids[i]
if sheet.cell(row=row_index, column=output_col).value:
print(f"Value already present at row {row_index}, output column {output_col}. Skipping.")
continue
input_value = sheet.cell(row=row_index, column=input_col).value or ""
if input_col == 1:
next_value = sheet.cell(row=row_index, column=input_col + 1).value
if next_value is not None:
input_value += "\n\n" + str(next_value)
if not input_value.strip():
print(f"Empty input at row {row_index}, column {input_col}. Skipping.")
continue
print(f"\n\nSending prompt for row {row_index}, input column {input_col}:\n\n{input_value}")
if not assistant_id or assistant_id.strip() == "":
print(f"No assistant ID provided for row {row_index}, input column {input_col}. Skipping.")
continue
try:
message = client.beta.threads.messages.create(
thread_id=thread.id,
role="user",
content=input_value
)
with client.beta.threads.runs.stream(
thread_id=thread.id,
assistant_id=assistant_id,
temperature=default_temperature,
event_handler=EventHandler()
) as stream:
for event in stream:
if 'error' in event:
print(f"Error: {event['error']}")
if 'data' in event and 'choices' in event['data']:
response = event['data']['choices'][0]['message']['content']
print(f"Response for row {row_index}, column {output_col}: '{response}'")
if response: # Check if response is not empty or None
sheet.cell(row=row_index, column=output_col).value = response
print(f"Set cell ({row_index}, {output_col}) to: '{response}'")
try:
workbook.save(workbook_path)
print(f"\n \n -------- Workbook saved after updating cell ({row_index}, {output_col}). -------- \n \n")
except Exception as e:
print(f"Failed to save the workbook: {e}")
else:
print(f"No response received to write at ({row_index}, {output_col})")
except Exception as e:
print(f"Error processing row {row_index}, input column {input_col}: {e}")
print("Final save of the workbook.")
try:
workbook.save(workbook_path)
print("Workbook saved.")
except Exception as e:
print(f"Failed to save the workbook on final attempt: {e}")