Automate SEO analysis with Google Sheets, GSC & ChatGPT API

Automate SEO analysis with Google Sheets + GSC and ChatGPT API

SEO analysis can be time-consuming, but automating the process helps eliminate repetitive tasks and speeds up important website optimizations.

This guide will show you how to build a custom tool using Google Sheets, Google Search Console (GSC), and the ChatGPT API to streamline SEO audits and gain AI-powered insights.

With this tool, you’ll be able to:

Automate GSC data retrieval for faster analysis.
Use AI to generate actionable SEO recommendations.

The plan? You’ll select a URL from a GSC-connected domain, enter your ChatGPT API key, inspect the page, and run an AI-driven analysis – all within Google Sheets.

What SEO analysis are we automating?

Once set up, this tool will allow you to quickly access key SEO data from GSC, including keyword rankings, referring URLs, last crawl date, and indexing status in robots.txt.

ChatGPT enhances the process by analyzing and providing recommendations for:

Title tags.
Meta descriptions.
Heading tags.
Content.
Keyword placement.
Internal linking.
Mobile optimization.
Local SEO.
Schema markup.

While this script won’t cover everything, it delivers detailed page insights in seconds, saving you hours of manual work.

You can then review the recommendations and decide which optimizations to implement.

Sample SEO analysis automation with Google Sheets

8 steps to set up your own GSC custom tool + script

Setting up your script may initially feel overwhelming, but follow each step carefully and copy the script to ensure everything works.

You’ll need a few things to get started:

ChatGPT API key.
Google Sheets.
Google Search Console (with linked properties).
Google Cloud Console data project.

Next, I’ll guide you through my eight-step process to get this “tool” up and running.

Step 1: Create the Google Sheet for your tool

If you have a Google account, this is as simple as following these steps:

Open Google Drive.
Go to the folder where you want to place your tool.
Right-click on the background.
Choose Google Sheets > Blank spreadsheet.

You can rename the Sheet to anything you like, but I chose the following: GSC Custom Tool.

Step 2: Populate your sheet

Populate the sheet by following these steps carefully:

Add the words “Select GSC property” to A1.
Add the words “Select a URL” to A2.

Move to A15 and add “Inspection Results.” Underneath this, in order from A15 to A25, add the following:

URL:
Coverage:
Robots.txt:
Indexing State:
Last Crawled:
Google Canonical:
User Canonical:
Mobile Usability:
Rich Results Eligibility:
Referring URLs:

In D1, you’ll paste your ChatGPT API key. If you don’t have this key, please go to the link above and be sure to get one for this tool to work.

Add “AI Analysis Result” to F1 and “Prompt Sent to ChatGPT” to G1.

Now, we need to merge a few rows in both of these columns. You’ll merge F2 – F30 and G2 – G30 separately, by following these steps:

Select the rows to merge.
Go to Format > Merge cells > Merge vertically.

Repeat this step for the F and G rows you need to merge.

GSC Custom Tool - Google Sheets - Merging cells

Step 3: Create a Google Cloud project

Create a Google Cloud Console data project for the tool.

Once set up, create a new project named “GSC Custom Tool.”

You can do this by going to Select Project > New Project and filling in the information just like in the screenshot above.

Click Create when you’re done naming the project.

Now, let’s connect the Google Search Console API to your project.

Go to the search bar and type “Google Search Console API,” select it, and then hit Enable on the next screen.

We still have a lot to do and will revisit this project shortly.

Step 4: Create an App Script

Integrate Apps Script into your newly created Google Sheets file.

GSC Custom Tool - Google Sheets Apps Script

To do so, open your file, then go to Extensions > Apps Script. Copy and paste the code I’ve created below.

(You can do this by selecting the code inside the window and hitting CTRL + C. Go back into your Apps Script and hit CTRL + V to paste the code in.)

Hit OK > Save project > Run.

Google will prompt you to review all permissions and select the corresponding account with all your Google Search Console data linked.

Dig deeper: 5 Python scripts for automating SEO tasks

Get the newsletter search marketers rely on.

See terms.

Step 5: Add access credentials

Return to your Google Cloud Console to make further adjustments.

Click on Credentials on the left side of the screen:

Google Cloud project - GSC Custom Tool credentials

Click on + Create Credentials at the top of the screen and select OAuth client ID.

Go to Configure Consent Screen and select External. Create the screen and then enter your:

App name.
Support email (linked to the GSC account).
Developer contact information.

