Automate Multi-Site Reporting With Google Sheets And GSC API

Working in search engine optimization results in attention-grabbing challenges that I’m positive you’ve all confronted at one level.

You’re a grasp of flexibility and managing tedious duties. I’ve lately discovered myself coping with 100+ top-tier websites.

Working with international firms, it’s fairly the puzzle to:

• Wrangle knowledge for 100+ websites.
• Hold tabs on each website’s efficiency.

And, since a few of these websites compete towards one another on the primary web page of Google, it’s fairly potential that Web site 1’s visitors drops however Web site 2 captures the loss.

Checking one website’s Google Search Console (GSC) is simple, but it surely’s intense with lots of of websites at a worldwide scale.

What Can You Do?

I devised a Google Sheets Apps Script that connects to GSC’s API to rework international reporting from an arduous job that may take days – or weeks – into one which takes a couple of minutes.

After creating the script, I can simply put in a date vary and pull every website’s:

• Clicks and impressions.
• Key phrases.
• Common rankings.
• And so on.

Since we handle lots of of websites, it’s not unusual for customers to finish up on one in all our websites to make their buy, as talked about above.

Within the grand scheme of issues, the larger image is extra essential than a person website’s efficiency.

What I’m going to indicate you is my 10-step course of to create a script that pulls clicks and impressions after which compares all of it yr over yr (YoY).

10-Step Course of To Create A Google Sheets Apps Script For Reporting On A whole bunch Of Websites

Step 1: Creating Your Google Sheets

Your first step is to create your authentic Google Sheets file. You are able to do this by following these steps:

• Go to Google Drive.
• Navigate to the folder the place you wish to place the recordsdata.
• Proper-click on the background
• Choose > Google Sheets > Clean Spreadsheet.

You’ll wish to rename the file. I referred to as mine “World Search Console Reporting.”

Screenshot from writer, April 2024

Your file is now arrange, and also you’re prepared for the following step.

Step 2: Setting Up Your Google Sheet

A clean sheet isn’t helpful and gained’t make sense to customers till you add some headers in Row 1. Headers that I like to recommend including, on this order and bolding, are:

• Web site.
• Area of interest.
• Clicks.
• Impressions.
• YoY Clicks.
• YoY Impressions.
• Clicks % Distinction.
• Impressions % Distinction.

Your file ought to now look one thing like this:

Screenshot from writer, April 2024

The next move is to create a Google Cloud Mission, which can also be pretty easy and simple.

Step 3: Create A Google Cloud Console Information Mission

Creating your undertaking must be free as a result of Google supplies a $300 credit score to check out its platform. Should you haven’t used Google Cloud, you will discover it at https://console.cloud.google.com/.

Now you can comply with these steps:

• Faucet Choose Mission > New Mission.
• Enter Mission Identify (instance: “My GSC Information Mission”).
• Faucet Create.

Screenshot from writer, April 2024

• Click on Choose Mission.
• Choose your Mission.

• Click on the highest Search bar.
• Sort “Google Search Console API.”
• Choose “Google Search Console API.”
• Click on Allow.

Step 4: Create Apps Scripts In Google Sheets

On this step, we are going to work on integrating the Apps Script into the Google Sheet that you just created beforehand. You’ll must open the Sheet and comply with these steps:

• Faucet Extensions > Apps Script.

I’m not going to enter the main points on how the script works, however you possibly can copy this code:

operate onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Search Console')
    .addItem('Fetch Information', 'menuItem1')
    .addToUi();
}

operate menuItem1() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow(); // Discover the final row with knowledge in column A

  // Clear cells C2:F151 earlier than processing knowledge
  sheet.getRange("C2:F151").clearContent();

  for (var i = 2; i <= lastRow; i++) { var siteProperty = sheet.getRange(i, 1).getValue(); var startDateValue = sheet.getRange('M1').getValue(); var endDateValue = sheet.getRange('M2').getValue(); var timeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(); var format = "yyyy-MM-dd"; // Calculate dates for final yr var lastYearStartDate = new Date(startDateValue); lastYearStartDate.setFullYear(lastYearStartDate.getFullYear() - 1); var lastYearEndDate = new Date(endDateValue); lastYearEndDate.setFullYear(lastYearEndDate.getFullYear() - 1); var startDate = Utilities.formatDate(lastYearStartDate, timeZone, format); var endDate = Utilities.formatDate(lastYearEndDate, timeZone, format); // Fetch knowledge for the earlier yr var previousYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Fetch knowledge for the present yr (unchanged) startDate = Utilities.formatDate(new Date(startDateValue), timeZone, format); endDate = Utilities.formatDate(new Date(endDateValue), timeZone, format); var currentYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Course of and write knowledge for each years processAndWriteData(sheet, i, previousYearResponse, currentYearResponse); } } operate processAndWriteData(sheet, row, previousYearResponse, currentYearResponse) { // Examine if response just isn't outlined or null and has not less than one row if (previousYearResponse && previousYearResponse.size > 0) {
    var previousYearClicks = 0;
    var previousYearImpressions = 0;

    previousYearResponse.forEach(operate(row) {
      previousYearClicks += row.clicks;
      previousYearImpressions += row.impressions;
    });

    sheet.getRange(row, 5).setValue(previousYearClicks); // Write to column D (index 5)
    sheet.getRange(row, 6).setValue(previousYearImpressions); // Write to column E (index 6)
  } else {
    Logger.log('No knowledge discovered for earlier yr in row: ' + row);
  }

