How to Connect ChatGPT with Google Sheets using App Script: A Google Apps Script Tutorial

 function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Chatgpt AI')
    .addItem('Generate Answers', 'generateAnswersForQuestions')
    .addToUi();
 
  createHeader();
}
function createHeader() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var headers = ["Question", "Answer"];
  var headerRange = sheet.getRange('A1:B1');
  headerRange.setValues([headers]);
 
  // Apply bold font and set font size
  headerRange.setFontWeight("bold");
  headerRange.setFontSize(12); // Change the font size as needed
}






function generateAnswersForQuestions() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const questionsRange = sheet.getRange('A2:A' + lastRow); // Fetch questions from column A
  const questions = questionsRange.getValues();
  const responsesRange = sheet.getRange('B2:B' + lastRow); // Generate responses in column B
  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(); // Trim any extra spaces
        sheet.getRange('B' + (index + 2)).setValue(generatedResponse); // Set response in column B
      } else {
        Logger.log('Failed to get a response from ChatGPT for question: ' + question);
      }
    } else {
      Logger.log('Response already exists for question: ' + question);
    }
  });
}


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;
  }
}