Save and continue to the next screen, which says Add or Remove Scopes.

Select Google Search Console API scopes and update. Then, Save and continue.

Add the users you want to grant access to, then click Save and continue.

Step 6: Change to a Google Cloud project for GSC data

Click the hamburger icon and go to Cloud overview > Dashboard.

You want to copy over your Project number, which is on the page.

Google Cloud project - Cloud overview > Dashboard

Navigate to the Project number, select it and copy it using CTRL + C.

Head over to your Google Sheet file and click on the gear icon that says Project Settings.

Paste your project number into the text box and click Set project.

Step 7: Name your Google Apps Script

Add a name for your script to keep things organized.

To do this, go to Project History, click on the Untitled project toward the top of the screen, and enter “GSC Custom Tool.”

Step 8: Edit the manifest file

You’ll now return to your Project Settings and click Show “appsscript.json” in the editor.

Inside the Editor, go to appsscript.json and replace everything in the file with the code below:

{
  "timeZone": "America/New_York",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": [
    "https://www.googleapis.com/auth/webmasters",
    "https://www.googleapis.com/auth/webmasters.readonly",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ],
  "runtimeVersion": "V8"
}

Once you’ve pasted everything in, you’ll go to the Code.js file and paste this code:

// Store the OAuth token and logs in script properties
const scriptProperties = PropertiesService.getScriptProperties();
const OPENAI_URL = "https://api.openai.com/v1/chat/completions";
const SYSTEM_MESSAGE = { role: "system", content: "You are a helpful SEO expert." };


function log(message) {
  Logger.log(message); // Regular Apps Script logging
  const logs = scriptProperties.getProperty('customLogs') || '';
  scriptProperties.setProperty('customLogs', logs + '\n' + message); // Append message to logs
}


function resetLogs() {
  scriptProperties.deleteProperty('customLogs'); // Clear logs for a new execution
}


function getLogs() {
  return scriptProperties.getProperty('customLogs') || 'No logs available.';
}


function fetchOAuthToken() {
  let token = scriptProperties.getProperty('oauthToken');
  if (!token) {
    token = ScriptApp.getOAuthToken();
    scriptProperties.setProperty('oauthToken', token);
    log('OAuth token fetched and stored.');
  }
  return token;
}


function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Search Console')
    .addItem('Authorize GSC', 'promptReauthorization')
    .addItem('Fetch GSC Properties', 'fetchGSCProperties')
    .addItem('Inspect URL', 'inspectUrl') // Add the Inspect URL button
    .addItem('AI Analyze', 'aiAnalyze') // Add the AI Analyze button
    .addToUi();
}


function promptReauthorization() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.alert(
    'Re-authorize Script',
    'Re-authorizing will revoke current permissions and require you to authorize again. Do you want to continue?',
    ui.ButtonSet.YES_NO
  );


  if (response === ui.Button.YES) {
    try {
      scriptProperties.deleteProperty('oauthToken'); // Clear old token
      const token = fetchOAuthToken(); // Fetch and store new token
      log("OAuth Token: " + token);
      ui.alert('Authorization successful. No further action is required.');
    } catch (e) {
      ui.alert('Authorization failed: ' + e.toString());
    }
  } else {
    ui.alert('Re-authorization canceled.');
  }
}


function fetchGSCProperties() {
  resetLogs();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const oauthToken = fetchOAuthToken();


  const sites = getSitesListFromGSC(oauthToken);


  if (!sites || sites.length === 0) {
    SpreadsheetApp.getUi().alert('No GSC properties found. Please ensure you have access to GSC properties.');
    return;
  }


  const siteUrls = ['Select a property'].concat(
    sites.map(site => site.siteUrl).sort()
  );


  sheet.getRange('A1').setValue('Select GSC property').setFontWeight('bold');
  sheet.getRange('B1').setDataValidation(
    SpreadsheetApp.newDataValidation()
      .requireValueInList(siteUrls, true)
      .build()
  );
  sheet.getRange('B1').setValue('Select a property').setFontWeight('bold');
  sheet.setColumnWidth(1, 150);
  sheet.setColumnWidth(2, 350);
}


let isProcessing = false; // Global flag to prevent recursive triggering


