Undefined error when trying to connect ChatGPT to Google Sheets Data

As the title says – I am trying to use ChatGPT to connect to Google Sheets via Google App Script. I have gotten many errors along the way, but now I am getting a vague “undefined” error and I’m not sure what to do.

For context, I am not a coder and I am just following a youtube video. However, the code in the video is a little outdated and it’s not working so I need to make edits. I’ve tried updating it to be aligned with newer Open AI documentation, but I’m still unable to reproduce the results.

This is my code so far:

const g = {
  folderId: '123456789'
};

function createSummaries() {
  const scriptProps = PropertiesService.getScriptProperties();
  g.apiKey = scriptProps.getProperty('OpenAIKey');
  if (!g.apiKey) {
    throw new Error('ChatGPT API Key script property is missing');
  }
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Test');
  const data = sh.getDataRange().getValues();
  data.splice(0, 1);
  data.forEach((row, i) => {
    if (!row[0]) {
      const name = row[2];
      const features = row.slice(3, row.length).join(' ');
      const text = callChatGPT(features);
      console.log(text);
    }
  });
}

function callChatGPT(features) {
  const apiUrl = 'https://api.openai.com/v1/chat/completions';
  const options = {
    method: 'post',
    headers: {
      Authorization: `Bearer ${g.apiKey}`,
      'Content-Type': 'application/json',
    },
    muteHttpExceptions: true,
    payload: JSON.stringify({
      model: 'gpt-3.5-turbo',
      messages: [
        { role: 'system', content: 'You are a helpful assistant.' },
        { role: 'user', content: `Write a real-estate property overview using the following property features: ${features}` }
      ],
      max_tokens: 150,
      temperature: 0.7,
      top_p: 1.0,
      n: 1,
      stop: "\n"
    }),
  };

  const response = UrlFetchApp.fetch(apiUrl, options);
  const content = response.getContentText();
  
  // Check for successful response status codes (200-299)
  const responseCode = response.getResponseCode();
  if (responseCode >= 200 && responseCode < 300) {
    const jsn = JSON.parse(content);
    if (!jsn.choices || jsn.choices.length === 0) {
      console.log(jsn);
      return null;
    }
    return jsn.choices[0].text;
  } else {
    console.log(content);
    return null;
  }
}here

Any advice or direction would be appreciated, thank you!

Its been some time since I messed with google sheets but it looks like you are trying to open the sheet and read out the values and grab them?

It might be better to just download it as a csv file and then you could read it into an array and send the variable to ChatGPT in your script (this is what I do for csv files).

1 Like