Google Sheet AI Content Generation

code.gs

// This function runs automatically when the Google Sheet is opened.
function onOpen() {
  // Obtain the user interface of the Google Spreadsheet.
  var ui = SpreadsheetApp.getUi();
  
  // Create a custom menu titled 'AI Content Generation' in the Spreadsheet's menu bar.
  ui.createMenu('AI Content Generation')
      // Add a menu item 'Generate All Values' that when clicked, will run the function 'generateAllValues'.
      .addItem('Generate All Values', 'generateAllValues')
      // Add another menu item 'Update Missing Only' that when clicked, will run the function 'updateMissingValues'.
      .addItem('Update Missing Only', 'updateMissingValues')
      // Commit the changes to the user interface and display the custom menu.
      .addToUi();
}

// This function generates bullet points for all rows.
function generateAllValues() {
  // Get the active sheet in the current Google Spreadsheet.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Get values from column A starting from the second row till the last filled row.
  var data = sheet.getRange("A2:A" + sheet.getLastRow()).getValues();
  
  // Loop through each row of data.
  for (var i = 0; i < data.length; i++) {
    // Extract the industry from the current row.
    var industry = data[i][0];
    // Fetch the bullet points for the given industry.
    var bulletPoints = getBulletPoints(industry);
    // Update the cell in column B of the current row with the generated bullet points.
    sheet.getRange(i+2, 2).setValue(bulletPoints);
  }
}

// This function generates bullet points only for rows where column B is empty.
function updateMissingValues() {
  // Get the active sheet in the current Google Spreadsheet.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Get values from columns A and B starting from the second row till the last filled row.
  var data = sheet.getRange("A2:B" + sheet.getLastRow()).getValues();
  
  // Loop through each row of data.
  for (var i = 0; i < data.length; i++) {
    // Extract the industry and the existing value in column B from the current row.
    var industry = data[i][0];
    var existingValue = data[i][1];
    
    // Check if the cell in column B of the current row is empty.
    if (!existingValue) {
      // Fetch the bullet points for the given industry.
      var bulletPoints = getBulletPoints(industry);
      // Update the cell in column B of the current row with the generated bullet points.
      sheet.getRange(i+2, 2).setValue(bulletPoints);
    }
  }
}

// This function contacts the OpenAI API to generate bullet points for a given industry.
function getBulletPoints(industry) {
  // Define the API endpoint.
  var url = "https://api.openai.com/v1/chat/completions";
  
  // Set the headers required for the API call. Make sure to replace 'YOUR_API_KEY' with your actual OpenAI API key.
  var headers = {
    "Content-Type": "application/json",
    "Authorization": "Bearer YOUR_API_KEY"
  };

  // Define the payload to be sent in the request body. 
  var payload = {
    "model": "gpt-4",
    "messages": [
      {
        "role": "system",
        "content": "You are an AI Content generation assistant. I need you to generate 5 bullet points on why a business you should business in a given industry."
      },
      {
        "role": "user",
        "content": industry
      }
    ],
    "temperature": 1,
    "max_tokens": 256,
    "top_p": 1,
    "frequency_penalty": 0,
    "presence_penalty": 0
  };

  // Set the request options including the method, headers, and payload.
  var options = {
    "method": "post",
    "headers": headers,
    "payload": JSON.stringify(payload)
  };

  // Send the request to the OpenAI API.
  var response = UrlFetchApp.fetch(url, options);
  // Parse the API's JSON response.
  var jsonResponse = JSON.parse(response.getContentText());

  // Check if the response contains choices and return the content of the first choice.
  if (jsonResponse.choices && jsonResponse.choices.length > 0) {
    return jsonResponse.choices[0].message.content;
  } else {
    // If no content was generated, return an error message.
    return "Unable to generate content";
  }
}