How To Connect ChatGPT API’s To Google Sheets

We’re going to walk through an example of how you can use ChatGPT to automate generating metadata at scale for a site. This is an extremely exciting process because you could give a poorly optimized site a baseline level of optimization recommendations in 30 minutes.

In this example, I’m going to create bulk title tags for the Dress Barn site. However, you could use it to create meta descriptions or H1 tags as well.

Below you can find the steps to do this!

1. Perform A Crawl Of Your Site

Using Screaming Frog, perform a crawl of the site that you’re looking to optimize. Export the crawl and add it to a Google Sheet.

2. Add A Target Keyword & Suggested Title Tag Columns

To get the most out of this function, you’ll want to ensure that you have a column for each URL that specifies the “Target Keyword”. We’ll include this in the prompt for ChatGPT when asking it to give optimization recommendations for a given page. If you don’t have this, oftentimes the H1 tag can work in a site that has some base-level of optimizations.

You’ll also want to add a column for your “Suggested [Metadata]” from ChatGPT. This is where ChatGPT’s recommendations will go. Since we’re using it to generate title tags, I’ll add a “Suggested Title Tags” column.

3. Add The GET_CHATGPT Apps Script

You’ll need to create a new function called GET_CHATGPT to connect Google Sheets to the ChatGPT API.

In Google Sheets navigate to Tools > Extensions > Apps Script. Copy and paste the below function into the Code.js block. Be sure to replace [Your ChatGPT API Key] with your ChatGPT API Key.

function callChatGptApi(prompt) {
  var url = 'https://api.openai.com/v1/chat/completions';
  var options = {
    'method' : 'post',
    'headers': {
      'Authorization': 'Bearer [Your ChatGPT API Key]',
      'Content-Type': 'application/json'
    },
    'payload' : JSON.stringify({
      'model': 'gpt-3.5-turbo',
      'messages': [{'role': 'user', 'content': prompt}]
    })
  };
  var response = UrlFetchApp.fetch(url, options);
  return JSON.parse(response.getContentText());
}

/**
* Sends a ChatGPT Prompt and gets a response using OpenAI API.
*
* @param {string} cell The prompt to send to chatGPT.
* @customfunction
*/
function GET_CHATGPT(cell) {
  var prompt = cell;
  var response = callChatGptApi(prompt);
  return response['choices'][0]['message']['content'];
}

Click “Save Project” and you’re done with this step!

4. Add Your Prompt To Your Google Sheet

Now, you’ll want to add the prompt that you want to feed into ChatGPT in the Google Sheet that you’re working out of it. In this example, we’ve added the “Prompt Start” to cell C2. We filled this in with the prompt:

“You’re an SEO expert helping provide recommendations for a page on a [topic] website with the information below.”

Next, you’ll use a string of Concatenate commands in order to dynamically pull this data into your feed. We’ll provide ChatGPT with:

1. The Prompt Start ($B$1,“)
2. URL ( “,$A$3,“: “,A4,“)
3. Title Tag (“,$B$3,“: “,B4,“)
4. Target Keyword ( Target Keyword: “,D4,“)
5. A request for a Suggested Title Tag (What is a “,$C$3,”Reply with just the “,$C$3)

“You’re an SEO expert helping provide recommendations for a page on a Women’s Fashion Website with the information below.
URL: https://dressbarn.com/collections/absolute-angel-1
Title Tag: Women’s Inner Beauty – Dressbarn
Target Keyword: Women’s Inner Beauty

What is a Suggested Title Tag

Reply with just the Suggested Title Tag”

5. Call The GET_ChatGPT Function Into Sheets

Next, you’re going to call the GET_ChatGPT function into Google Sheets. This will send the prompt to ChatGPT to give you metadata recommendations for a given row.

Go to the first empty row of your “Suggested Title Tag” column. Then enter =GET_CHATGPT and copy/paste the Concatenate function from above:

=GET_CHATGPT(Concatenate($B$1,””,$A$3,”: “,A4,””,$B$3,”: “,B4,”Target Keyword: “,D4,”What is a “,$C$3,” Reply with just the “,$C$3))

The relative cell references are the URL (A4), Title Tag (B4), and Target Keyword (D4). Be sure those match the appropriate cells in your references.

Now hit enter. You’ll know it’s worked when you see metadata populate your row!

Finally, drag to autofill the rest of your cells to populate with metadata! You can use this for title tags, meta descriptions and H1 tags!

Other Notes

• ChatGPT’s API comes at a very small cost to run per row. Be sure to test the costs before running on large datasets.
• Be careful making changes to referencing cells. Any changes to a cell reference will cause ChatGPT to re-run the call (thus additional expense). Once you have everything finalized you might consider copying the data and pasting as “Values Only” to prevent additional API calls. You could possibly accidentally re-run thousands of API calls at once.
• Test your prompt first on a small number of rows before running in bulk. This will ensure that you’re getting a consistent output before running on large datasets.

About Chris Long

Chris Long is the VP of Marketing at Go Fish Digital. With 10+ years of SEO experience, Chris works unique problems and advanced search situations to help clients improve organic traffic through a deep understanding of Google's algorithm and web technology. Chris has advised on the search strategies for clients such as GEICO, Adobe & The New York Times. He is a contributor for Moz, Search Engine Land, and Search Engine Journal. He is also a speaker at industry conferences such as MozCon, SMX East, BrightonSEO and more.