function onEdit(e) {
  if (isProcessing) return; // Prevent re-entry during execution
  isProcessing = true; // Set flag to true


  try {
    resetLogs();
    const sheet = e.source.getActiveSheet();
    const range = e.range;


    // Always clear A3:D30 on edits to B1 or B2
    if (range.getA1Notation() === 'B1' || range.getA1Notation() === 'B2') {
      sheet.getRange('A3:D30').clearContent();
      sheet.getRange('F1:G30').clearContent();


      if (range.getA1Notation() === 'B1') {
        const selectedProperty = range.getValue();


        // Clear A2 and set loading state
        sheet.getRange('A2').setValue('Loading results...').setFontWeight('bold');
        sheet.getRange('B2').clearContent();


        if (selectedProperty === 'Select a property') {
          sheet.getRange('A2').clearContent();
          sheet.getRange('B2').clearContent();
          return;
        }


        const oauthToken = fetchOAuthToken();
        const urls = getUrlsForProperty(selectedProperty, oauthToken);


        if (!urls || urls.length === 0) {
          sheet.getRange('A2').setValue('No URLs found').setFontWeight('bold');
          sheet.getRange('B2').clearContent();
          log(`No URLs found for property ${selectedProperty}`);
          return;
        }


        sheet.getRange('A2').setValue('Select a URL').setFontWeight('bold');
        sheet.getRange('B2').setDataValidation(
          SpreadsheetApp.newDataValidation()
            .requireValueInList(['Select a URL'].concat(urls), true)
            .build()
        );
        sheet.getRange('B2').setValue('Select a URL').setFontWeight('bold');
      }


      if (range.getA1Notation() === 'B2') {
        const selectedProperty = sheet.getRange('B1').getValue();
        const selectedUrl = range.getValue();


        if (selectedUrl === 'Select a URL') {
          return;
        }


        sheet.getRange('A3').setValue('Loading keywords...').setFontWeight('bold');


        const oauthToken = fetchOAuthToken();
        const keywords = getTopKeywordsForUrl(selectedProperty, selectedUrl, oauthToken);


        if (!keywords || keywords.length === 0) {
          sheet.getRange('A3').setValue('No keywords found').setFontWeight('bold');
          log(`No keywords found for URL ${selectedUrl}`);
          return;
        }


        // Populate keywords and metrics
        sheet.getRange('A3:D12').clearContent(); // Clear any loading message
        keywords.forEach((keyword, index) => {
          if (index < 10) {
            sheet.getRange(`A${3 + index}`).setValue(keyword.query).setFontWeight('bold');
            sheet.getRange(`B${3 + index}`).setValue(keyword.clicks);
            sheet.getRange(`C${3 + index}`).setValue(keyword.impressions);
            sheet.getRange(`D${3 + index}`).setValue(keyword.ctr);
          }
        });
      }
    }
  } catch (error) {
    log(`Error in onEdit: ${error}`);
  } finally {
    isProcessing = false; // Reset the flag after execution
  }
}


function getApiRequestDetails(selectedProperty) {
  const payload = {
    startDate: getThreeMonthsAgo(),
    endDate: getToday(),
    dimensions: ["page"],
    rowLimit: 100,
    orderBy: [{ fieldName: "clicks", sortOrder: "DESCENDING" }]
  };


  const apiUrl = `https://www.googleapis.com/webmasters/v3/sites/${encodeURIComponent(selectedProperty)}/searchAnalytics/query`;
  return { url: apiUrl, payload: payload };
}


function getSitesListFromGSC(oauthToken) {
  try {
    const url = 'https://www.googleapis.com/webmasters/v3/sites';


    const headers = {
      'Authorization': 'Bearer ' + oauthToken,
      'Content-Type': 'application/json'
    };


    const options = {
      method: 'get',
      headers: headers,
      muteHttpExceptions: true
    };


    const response = UrlFetchApp.fetch(url, options);
    log(`Response Code: ${response.getResponseCode()}`);
    log(`Response Content: ${response.getContentText()}`);


    if (response.getResponseCode() === 200) {
      const json = JSON.parse(response.getContentText());
      return json.siteEntry || [];
    } else {
      throw new Error(`Error fetching data: ${response.getResponseCode()} - ${response.getContentText()}`);
    }
  } catch (e) {
    log(`Error: ${e.toString()}`);
    return [];
  }
}


