Get ChatGPT in Google Sheets for Practically Free

I’m a huge geek.

I’m also so very pumped about AI (until it takes over everything and makes me listen to Eiffel 65 - Blue (Da Ba Dee) on an infinate loop because I spent all of GPT-3 making it write Haikus).

Since I practically live in Google Sheets, I was very interested when I saw plugins start to pop up to let you integrate AI into Google Sheets or Excel…But then I realized that all I need to do is get ChatGPT to write a Google Apps Script to let me call the OpenAI API myself. So I did.

And yes, I know that Claude has a plugin for this. And that Google Workspace can get Gemini integrated. But sometimes it’s fun to just build, amiright?

The result is amazing for anyone who also lives in Google Sheets, able to send ranges of data (ie, tables) or individual cells with a prompt and get a response back in seconds.

My personal use cases are mostly around data analysis, at-scale categorization, and a dab of helping create concepts or ideas for content creation. There’s way more potential - analytics, translations, real-time NLP data querying in dashboards, whatever. Like ChatGPT as a whole, it’s all about finding those use cases.

Of course, this isn’t a new idea and I didn’t reinvent the wheel. This is just an easy and incredibly cheap way to do it yourself. Also, as a caveat…

The end result is that you can highlight a table or cell, throw in:

=GPT(INPUT, PROMPT) and get a response from any OpenAI model you like.

It takes five minutes to set up…so let’s get to it.

Getting Started with ChatGPT for Google Sheets

What You Need

• A paid OpenAI API. We’re talking less than pennie per call. A ChatGPT account won’t do it; register at OpenAIs Platform site (not via ChatGPT).
• This Google Sheet with the code below pasted in Google App Scripts to get OpenAI’s ChatGPT in Google Sheets
• A use case

Getting The ChatGPT Google Sheet Script Working

1. First, copy your API key from OpenAI’s platform section

2. Make a copy of this sheet into your own Google Drive. First time doing it? Just click on “File > Make a Copy”.

   

3. Add your API key into the Settings tab in cell B2. From this point on, do not share this sheet; people will be able to take advantage of your API key.

   

4. All set? Great, now you just need to tell Google you let it run that script. Click in Extensions > Apps Scripts

5. You should see code there. Make sure it’s not nefarious by throwing it in ChatGPT and asking what it does. Click save and run. You’ll be asked to give it permission to run. That’s a good thing. Say yes. Feels good to go with the flow, right?

How To Use ChatGPT in Google Sheet

Now go back to the Google Sheet and just use the formula =GPT(INPUT,PROMPT). That’s it. Cool, right? If you’re column A has company names in it, beginning in A2, you can try:

=GPT(A2,"My ideal customer base focuses on import and export. Is this a relevant company").

And then drag that B2 cell down to autopopulate.

This works for ranges too, so tables are fair game. For example, if you have data in a table in A1:F8 and you want it to find your worst month as a whole, write in any random cell:

=GPT(A1:F8,"What was the worst month for the business?").

Run into a problem? Copy the script into ChatGPT and ask it to fix it for you. I certainly can’t help.

Key Links and Code for ChatGPT in Google Sheets

Links

• Grab the full Google Sheet here
• You can see a quick video demo of this here (and give me a follow on LinkedIn while you’re at it!)
• Create your OpenAI key here

The Google Sheet Code for ChatGPT

/*

Made by ChatGPT with some direction from Eytan Buchman (buchman.co.il)

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

*/

function GPT(rangeOrInput, prompt) {
  try {
    var context;
    
    if (isRange(rangeOrInput)) {
      var sheet = SpreadsheetApp.getActiveSpreadsheet();
      var range = sheet.getRange(rangeOrInput);
      var values = range.getDisplayValues();  // Use getDisplayValues() to get the results of formulas
      
      if (values.length > 1 || values[0].length > 1) {
        context = flattenArray(values);
      } else {
        context = values[0][0];
      }
      
      if (context === null || context === undefined || context === "") {
        throw new Error('The selected range is empty. Please select a valid range.');
      }
    } else {
      context = rangeOrInput;
    }
    
    var result = completeMe(context, prompt);
    return result;
  } catch (error) {
    Logger.log('Error: ' + error.message);
    return 'Error: ' + error.message;
  }
}

function isRange(input) {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    sheet.getRange(input);
    return true;
  } catch (e) {
    return false;
  }
}

function completeMe(context, input) {
  var messages = [];
  messages.push({"role": "user", "content": String(context)});
  messages.push({"role": "user", "content": String(input)});
  
  return makePostRequest(messages);
}

function flattenArray(arr) {
  return arr.reduce((acc, val) => acc.concat(val), []).join(' ');
}

function getSetting(setting){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Settings');
  var values = sheet.getRange('A:C').getDisplayValues();  // Use getDisplayValues() to get the results of formulas
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] == setting) {
      return values[i][1];
    }
  }
  return null;
}

function makePostRequest(messages) {
  var url = 'https://api.openai.com/v1/chat/completions';
  var payload = {
    "model": getSetting('model'),
    "messages": messages,
    "temperature": parseFloat(getSetting('temperature')),
    "max_tokens": parseInt(getSetting('max_tokens')),
  };
  
  var options = {
    'method': 'post',
    'payload': JSON.stringify(payload),
    'headers': {
      'Authorization': 'Bearer ' + getSetting('api_key'),
      'Content-Type': 'application/json'
    },
    'muteHttpExceptions': true
  };

  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());
  
  if (response.getResponseCode() !== 200) {
    Logger.log('Error: ' + data.error.message);
    return 'Error: ' + data.error.message;
  }
  
  return data.choices[0].message.content;
}

This isn’t just a party trick.

Off the top of my head, here are some practical applications that may help.

1. Classifying Data: Quickly categorize large datasets, such as company lists for trade shows, by simply dragging a formula across cells.
2. Personalizing Emails: Automate email personalization based on specific data points, saving time and ensuring consistency.
3. Generating Content idea: Create LinkedIn posts or other content ideas from keywords, perfect for maintaining a content calendar.
4. Advanced Data Handling: Use formulas to split data into multiple columns or generate queries from cell ranges for direct analysis. This is cool for analyzing tables of data on the fly.

I’m sure you can find way more.

And if you’ve enjoyed this, share it with a friend or drop your email to get updates like this in your inbox.