Build an AI that generates content from anything using Google sheets

Johan Salo

Published in

Bootcamp

4 min readMar 16, 2023

A quickguide to create AI prompts (same as ChatGPT) in Google sheets using OpenAI API.

Google Sheet ❤ OpenAI

Introduction

🤖 This step-by-step guide will help set up auto-generated AI content in Google Sheets using OpenAI.
Whenever a cell is updated in Google sheets, Google Apps Scripts sends a prompt to an OpenAI model and get it back inserted in a cell, and you dont need Microsoft Azure to do this.
Why you need this? You can generate high-quality content fast and in a shared document that automatically converts or translates UX research data, or any text that needs to be rephrased, summarised and more without any effort.

First I will show how to set this up, and then show you the script you need to automate the content generation process.

What is OpenAI

OpenAI was founded in 2015 by Elon Musk among others. The company was established to create an open-source AI consortium dealing with machine learning, natural language processing, robotics, and more. OpenAI is best known for Dall-E and more recently, ChatGPT, which was released at the end of 2022.

Additionally, OpenAI provides a service called Playground where most of their models are available in a more advanced environment, including ChatGPT. Recently, Microsoft acquired OpenAI, making it less open and integrating its features into Microsoft Azure and Bing services.

In this example, we will not be using Azure. Instead, we will use the OpenAI API to send text from a cell inside Google Sheets and back again, which anyone can do with a bit of copy-pasting. Let’s dive in!

Setting up OpenAI API and Google Sheets

Create an account at https://platform.openai.com/signup.
Create a new secret OpenAI API key and copy it by visiting https://platform.openai.com/account/api-keys

A screenshot on where to find OpenAI API keys — Screenshot on where to find OpenAI API keys

To begin, create a Google Sheet by visiting https://docs.google.com/spreadsheets.

Writing (copy/paste) the AI content generation scripts

Open up the top menu Extensions and click on Apps Script.

Create a new script and name it what you like, for example: “OPENAI_MODEL.gs”

Copy paste the code and paste your OpenAI API key in the constant variable called API_KEY down below. If you would like to use another model then “text-davinci-003” just change it in the constant url variable to something listed here: https://platform.openai.com/docs/models/

👉💾 Press Save in the top menu!

function OPENAI_MODEL(prompt) {
  const API_KEY = 'REMOVE THIS AND PUT YOUR API KEY HERE';
  const url = 'https://api.openai.com/v1/engines/text-davinci-003/completions';

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + API_KEY
    },
    payload: JSON.stringify({
      prompt: prompt,
      max_tokens: 150,
      n: 1,
      stop: null,
      temperature: 1,
    })
  };
  
  const response = UrlFetchApp.fetch(url, options);
  const jsonResponse = JSON.parse(response.getContentText());
  
  if (jsonResponse.choices && jsonResponse.choices.length > 0) {
    return jsonResponse.choices[0].text.trim();
  } else {
    return 'No response received';
  }
}

Also, add another script with the following code that we will use to trigger the prompt:

function atEdit(e) {
  const triggerColumn = 1; 
// The column that contain the cells that will 
// trigger the script. in this case: column A,

  const sheet = e.source.getActiveSheet();
  const editedColumn = e.range.getColumn();

  if (editedColumn === triggerColumn) {
    callOpenAIModel(e.range);
  }
}

function callOpenAIModel(range) {
  const sheet = range.getSheet();
  const editedCell = range.getA1Notation();
  const prompt = "Please summarize the following text in 5 bullets" + range.getValue();
  // Change the prompt text as you like!
  const result = OPENAI_MODEL(prompt);
  sheet.getRange(editedCell).offset(0, 1).setValue(result);
}

👉💾 Press Save in the top menu!

Automating the AI content generation process

One last thing, set up triggers to run the script automatically:

Now we are good to go! Save the trigger and paste a link in one of the cells in the spreadsheet column that you used in the code earlier, after about 5–8 seconds the generated text should appear automagically to the right of that cell.

Text, in this case, a web link, is sent to the OpenAI model, which returns bullet points in the cell to the right.

Conclusion

In summary, the article presents a simple solution to the problem of manual content creation in Google Sheets. It provides a step-by-step guide on how to set up an AI content generation system using OpenAI and Google Sheets, and how to automate the process using Google Apps Script.

Implementing this solution can save you significant amounts of time and effort in your content creation process, and allow you to focus on more important aspects of your work. By leveraging the power of AI, you can produce higher-quality content more efficiently and collaboratively.

This is the first step of implementing an AI content generation system in Google Sheets using OpenAI, and it can be a game-changer for your collaborative content creation process.

Read about how to build your own AI-bot

👉 Read Irina Nik’s guide on how she built an AI that answers questions based on user research data. UX Collective. https://uxdesign.cc/i-built-an-ai-that-answers-questions-based-on-my-user-research-data-7207b052e21c

👉 Unleasing the Power of ChatGPT for designers:https://medium.com/design-bootcamp/unleashing-the-power-of-chatgpt-for-designers-1f2775ecd31c

👉 The future of AI (written in 2021) https://medium.com/@johansalo/the-future-of-ai-this-is-the-way-70537a0b2a06