function getUrlsForProperty(property, oauthToken) {
  try {
    const apiUrl = `https://www.googleapis.com/webmasters/v3/sites/${encodeURIComponent(property)}/searchAnalytics/query`;


    log(`API URL: ${apiUrl}`);
    log(`OAuth Token: ${oauthToken}`);


    const payload = {
      startDate: getThreeMonthsAgo(),
      endDate: getToday(),
      dimensions: ["page"],
      rowLimit: 100,
      orderBy: [{ fieldName: "clicks", sortOrder: "DESCENDING" }]
    };


    log(`Payload: ${JSON.stringify(payload)}`);


    const headers = {
      Authorization: `Bearer ${oauthToken}`,
      "Content-Type": "application/json"
    };


    const options = {
      method: "post",
      contentType: "application/json",
      headers: headers,
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    };


    const response = UrlFetchApp.fetch(apiUrl, options);


    log(`Response Code: ${response.getResponseCode()}`);
    log(`Response: ${response.getContentText()}`);


    if (response.getResponseCode() === 200) {
      const json = JSON.parse(response.getContentText());
      return json.rows ? json.rows.map(row => row.keys[0]) : [];
    } else {
      throw new Error(`Failed to fetch data: ${response.getResponseCode()} - ${response.getContentText()}`);
    }
  } catch (e) {
    log(`Error: ${e.toString()}`);
    return [];
  }
}


function getTopKeywordsForUrl(property, url, oauthToken) {
  try {
    const apiUrl = `https://www.googleapis.com/webmasters/v3/sites/${encodeURIComponent(property)}/searchAnalytics/query`;


    log(`API URL: ${apiUrl}`);
    log(`OAuth Token: ${oauthToken}`);


    const payload = {
      startDate: getThreeMonthsAgo(),
      endDate: getToday(),
      dimensions: ["query"],
      dimensionFilterGroups: [
        {
          filters: [
            {
              dimension: "page",
              operator: "equals",
              expression: url
            }
          ]
        }
      ],
      rowLimit: 10,
      orderBy: [{ fieldName: "clicks", sortOrder: "DESCENDING" }]
    };


    log(`Payload: ${JSON.stringify(payload)}`);


    const headers = {
      Authorization: `Bearer ${oauthToken}`,
      "Content-Type": "application/json"
    };


    const options = {
      method: "post",
      contentType: "application/json",
      headers: headers,
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    };


    const response = UrlFetchApp.fetch(apiUrl, options);
    log(`Response Code: ${response.getResponseCode()}`);
    log(`Response: ${response.getContentText()}`);


    if (response.getResponseCode() === 200) {
      const json = JSON.parse(response.getContentText());
      return json.rows ? json.rows.map(row => ({
        query: row.keys[0],
        clicks: row.clicks,
        impressions: row.impressions,
        ctr: row.ctr
      })) : [];
    } else {
      throw new Error(`Failed to fetch data: ${response.getResponseCode()} - ${response.getContentText()}`);
    }
  } catch (e) {
    log(`Error: ${e.toString()}`);
    return [];
  }
}


function getToday() {
  const today = new Date();
  return today.toISOString().split("T")[0];
}


function getThreeMonthsAgo() {
  const date = new Date();
  date.setMonth(date.getMonth() - 3);
  return date.toISOString().split("T")[0];
}


