Automate Google Sheets Like a Pro with AI

Copy Code

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('OUTRIGHT')
    .addItem('Generate Answers', 'generateAnswersForQuestions')
    .addItem('Set API Key', 'showSidebar')
    .addToUi();
 
  createHeader();
  checkApiKey();
}


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 showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Sidebar')
    .setTitle('Set API Key')
    .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}


function checkApiKey() {
  var apiKey = PropertiesService.getUserProperties().getProperty('OPENAI_API_KEY');
  if (!apiKey) {
    showSidebar();
    SpreadsheetApp.getUi().alert('Please set your API key using the sidebar.');
  }
}


function saveApiKey(apiKey) {
  PropertiesService.getUserProperties().setProperty('OPENAI_API_KEY', apiKey);
}


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 = PropertiesService.getUserProperties().getProperty('OPENAI_API_KEY');
  if (!apiKey) {
    Logger.log('API key not set.');
    return null;
  }


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