Google Apps Script: ChatGPT Integration for Spreadsheets

This script creates a custom menu in Google Sheets that allows users to generate answers to questions using ChatGPT. Let’s break down each function and explain what it does.

Function: onOpen()

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Chatgpt AI')
    .addItem('Generate Answers', 'generateAnswersForQuestions')
    .addToUi();
 
  createHeader();
}

This function runs automatically when the spreadsheet is opened:

1. It gets the user interface of the spreadsheet.
   
   
2. Creates a new menu called “Chatgpt AI”.
   
   
3. Adds a menu item called “Generate Answers” that will run the generateAnswersForQuestions function when clicked.
   
   
4. Adds this new menu to the spreadsheet’s menu bar.
   
   
5. Calls the createHeader() function to set up the spreadsheet headers.

Function: createHeader()

function createHeader() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var headers = ["Question", "Answer"];
  var headerRange = sheet.getRange('A1:B1');
  headerRange.setValues([headers]);
 
  headerRange.setFontWeight("bold");
  headerRange.setFontSize(12);
}

This function sets up the headers for the spreadsheet:

1. Gets the active sheet in the spreadsheet
   
   
2. Creates an array with two headers: “Question” and “Answer”.
   
   
3. Selects the range A1:B1 (first two cells in the first row).
   
   
4. Sets the values of these cells to the headers.
   
   
5. Makes the headers bold and sets the font size to 12.

Function: generateAnswersForQuestions()

function generateAnswersForQuestions() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const questionsRange = sheet.getRange('A2:A' + lastRow);
  const questions = questionsRange.getValues();
  const responsesRange = sheet.getRange('B2:B' + lastRow);
  const existingResponses = responsesRange.getValues();

  questions.forEach((question, index) => {
    if (question[0] && !existingResponses[index][0]) {
      const prompt = `Generate a response for the following question: "${question}"`;
      const response = getChatGPTResponse(prompt);

      if (response) {
        const generatedResponse = response.trim();
        sheet.getRange('B' + (index + 2)).setValue(generatedResponse);
      } else {
        Logger.log('Failed to get a response from ChatGPT for question: ' + question);
      }
    } else {
      Logger.log('Response already exists for question: ' + question);
    }
  });
}

This function generates answers for the questions in the spreadsheet:

1. Gets the active sheet and finds the last row with content.
   
   
2. Retrieves all questions from column A (starting from A2).
   
   
3. Retrieves existing responses from column B (starting from B2).
   
   
4. For each question:

If there’s a question and no existing answer:

• Creates a prompt for ChatGPT.
  
  
• Calls getChatGPTResponse() to get an answer.
  
  
• If successful, trims the response and adds it to column B.
  
  
• If unsuccessful, logs an error message.

If there’s already an answer, it logs a message saying so.

Function: getChatGPTResponse(prompt)

function getChatGPTResponse(prompt) {
  const apiKey = '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';
  const url = 'https://api.openai.com/v1/chat/completions';
  const payload = {
    "model": "gpt-3.5-turbo",
    "messages": [
      { "role": "system", "content": "I am your lovely assistant" },
      { "role": "user", "content": prompt }
    ]
  };

  const options = {
    'method': 'post',
    'payload': JSON.stringify(payload),
    'headers': {
      'Content-Type': 'application/json',
      'Authorization': 'Bearer ' + apiKey
    }
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const data = JSON.parse(response.getContentText());
    return data.choices[0].message.content.trim();
  } catch (e) {
    Logger.log('Error: ' + e);
    return null;
  }
}

This function sends a request to the ChatGPT API and returns the response:

1. Sets up the API key (Note: This should be kept secret and not shared publicly).
   
   
2. Defines the API endpoint URL.
   
   
3. Creates a payload with the model to use and the messages (system message and user prompt).
   
   
4. Sets up the options for the API request, including headers and the method.
   
   
5. Tries to send the request to the API:

• If successful, it parses the response and returns the generated text.
  
• If there’s an error, it logs the error and returns null.

Conclusion

This script creates a user-friendly way to use ChatGPT in Google Sheets. Users can input questions in column A, then use the custom menu to generate answers in column B. The script checks for existing answers to avoid duplicating work and handles errors gracefully.