function inspectUrl() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const url = sheet.getRange('B2').getValue();
  const property = sheet.getRange('B1').getValue();


  // Clear previous inspection results in A15:D30
  sheet.getRange('A15:D30').clearContent();
  sheet.getRange('A15').setValue('Inspecting...').setFontWeight('bold');


  if (!url || url === 'Select a URL') {
    SpreadsheetApp.getUi().alert('Please select a valid URL in cell B2 before inspecting.');
    sheet.getRange('A15').setValue('No URL selected').setFontWeight('bold');
    return;
  }


  const oauthToken = fetchOAuthToken();


  try {
    const result = callUrlInspectionApi(property, url, oauthToken);


    // Extract fields from the response
    const indexStatus = result.indexStatusResult || {};
    const mobileUsability = result.mobileUsabilityResult || {};
    const richResults = result.richResultsInfo || {};
    const referringUrls = indexStatus.referringUrls?.join(', ') || 'None';


    // Populate inspection results in the sheet
    sheet.getRange('A15').setValue(`Inspection Results`).setFontWeight('bold');
    sheet.getRange('A16').setValue(`URL:`).setFontWeight('bold');
    sheet.getRange('B16').setValue(url);


    sheet.getRange('A17').setValue(`Coverage:`).setFontWeight('bold');
    sheet.getRange('B17').setValue(indexStatus.coverageState || 'Unknown');


    sheet.getRange('A18').setValue(`Robots.txt:`).setFontWeight('bold');
    sheet.getRange('B18').setValue(indexStatus.robotsTxtState || 'Unknown');


    sheet.getRange('A19').setValue(`Indexing State:`).setFontWeight('bold');
    sheet.getRange('B19').setValue(indexStatus.indexingState || 'Unknown');


    sheet.getRange('A20').setValue(`Last Crawled:`).setFontWeight('bold');
    sheet.getRange('B20').setValue(indexStatus.lastCrawlTime || 'Not Available');


    sheet.getRange('A21').setValue(`Google Canonical:`).setFontWeight('bold');
    sheet.getRange('B21').setValue(indexStatus.googleCanonical || 'Unknown');


    sheet.getRange('A22').setValue(`User Canonical:`).setFontWeight('bold');
    sheet.getRange('B22').setValue(indexStatus.userCanonical || 'Unknown');


    sheet.getRange('A23').setValue(`Mobile Usability:`).setFontWeight('bold');
    sheet.getRange('B23').setValue(mobileUsability.verdict || 'Unknown');


    sheet.getRange('A24').setValue(`Rich Results Eligibility:`).setFontWeight('bold');
    sheet.getRange('B24').setValue(richResults.verdict || 'Unknown');


    sheet.getRange('A25').setValue(`Referring URLs:`).setFontWeight('bold');
    sheet.getRange('B25').setValue(referringUrls);


    // Log and alert full response for debugging
    const fullResponse = JSON.stringify(result, null, 2);
    log(`Full Inspection Result: ${fullResponse}`);
    //SpreadsheetApp.getUi().alert(`Inspection Completed. Full Response:\n\n${fullResponse}`);
  } catch (error) {
    sheet.getRange('A15').setValue('Inspection Failed').setFontWeight('bold');
    log(`Error inspecting URL: ${error.message}`);
    SpreadsheetApp.getUi().alert(`Error inspecting URL: ${error.message}\n\nLogs:\n${getLogs()}`);
  }
}


function callUrlInspectionApi(property, url, oauthToken) {
  const apiUrl = 'https://searchconsole.googleapis.com/v1/urlInspection/index:inspect';


  const payload = {
    siteUrl: property,
    inspectionUrl: url,
    languageCode: 'en-US'
  };


  const headers = {
    Authorization: `Bearer ${oauthToken}`,
    'Content-Type': 'application/json'
  };


  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: headers,
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };


  log(`API URL: ${apiUrl}`);
  log(`Payload: ${JSON.stringify(payload)}`);


  try {
    const response = UrlFetchApp.fetch(apiUrl, options);
    const responseCode = response.getResponseCode();
    const responseText = response.getContentText();


    log(`Response Code: ${responseCode}`);
    log(`Response Content: ${responseText}`);


    if (responseCode === 200) {
      const jsonResponse = JSON.parse(responseText);


      if (jsonResponse && jsonResponse.inspectionResult) {
        return jsonResponse.inspectionResult;
      } else {
        log(`Unexpected API Response Structure: ${responseText}`);
        throw new Error('Unexpected API response format. "inspectionResult" field is missing.');
      }
    } else {
      log(`Failed API Call: ${responseText}`);
      throw new Error(`Failed to inspect URL. Response Code: ${responseCode}. Response: ${responseText}`);
    }
  } catch (error) {
    log(`Error during API call: ${error}`);
    throw new Error(`Error inspecting URL: ${error.message}`);
  }
}


function callChatGPT(prompt, temperature = 0.9, maxTokens = 800, model = "gpt-3.5-turbo") {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const secretKey = sheet.getRange('D1').getValue().trim(); // Retrieve the OpenAI API key from D1


  if (!secretKey) {
    throw new Error("API Key is missing in cell D1. Please provide a valid OpenAI API key.");
  }


  const payload = {
    model: model,
    messages: [
      SYSTEM_MESSAGE,
      { role: "user", content: prompt }
    ],
    temperature: temperature,
    max_tokens: maxTokens
  };


  const options = {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + secretKey
    },
    payload: JSON.stringify(payload)
  };


  try {
    const response = UrlFetchApp.fetch(OPENAI_URL, options);
    const responseData = JSON.parse(response.getContentText());


    if (responseData.choices && responseData.choices[0] && responseData.choices[0].message) {
      return responseData.choices[0].message.content.trim();
    } else {
      log("Unexpected response format from OpenAI: " + JSON.stringify(responseData));
      return "Sorry, I couldn't process the request.";
    }
  } catch (error) {
    log("Error calling OpenAI API: " + error);
    return "Sorry, there was an error processing your request.";
  }
}


