Automate search engine marketing evaluation with Google Sheets, GSC & ChatGPT API

search engine marketing evaluation may be time-consuming, however automating the method helps get rid of repetitive duties and accelerates essential web site optimizations. 

This information will present you how one can construct a customized instrument utilizing Google Sheets, Google Search Console (GSC), and the ChatGPT API to streamline search engine marketing audits and acquire AI-powered insights.

With this instrument, you’ll be capable of:

• Automate GSC information retrieval for quicker evaluation.
• Use AI to generate actionable search engine marketing suggestions.

The plan? You’ll choose a URL from a GSC-connected area, enter your ChatGPT API key, examine the web page, and run an AI-driven evaluation – all inside Google Sheets.

What search engine marketing evaluation are we automating?

As soon as arrange, this instrument will help you shortly entry key search engine marketing information from GSC, together with key phrase rankings, referring URLs, final crawl date, and indexing standing in robots.txt.

ChatGPT enhances the method by analyzing and offering suggestions for:

Whereas this script received’t cowl every part, it delivers detailed web page insights in seconds, saving you hours of guide work. 

You’ll be able to then evaluation the suggestions and determine which optimizations to implement.

Establishing your script might initially really feel overwhelming, however comply with every step rigorously and replica the script to make sure every part works. 

You’ll want a couple of issues to get began:

Subsequent, I’ll information you thru my eight-step course of to get this “instrument” up and working.

Step 1: Create the Google Sheet on your instrument

In case you have a Google account, this is so simple as following these steps:

• Open Google Drive.
• Go to the folder the place you need to place your instrument.
• Proper-click on the background.
• Select Google Sheets > Clean spreadsheet.

You’ll be able to rename the Sheet to something you want, however I selected the next: GSC Customized Device.

Step 2: Populate your sheet

Populate the sheet by following these steps rigorously:

• Add the phrases “Choose GSC property” to A1.
• Add the phrases “Choose a URL” to A2.

Transfer to A15 and add “Inspection Outcomes.” Beneath this, so as from A15 to A25, add the next:

• URL:
• Protection:
• Robots.txt:
• Indexing State:
• Final Crawled:
• Google Canonical:
• Consumer Canonical:
• Cell Usability:
• Wealthy Outcomes Eligibility:
• Referring URLs:

In D1, you’ll paste your ChatGPT API key. When you don’t have this key, please go to the hyperlink above and you should definitely get one for this instrument to work.

Add “AI Evaluation Consequence” to F1 and “Immediate Despatched to ChatGPT” to G1.

Now, we have to merge a couple of rows in each of those columns. You’ll merge F2 – F30 and G2 – G30 individually, by following these steps:

• Choose the rows to merge.
• Go to Format > Merge cells > Merge vertically.

Repeat this step for the F and G rows it is advisable merge.

Step 3: Create a Google Cloud challenge

Create a Google Cloud Console information challenge for the instrument. 

As soon as arrange, create a brand new challenge named “GSC Customized Device.”

You are able to do this by going to Choose Venture > New Venture and filling within the info similar to within the screenshot above. 

Click on Create once you’re performed naming the challenge.

Now, let’s join the Google Search Console API to your challenge.

Go to the search bar and sort “Google Search Console API,” choose it, after which hit Allow on the following display.

We nonetheless have lots to do and can revisit this challenge shortly.

Step 4: Create an App Script

Combine Apps Script into your newly created Google Sheets file.

To take action, open your file, then go to Extensions > Apps Script. Copy and paste the code I’ve created beneath. 

(You are able to do this by choosing the code contained in the window and hitting CTRL + C. Return into your Apps Script and hit CTRL + V to stick the code in.)

Hit OK > Save challenge > Run.

Google will immediate you to evaluation all permissions and choose the corresponding account with all of your Google Search Console information linked.

Dig deeper: 5 Python scripts for automating search engine marketing duties

Step 5: Add entry credentials

Return to your Google Cloud Console to make additional changes. 

Click on on Credentials on the left facet of the display:

Click on on + Create Credentials on the high of the display and choose OAuth consumer ID. 

Go to Configure Consent Display screen and choose Exterior. Create the display after which enter your:

• App identify.
• Assist e mail (linked to the GSC account).
• Developer contact info.

Save and proceed to the following display, which says Add or Take away Scopes. 

