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; } }
Automate Google Sheets Like a Pro with AI
Published: August 14, 2024
Updated Date : August 14, 2024
Author
Editorial Team
Outrightcrm.com