Automate Google Sheets Like a Pro with AI

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

  createHeader();
  checkApiKey();
}

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

// Show sidebar for entering API key
function showSidebar() {
  const html = HtmlService.createHtmlOutputFromFile('Sidebar')
    .setTitle('Set API Key')
    .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}

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

// Save API key to user properties
function saveApiKey(apiKey) {
  PropertiesService.getUserProperties().setProperty('OPENAI_API_KEY', apiKey);
}

// Generate answers for questions in column A
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) {
        sheet.getRange('B' + (index + 2)).setValue(response.trim());
      } else {
        Logger.log('Failed to get a response for question: ' + question);
      }
    } else {
      Logger.log('Response already exists for question: ' + question);
    }
  });
}

// Fetch response from ChatGPT
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;
  }
}