Choose Google Search Console API scopes and replace. Then, Save and proceed. 

Add the customers you need to grant entry to, then click on Save and proceed.

Step 6: Change to a Google Cloud challenge for GSC information

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

You need to copy over your Venture quantity, which is on the web page.

Navigate to the Venture quantity, choose it and replica it utilizing CTRL + C. 

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

Paste your challenge quantity into the textual content field and click on Set challenge.

Step 7: Title your Google Apps Script

Add a reputation on your script to maintain issues organized. 

To do that, go to Venture Historical past, click on on the Untitled challenge towards the highest of the display, and enter “GSC Customized Device.”

Step 8: Edit the manifest file

You’ll now return to your Venture Settings and click on Present “appsscript.json” within the editor. 

Contained in the Editor, go to appsscript.json and exchange every part within the file with the code beneath:

{
  "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"
}

When you’ve pasted every part in, you’ll go to the Code.js file and paste this code:

// Retailer 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 = { position: "system", content material: "You're a useful search engine marketing professional." };


perform log(message) 


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


perform getLogs()  'No logs out there.';



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


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


perform promptReauthorization() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.alert(
    'Re-authorize Script',
    'Re-authorizing will revoke present permissions and require you to authorize once more. Do you need to proceed?',
    ui.ButtonSet.YES_NO
  );


  if (response === ui.Button.YES) {
    attempt {
      scriptProperties.deleteProperty('oauthToken'); // Clear previous token
      const token = fetchOAuthToken(); // Fetch and retailer new token
      log("OAuth Token: " + token);
      ui.alert('Authorization profitable. No additional motion is required.');
    } catch (e) {
      ui.alert('Authorization failed: ' + e.toString());
    }
  } else {
    ui.alert('Re-authorization canceled.');
  }
}


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


  const websites = getSitesListFromGSC(oauthToken);


  if (!websites || websites.size === 0) {
    SpreadsheetApp.getUi().alert('No GSC properties discovered. Please guarantee you may have entry to GSC properties.');
    return;
  }


  const siteUrls = ['Select a property'].concat(
    websites.map(website => website.siteUrl).type()
  );


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


let isProcessing = false; // International flag to forestall recursive triggering


perform onEdit(e) {
  if (isProcessing) return; // Forestall re-entry throughout execution
  isProcessing = true; // Set flag to true


  attempt {
    resetLogs();
    const sheet = e.supply.getActiveSheet();
    const vary = e.vary;


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


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


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


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


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


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


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


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


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


        sheet.getRange('A3').setValue('Loading key phrases...').setFontWeight('daring');


        const oauthToken = fetchOAuthToken();
        const key phrases = getTopKeywordsForUrl(selectedProperty, selectedUrl, oauthToken);


        if (!key phrases || key phrases.size === 0) {
          sheet.getRange('A3').setValue('No key phrases discovered').setFontWeight('daring');
          log(`No key phrases discovered for URL ${selectedUrl}`);
          return;
        }


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


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


  const apiUrl = `https://www.googleapis.com/site owners/v3/websites/${encodeURIComponent(selectedProperty)}/searchAnalytics/question`;
  return { url: apiUrl, payload: payload };
}


perform getSitesListFromGSC(oauthToken) {
  attempt {
    const url="https://www.googleapis.com/site owners/v3/websites";


    const headers = {
      'Authorization': 'Bearer ' + oauthToken,
      'Content material-Kind': 'utility/json'
    };


    const choices = {
      technique: 'get',
      headers: headers,
      muteHttpExceptions: true
    };


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


    if (response.getResponseCode() === 200)  [];
     else {
      throw new Error(`Error fetching information: ${response.getResponseCode()} - ${response.getContentText()}`);
    }
  } catch (e) {
    log(`Error: ${e.toString()}`);
    return [];
  }
}


perform getUrlsForProperty(property, oauthToken) {
  attempt {
    const apiUrl = `https://www.googleapis.com/site owners/v3/websites/${encodeURIComponent(property)}/searchAnalytics/question`;


    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 material-Kind": "utility/json"
    };


    const choices = {
      technique: "publish",
      contentType: "utility/json",
      headers: headers,
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    };


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


    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(`Did not fetch information: ${response.getResponseCode()} - ${response.getContentText()}`);
    }
  } catch (e) {
    log(`Error: ${e.toString()}`);
    return [];
  }
}


