Automate Multi-Web site Reporting With Google Sheets And GSC API

Working in search engine optimisation 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 world corporations, 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 doable 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 a whole lot of web sites at a worldwide scale.

What Can You Do?

I devised a Google Sheets Apps Script that connects to GSC’s API to rework world reporting from an arduous activity 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 many others.

Since we handle a whole lot of web sites, it’s not unusual for customers to finish up on considered one of 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 point out 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 Tons of Of Websites

Step 1: Creating Your Google Sheets

Screenshot from writer, April 2024

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 need to place the information.
• Proper-click on the background
• Choose > Google Sheets > Clean Spreadsheet.

You’ll need to rename the file. I referred to as mine “International 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 Venture, which can also be pretty easy and easy.

Step 3: Create A Google Cloud Console Information Venture

Creating your mission needs to 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 Venture > New Venture.
• Enter Venture Identify (instance: “My GSC Information Venture”).
• Faucet Create.

Screenshot from writer, April 2024

• Click on Choose Venture.
• Choose your Venture.

• 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 simply created beforehand. You’ll have to 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) { // Verify if response shouldn't be outlined or null and has no 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) {

  strive {
    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': 'utility/json'
    };
    const choices = {
      'technique': 'put up',
      'contentType': 'utility/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 can log the precise JSON response
    return json.rows; // Modify this line primarily based 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 mission and do the next:

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

*Observe: If you’re receiving a Unhealthy Request error from Google with too many redirects, it is because you’ve 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 Overview permissions and might want to choose the Google Account related together with your Google Search Console.

Google provides you with a warning as a result of the app isn’t verified, so merely faucet on the “Superior” setting after which “Go to Untitled mission (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 number of back-and-forth occurring between Sheets and Google Cloud Console, but it surely’s an unlucky necessity at this level. Now, we might be establishing Entry Credentials, which would require you to return to the Google Cloud Console.

Observe: You need 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 have to:

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

• Click on Exterior.
• Faucet Create.
• Enter “My GSC Information” because the App title.
• Add your Help electronic mail (your electronic 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.
• Verify 2 of the Google Search Console API scopes (is likely to be 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 Venture For GSC Information

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

Screenshot from writer, April 2024

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

Change again to your Apps Script tab and faucet Venture Settings:

Screenshot from writer, April 2024

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

Step 7: Rename Your Google Apps Script

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

You’ll then:

• Click on Untitled mission on the prime of the display screen.
• Enter “My GSC Information Venture Script.”
• Click on on Rename.

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

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

This time, you’ll need 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 see beneath:

Screenshot from writer, April 2024

You’ll need to delete every part 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 evaluation permissions, and also you’ll want to pick 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: Modify The Dates For Web site Information Evaluation

Within the Google Sheets file, you’ll need to add the next below:

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

Observe: The beginning and finish dates needs to be laid out in M1 and M2. For instance, you possibly can enter:

Observe: The date format might differ primarily based in your system settings and site.

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

Every little thing is about up, however it’s 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 below the headers “Clicks % Distinction” and “Impressions % Distinction” and click on on Format > Conditional formatting. Underneath Format guidelines, you’ll need to choose Lower than.

Within the “Worth or formulation” 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 finished.

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

Listed below 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’ve a straightforward solution to run studies on a number of websites without delay.

That’s It, You Have Your International 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:

*Observe: This script helps about 150 domains, however when you want extra, you possibly can regulate the row #14 in your AppScripts file:

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

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

Try my different tutorial on Integrating ChatGPT With Google Sheets.

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

Extra sources: 

Featured Picture: 200dgr /Shutterstock