Send Google Search Console data to ChatGPT

Artificial Intelligence is getting into new business workflows by the day. One way we can increase work productivity, as well as the Search Engine Optimization (SEO) of our websites is by connecting AI with Google Search Console. Let's see how to pull search queries out of GSC and have OpenAI's ChatGPT create blog post titles for these queries – all using Google Apps Script.

Setup

For this tutorial, all you need is a Google Sheet with one tab (I call mine "SEO"). The tab contains three columns: Queries, Impressions, and Titles. We'll populate the first two columns with data from GSC, and then feed the queries into ChatGPT, and write its title suggestions into column C.

To get going with this project, you'll also need to obtain an OpenAI API key and store it in Script Properties. If you don't know how to do that then please read this post.

You also need to declare three OAuth scopes in your Google Apps Script manifest file, which you can access by opening Apps Script from within the Google Sheet. Click on the gear icon, and under General settings, tick the box for Show "appsscript.json" manifest file in editor. Click the code icon, and then click on appsscript.json. Add the following:

  "oauthScopes": [
    "https://www.googleapis.com/auth/webmasters.readonly",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]

Utility functions

Because we are going to be using two different APIs, which will both interact Google Sheet, we can create some utility code that we will use multiple times. In Code.gs, enter:

const g = { sheetName: 'SEO' };

We'll use the "g" global object to hold key pieces of info for us, like the name of our tab inside the sheet. Now let's create a utility function to call a supplied API endpoint and return its response:

function callApi(apiUrl, apiKey, payload) {
  const options = {
    method: 'POST',
    muteHttpExceptions: true,
    headers: { Authorization: 'Bearer ' + apiKey },
    contentType: 'application/json',
    payload: JSON.stringify(payload),
  };
  const resp = UrlFetchApp.fetch(apiUrl, options);
  const jsn = JSON.parse(resp.getContentText());
  return jsn;
}

The function above receives the API url, the API authorization token, and the API payload. It constructs an options object, which includes the supplied payload as a string, to the API. It stores the response in "resp", parses out the JSON object and returns it.

Le'ts write a second utility function to store the API data in the Google Sheet:

function saveToSheet(cols, colNumber) {
  if (cols.length < 1 || cols[0].length < 1)
    throw new Error('Insufficient data to save to sheet');
  g.sh.getRange(2, colNumber, cols.length, cols[0].length).setValues(cols);
  SpreadsheetApp.flush();
}

The function above receives the data as a nested array, as well as the column number from which to enter the data. It uses "g.sh" that we will define momentarily to write the data into the sheet.

Our final utility function is to set the "g" object and invoke the two functions that will handle the APIs:

function automate() {
  g.ss = SpreadsheetApp.getActive();
  g.sh = g.ss.getSheetByName(g.sheetName);
  getSearchConsoleQueries();
  genPostTitles();
}

Get Search Console data

function getSearchConsoleQueries() {
  const url = 'your-domain.com';
  const from = new Date();
  from.setDate(1);
  from.setMonths(from.getMonths() - 1);
  const to = new Date();
  const startDate = Utilities.formatDate(
    from,
    Session.getScriptTimeZone(),
    'YYYY-MM-dd'
  );
  const endDate = Utilities.formatDate(
    to,
    Session.getScriptTimeZone(),
    'YYYY-MM-dd'
  );
  const apiUrl = `https://www.googleapis.com/webmasters/v3/sites/sc-domain:${url}/searchAnalytics/query`;
  const apiToken = ScriptApp.getOAuthToken();
  const payload = {
    startDate,
    endDate,
    dimensions: ['query'],
  };
  const jsn = callApi(apiUrl, apiToken, payload);
  if (!jsn.rows) {
    throw new Error('No Search Console data available for the selected date range.')l
  }
  if (jsn.error) {
    console.error(jsn.error.message);
    return;
  }
  var { rows } = jsn;
  let keys = rows.map((row) => [row.keys[0], row.impressions]);
  // , row.clicks
  keys = keys.sort((a, b) => b[1] - a[1]);
  keys = keys.slice(0, 10);
  saveToSheet(keys, 1);
}

In the above function, set up the "url" variable to point to your own domain name. The function then creates the start and end dates for the period for which we want to get the data. We then set up the apiURL with our own website URL. The security token is generated for us by Apps Script using ScriptApp.getOAuthToken(). We set up the payload to bring back "query"-related data.

Next, we call our utility function supplying to it the necessary API data. We check that we get "rows" back. If we don't then GSC didn't provide data for our date period. Otherwise, we pull out the query texts and impressions, sort them by impression count in a descending order, and take the first 10, which we write to the sheet starting at column 1.

If you enabled the API and ran "automate" then you should see your Google Sheet populated with query data. We can now feed these queries to ChatGPT:

function genPostTitles() {
  const scriptProps = PropertiesService.getScriptProperties();
  const apiToken = scriptProps.getProperty('openAIKey');
  if (!apiToken) {
    throw new Error(`OpenAI API token script property is missing.`);
  }
  const apiUrl = 'https://api.openai.com/v1/chat/completions';
  const terms = g.sh.getRange(2, 1, g.sh.getLastRow(), 1).getValues().flat();
  const content =
    'Generate an SEO-rich blog post title for each of the search queries provided below. ' +
    'Separate the titles with commas. Do not enumerate the titles.' +
    'My search terms are: ' +
    terms;
  const payload = {
    model: 'gpt-3.5-turbo',
    messages: [{ role: 'assistant', content }],
  };
  const jsn = callApi(apiUrl, apiToken, payload);
  // console.log(JSON.stringify(jsn, null, 2));
  if (jsn.error) {
    console.error(jsn.error.message);
    return;
  }
  if (!jsn.choices && jsn.choices.length === 0) {
    return;
  }
  const titlesString = jsn.choices[0].message.content;
  const titles = titlesString.split(',').map((title) => [title]);
  saveToSheet(titles, 3);
}

Above, we pull the OpenAI token from the Properties Service, set up the API URL, and get the queries out of the sheet. We form a prompt that includes our queries. We set the payload with the model and prompt of ChatGPT. We call our function and parse out the results from the response. We convert the text into an array, and save it to our Google Sheet. And that's all there is to it.

Happy SEOing!