perform getTopKeywordsForUrl(property, url, oauthToken) {
  attempt {
    const apiUrl = `https://www.googleapis.com/site owners/v3/websites/${encodeURIComponent(property)}/searchAnalytics/question`;


    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 material-Kind": "utility/json"
    };


    const choices = {
      technique: "publish",
      contentType: "utility/json",
      headers: headers,
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    };


    const response = UrlFetchApp.fetch(apiUrl, choices);
    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 => ({
        question: row.keys[0],
        clicks: row.clicks,
        impressions: row.impressions,
        ctr: row.ctr
      })) : [];
    } else {
      throw new Error(`Did not fetch information: ${response.getResponseCode()} - ${response.getContentText()}`);
    }
  } catch (e) {
    log(`Error: ${e.toString()}`);
    return [];
  }
}


perform getToday() {
  const at the moment = new Date();
  return at the moment.toISOString().cut up("T")[0];
}


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


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


  // Clear earlier inspection leads to A15:D30
  sheet.getRange('A15:D30').clearContent();
  sheet.getRange('A15').setValue('Inspecting...').setFontWeight('daring');


  if (!url || url === 'Choose a URL') {
    SpreadsheetApp.getUi().alert('Please choose a sound URL in cell B2 earlier than inspecting.');
    sheet.getRange('A15').setValue('No URL chosen').setFontWeight('daring');
    return;
  }


  const oauthToken = fetchOAuthToken();


  attempt {
    const outcome = callUrlInspectionApi(property, url, oauthToken);


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


    // Populate inspection leads to the sheet
    sheet.getRange('A15').setValue(`Inspection Outcomes`).setFontWeight('daring');
    sheet.getRange('A16').setValue(`URL:`).setFontWeight('daring');
    sheet.getRange('B16').setValue(url);


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


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


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


    sheet.getRange('A20').setValue(`Final Crawled:`).setFontWeight('daring');
    sheet.getRange('B20').setValue(indexStatus.lastCrawlTime || 'Not Obtainable');


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


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


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


    sheet.getRange('A24').setValue(`Wealthy Outcomes Eligibility:`).setFontWeight('daring');
    sheet.getRange('B24').setValue(richResults.verdict || 'Unknown');


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


    // Log and alert full response for debugging
    const fullResponse = JSON.stringify(outcome, null, 2);
    log(`Full Inspection Consequence: ${fullResponse}`);
    //SpreadsheetApp.getUi().alert(`Inspection Accomplished. Full Response:nn${fullResponse}`);
  } catch (error) {
    sheet.getRange('A15').setValue('Inspection Failed').setFontWeight('daring');
    log(`Error inspecting URL: ${error.message}`);
    SpreadsheetApp.getUi().alert(`Error inspecting URL: ${error.message}nnLogs:n${getLogs()}`);
  }
}


perform callUrlInspectionApi(property, url, oauthToken) {
  const apiUrl="https://searchconsole.googleapis.com/v1/urlInspection/index:examine";


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


  const headers = {
    Authorization: `Bearer ${oauthToken}`,
    'Content material-Kind': 'utility/json'
  };


  const choices = {
    technique: 'publish',
    contentType: 'utility/json',
    headers: headers,
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };


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


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


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


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


      if (jsonResponse && jsonResponse.inspectionResult) {
        return jsonResponse.inspectionResult;
      } else {
        log(`Surprising API Response Construction: ${responseText}`);
        throw new Error('Surprising API response format. "inspectionResult" subject is lacking.');
      }
    } else {
      log(`Failed API Name: ${responseText}`);
      throw new Error(`Failed to examine URL. Response Code: ${responseCode}. Response: ${responseText}`);
    }
  } catch (error) {
    log(`Error throughout API name: ${error}`);
    throw new Error(`Error inspecting URL: ${error.message}`);
  }
}