function aiAnalyze() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const url = sheet.getRange('B2').getValue();
  const keywords = sheet.getRange('A3:A12').getValues().flat().filter(Boolean); // Get non-empty keywords
  const inspectionData = sheet.getRange('A16:B20').getValues();


  // Validate input fields
  if (!url || keywords.length === 0 || inspectionData.some(row => row.length < 2 || !row[0].trim() || !row[1].trim())) {
    SpreadsheetApp.getUi().alert("Ensure the following are filled before running AI Analyze:\n- URL in B2\n- Keywords in A3:A12\n- Inspection data in A16:B20");
    return;
  }


  // Prepare the prompt for ChatGPT
  const prompt = `
    Analyze this URL: ${url}
    Also the view-source version from: ${url}
    against these keywords: ${keywords.join(", ")}
    Considering the URL inspection data from Google Search Console:
    ${inspectionData.map(row => `${row[0]}: ${row[1]}`).join("\n")}
    Suggest a short list of specific recommendations on how I can improve the page's SEO. Make sure the recommendations include details such as change this to that, or add something, etc... Be concrete with SEO recommendations.
  `;


  // Display the prompt in G1
  sheet.getRange('G1').setValue("Prompt Sent to ChatGPT").setFontWeight("bold");
  sheet.getRange('G2:G30').clearContent(); // Clear previous content in column G
  sheet.getRange('G2:G30').merge(); // Merge cells G2:G30
  sheet.getRange('G2').setValue(prompt).setVerticalAlignment("top"); // Add the prompt and align to top
  sheet.setColumnWidth(7, 400); // Set column G width to 400px


  // Call ChatGPT API
  const analysisResult = callChatGPT(prompt);


  // Display the result in the spreadsheet (Column F)
  sheet.getRange('F1').setValue("AI Analysis Result").setFontWeight("bold");
  sheet.getRange('F2:F30').clearContent(); // Clear previous content
  sheet.getRange('F2:F30').merge(); // Merge the cells
  sheet.getRange('F2').setValue(analysisResult).setVerticalAlignment("top"); // Add the AI result and align to top
  sheet.setColumnWidth(6, 400); // Set column F width to 400px


  // Log the response
  log("AI Analysis Completed: " + analysisResult);
}

Once done, go back to your sheets, refresh and use the new Search Console > Fetch GSC Properties.

Follow the prompts until it asks you to select the account you’re using and to ultimately select the app you plan on using.

If everything goes well, you can move on to the exciting part of putting it together and running your first SEO analysis using your new script.

Putting our Sheets, GSC and ChatGPT tool together

You’ve done a lot until now, but it’s time to see the tool in action. Here’s how it works:

Go to Search Console > Authorize GSC.

Be sure that you’re using the account to which the domain’s GSC is connected; otherwise, it will not work.

Go to Search Console > Fetch GSC properties.

You’ll now notice that the Select GSC property at B1 on the Sheet is populated. Select the domain you want to analyze and then select the URL from B1 on the Sheet.

Go to Search Console > Inspect URL.

The Inspection Results on the Sheet will populate and offer you a wealth of information about the page, such as the coverage, last crawl date, and more.

Finally, we’ll go back one last time and do the following:

Go to Search Console > AI Analyze.

All of the information in the “AI Analysis Result” will now populate, offering you insights into the page’s key elements and telling you the exact steps that you can take to improve the page.

I don’t recommend following the directions blindly, but they do offer actionable steps that you can follow to strengthen the page.

If you want to change the ChatGPT prompt to add your own custom data, go to cell G2, right underneath the heading that says “Prompt Sent to ChatGPT.”

Bravo! You have a working script that can speed up your SEO analysis and make your day more actionable.

Combining Sheets, GSC and ChatGPT has helped me become more efficient and allows me to spend more time optimizing and less analyzing thousands of pages.

Experiment with the script and find new ways to make it your own.

Dig deeper: 15 AI tools you should use for SEO

source https://searchengineland.com/automate-seo-analysis-with-google-sheets-gsc-chatgpt-api-451306

All about Digital Marketing

Automate SEO analysis with Google Sheets, GSC & ChatGPT API

What SEO analysis are we automating?

8 steps to set up your own GSC custom tool + script