// Course of and write knowledge for the present yr
  if (currentYearResponse && currentYearResponse.size > 0) {
    var currentYearClicks = 0;
    var currentYearImpressions = 0;

    currentYearResponse.forEach(operate(row) {
      currentYearClicks += row.clicks;
      currentYearImpressions += row.impressions;
    });

    sheet.getRange(row, 3).setValue(currentYearClicks); // Write to column C (index 3)
    sheet.getRange(row, 4).setValue(currentYearImpressions); // Write to column D (index 4)
  } else {
    Logger.log('No knowledge discovered for present yr in row: ' + row);
  }
}



operate requestSearchConsoleAPI(siteProperty, startDate, endDate) {

  attempt {
    const oauthToken = ScriptApp.getOAuthToken(); // Accurately name the tactic
    const siteUrl = siteProperty;
    const url="https://www.googleapis.com/site owners/v3/websites/" + encodeURIComponent(siteUrl) + '/searchAnalytics/question';
    const payload = {
      startDate: startDate,
      endDate: endDate,
      kind: 'net'
    };

    const headers = {
      'Authorization': 'Bearer ' + oauthToken,
      'Content material-Sort': 'software/json'
    };
    const choices = {
      'methodology': 'submit',
      'contentType': 'software/json', // Constant content material kind
      'headers': headers,
      'payload': JSON.stringify(payload),
      'muteHttpExceptions': true
    };

    const response = UrlFetchApp.fetch(url, choices);
    const responseCode = response.getResponseCode();
    const contentText = response.getContentText(); // Get response textual content for logging

  Logger.log('Response Code: ${responseCode}'); // Use backticks
  Logger.log('Response Content material: ${contentText}'); // Use backticks


  if (responseCode === 200) {
    const json = JSON.parse(contentText);
    Logger.log(json); // This may log the precise JSON response
    return json.rows; // Alter this line based mostly on the precise construction of your API response
  } else {
    // Accurately use backticks right here for template literals
    const errorMessage="Error fetching knowledge: ${responseCode} - ${contentText}";
    Logger.log(errorMessage);
    throw new Error(errorMessage);
  }

  } catch (e) {
    Logger.log('Error: ${e.toString()}');
    return null;
  }
}

After which return to your Apps Script undertaking and do the next:

• Press CTRL + A to pick out all.
• Press CTRL + V to stick within the code you copied.
• Faucet OK.
• Click on Save undertaking.
• Faucet Run.

*Be aware: If you’re receiving a Dangerous Request error from Google with too many redirects, it’s because you have got a number of accounts logged in. Strive in a browser with just one Google account logged in.

Screenshot from writer, April 2024

You’ll be requested to Evaluation permissions and might want to choose the Google Account related together with your Google Search Console.

Google offers you a warning as a result of the app isn’t verified, so merely faucet on the “Superior” setting after which “Go to Untitled undertaking (unsafe).”

Screenshot from writer, April 2024

Lastly, you possibly can full this step by tapping or clicking on the Permit button.

Step 5: Set Up The Entry Credentials

I do know there’s a whole lot of back-and-forth occurring between Sheets and Google Cloud Console, but it surely’s an unlucky necessity at this level. Now, we can be organising Entry Credentials, which would require you to return to the Google Cloud Console.

Be aware: It’s essential to have enabled the Google Search Console API from the earlier step.

Your display screen ought to look one thing like this:

Screenshot from writer, April 2024

You’ll must:

• Faucet Credentials > Create Credentials.
• Faucet OAuth consumer ID > Configure Consent Display.