perform callChatGPT(immediate, temperature = 0.9, maxTokens = 800, mannequin = "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's lacking in cell D1. Please present a sound OpenAI API key.");
  }


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


  const choices = {
    technique: "POST",
    headers: {
      "Content material-Kind": "utility/json",
      "Authorization": "Bearer " + secretKey
    },
    payload: JSON.stringify(payload)
  };


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


    if (responseData.selections && responseData.selections[0] && responseData.selections[0].message) {
      return responseData.selections[0].message.content material.trim();
    } else {
      log("Surprising response format from OpenAI: " + JSON.stringify(responseData));
      return "Sorry, I could not course of the request.";
    }
  } catch (error) {
    log("Error calling OpenAI API: " + error);
    return "Sorry, there was an error processing your request.";
  }
}


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


  // Validate enter fields
  if (!url || key phrases.size === 0 || inspectionData.some(row => row.size < 2 || !row[0].trim() || !row[1].trim())) {
    SpreadsheetApp.getUi().alert("Guarantee the next are crammed earlier than working AI Analyze:n- URL in B2n- Key phrases in A3:A12n- Inspection information in A16:B20");
    return;
  }


  // Put together the immediate for ChatGPT
  const immediate = `
    Analyze this URL: ${url}
    Additionally the view-source model from: ${url}
    towards these key phrases: ${key phrases.be part of(", ")}
    Contemplating the URL inspection information from Google Search Console:
    ${inspectionData.map(row => `${row[0]}: ${row[1]}`).be part of("n")}
    Recommend a brief record of particular suggestions on how I can enhance the web page's search engine marketing. Make sure that the suggestions embody particulars comparable to change this to that, or add one thing, and many others... Be concrete with search engine marketing suggestions.
  `;


  // Show the immediate in G1
  sheet.getRange('G1').setValue("Immediate Despatched to ChatGPT").setFontWeight("daring");
  sheet.getRange('G2:G30').clearContent(); // Clear earlier content material in column G
  sheet.getRange('G2:G30').merge(); // Merge cells G2:G30
  sheet.getRange('G2').setValue(immediate).setVerticalAlignment("high"); // Add the immediate and align to high
  sheet.setColumnWidth(7, 400); // Set column G width to 400px


  // Name ChatGPT API
  const analysisResult = callChatGPT(immediate);


  // Show the outcome within the spreadsheet (Column F)
  sheet.getRange('F1').setValue("AI Evaluation Consequence").setFontWeight("daring");
  sheet.getRange('F2:F30').clearContent(); // Clear earlier content material
  sheet.getRange('F2:F30').merge(); // Merge the cells
  sheet.getRange('F2').setValue(analysisResult).setVerticalAlignment("high"); // Add the AI outcome and align to high
  sheet.setColumnWidth(6, 400); // Set column F width to 400px


  // Log the response
  log("AI Evaluation Accomplished: " + analysisResult);
}

As soon as performed, return to your sheets, refresh and use the brand new Search Console > Fetch GSC Properties.

Observe the prompts till it asks you to pick the account you’re utilizing and to finally choose the app you intend on utilizing.

If every part goes nicely, you’ll be able to transfer on to the thrilling a part of placing it collectively and working your first search engine marketing evaluation utilizing your new script.

Placing our Sheets, GSC and ChatGPT instrument collectively

You’ve performed lots till now, but it surely’s time to see the instrument in motion. Right here’s the way it works: 

• Go to Search Console > Authorize GSC.

Ensure that you’re utilizing the account to which the area’s GSC is related; in any other case, it is not going to work.

• Go to Search Console > Fetch GSC properties.

You’ll now discover that the Choose GSC property at B1 on the Sheet is populated. Choose the area you need to analyze after which choose the URL from B1 on the Sheet. 

• Go to Search Console > Examine URL.

The Inspection Outcomes on the Sheet will populate and give you a wealth of details about the web page, such because the protection, final crawl date, and extra. 

Lastly, we’ll return one final time and do the next:

• Go to Search Console > AI Analyze.

The entire info within the “AI Evaluation Consequence” will now populate, providing you insights into the web page’s key components and telling you the precise steps that you could take to enhance the web page.

I don’t advocate following the instructions blindly, however they do supply actionable steps that you could comply with to strengthen the web page.

If you wish to change the ChatGPT immediate so as to add your personal customized information, go to cell G2, proper beneath the heading that claims “Immediate Despatched to ChatGPT.”

Bravo! You’ve got a working script that may velocity up your search engine marketing evaluation and make your day extra actionable. 

Combining Sheets, GSC and ChatGPT has helped me grow to be extra environment friendly and permits me to spend extra time optimizing and fewer analyzing hundreds of pages.

Experiment with the script and discover new methods to make it your personal.

Dig deeper: 15 AI instruments you need to use for search engine marketing