• Click on Exterior.
• Faucet Create.
• Enter “My GSC Information” because the App identify.
• Add your Assist e mail (your e mail used for GSC).
• Add your Developer contact info (the e-mail you used for GSC).
• Faucet Save and proceed.
• Faucet ADD OR REMOVE SCOPES.
• Examine 2 of the Google Search Console API scopes (is perhaps on web page 2).

• Click on Replace.
• Click on Save and Proceed.
• Now click on Add Customers.

• You’ll be able to add a number of customers, ideally people who have entry to GSC.
• Save and Proceed.

Step 6: Set Up Google Cloud Mission For GSC Information

Whereas we’re nonetheless on the Google Cloud Mission, you’ll wish to click on the hamburger icon and go to Cloud overview > Dashboard:

Screenshot from writer, April 2024

You’ll discover that it says “Mission quantity,” which it is best to choose and Copy by urgent CTRL + C.

Swap again to your Apps Script tab and faucet Mission Settings:

Screenshot from writer, April 2024

Go to the part titled Google Cloud Platform (GCP) Mission, paste the undertaking quantity (CTRL +  V) into the textual content field, and click on Set undertaking.

Step 7: Rename Your Google Apps Script

You’ll now wish to rename your Apps Script by going to Mission Historical past like this:

You’ll then:

• Click on Untitled undertaking on the high of the display screen.
• Enter “My GSC Information Mission Script.”
• Click on on Rename.

Step 8: Edit Google Apps Manifest File For Code.gs Script

You’re nonetheless staying inside your script, and we’re going to return to Mission Settings simply as we did earlier than.

This time, you’ll wish to click on Present “appsscript.json” manifest file in editor to ensure there’s a checkmark subsequent to it.

Subsequent, click on on Editor and navigate to the appsscript.json, which you’ll be able to see beneath:

Screenshot from writer, April 2024

You’ll wish to delete the whole lot within the appsscript.json file and paste within the following script:

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

When you’ve added the code, you possibly can click on in your Code.gs file and faucet Save, after which Run. You’ll be prompted to evaluate permissions, and also you’ll want to pick out your applicable account to proceed utilizing.

After a couple of prompts, you’ll be requested to permit your app “My GSC Information,” and execution will start.

Step 9: Alter The Dates For Web site Information Evaluation

Within the Google Sheets file, you’ll wish to add the next beneath:

• L1: Begin Date.
• L2: Finish Date.

Be aware: The beginning and finish dates must be laid out in M1 and M2. For instance, you possibly can enter:

Be aware: The date format could differ based mostly in your system settings and placement.

Step 10: Set Conditional Formatting For Non-Empty Cells Much less Than Zero

The whole lot is ready up, however it is best to add some conditional formatting to make it look higher. We’re going to give attention to the “Clicks % Distinction” and “Impressions % Distinction” columns:

Screenshot from writer, April 2024

Choose the rows beneath the headers “Clicks % Distinction” and “Impressions % Distinction” and click on on Format > Conditional formatting. Beneath Format guidelines, you’ll wish to choose Lower than.

Within the “Worth or components” textual content space, you possibly can add 0.

What this does is that if it’s lower than 0, we’ll be altering the colour to pink because it’s within the unfavorable and visitors has been misplaced. You are able to do this by clicking on the paint can and altering it to pink earlier than clicking achieved.

If you wish to change a constructive improve in visitors to inexperienced, you’ll add one other rule for Better than and add the 0 worth.

Listed here are the formulation to make use of in G2 and H2 (you possibly can replicate them for every row; simply click on and drag down for the opposite rows):

=IFERROR(IF(AND(C2<>"",E2<>""), (C2-E2)/E2, ""),"")

=IFERROR(IF(AND(D2<>"",F2<>""), (D2-F2)/F2, ""),"")

Now, you have got a simple method to run stories on a number of websites without delay.

That’s It, You Have Your World Report

In column A, enter your Google Search Console properties; if it’s a area property, add it as sc-domain:instance.com or a URL property as https://instance.com

To run or refresh the report, use the particular menu Search Console > Fetch Information:

*Be aware: This script helps about 150 domains, however should you want extra, you possibly can modify the row #14 in your AppScripts file:

sheet.getRange("C2:F151").clearContent();

Utilizing this very tutorial, you’ll have a simple time turning days of gathering knowledge and operating stories into a couple of minutes. You’ll be able to even broaden the scripts to carry out different calculations or collect extra knowledge on your report.

Try my different tutorial on Integrating ChatGPT With Google Sheets.

Automating your stories is a good way to streamline tedious duties, and I hope it makes your job just a little simpler.

Extra assets: 

Featured Picture: 200dgr /Shutterstock