Home Marketing Automate Multi-Website Reporting With Google Sheets And GSC API

Automate Multi-Website Reporting With Google Sheets And GSC API

admin
-
0
Automate Multi-Website 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 just 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 web site’s efficiency.

And, since a few of these websites compete in opposition to one another on the primary web page of Google, it’s fairly doable that Website 1’s visitors drops however Website 2 captures the loss.

Checking one web site’s Google Search Console (GSC) is simple, but it surely’s intense with a whole bunch 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 remodel international reporting from an arduous process 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 web site’s:

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

Since we handle a whole bunch of web sites, it’s not unusual for customers to finish up on one in every 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 web site’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 12 months over 12 months (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

Screenshot from creator, April 2024

Your first step is to create your unique 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 information.
  • 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.”

step 1 name google sheets fileScreenshot from creator, April 2024

Your file is now arrange, and also you’re prepared for the subsequent 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:

step 2 add column headersScreenshot from creator, April 2024

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

Step 3: Create A Google Cloud Console Knowledge Challenge

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

Now you can observe these steps:

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

step 3 google console projectScreenshot from creator, April 2024

  • Click on Choose Challenge.
  • Choose your Challenge.

step 3 select project

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

step 3 search console api

Step 4: Create Apps Scripts In Google Sheets

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

  • Faucet Extensions > Apps Script.

step 4 create apps script

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

operate onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu(‘Search Console’)
.addItem(‘Fetch Knowledge’, ‘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 last year 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 data for the previous year var previousYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Fetch data for the current year (unchanged) startDate = Utilities.formatDate(new Date(startDateValue), timeZone, format); endDate = Utilities.formatDate(new Date(endDateValue), timeZone, format); var currentYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Process and write data for both years processAndWriteData(sheet, i, previousYearResponse, currentYearResponse); } } function processAndWriteData(sheet, row, previousYearResponse, currentYearResponse) { // Check if response is not defined or null and has at least one row if (previousYearResponse && previousYearResponse.length > 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 12 months in row: ‘ + row);
}

// Course of and write knowledge for the present 12 months
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 12 months 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-Kind’: ‘software/json’
};
const choices = {
‘technique’: ‘publish’,
‘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); // It will log the precise JSON response
return json.rows; // Modify 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 venture 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 venture.
  • Faucet Run.

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

step 4 save run apps scriptScreenshot from creator, April 2024

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

Google will provide 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 venture (unsafe).”

step 4 unsafe appScreenshot from creator, April 2024

Lastly, you may full this step by tapping or clicking on the Enable button.

Step 5: Set Up The Entry Credentials

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

Be aware: You could have enabled the Google Search Console API from the earlier step.

Your display ought to look one thing like this:

step-5 oauth concent screenScreenshot from creator, April 2024

You’ll have to:

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

step 5 create credentials oauth

  • Click on Exterior.
  • Faucet Create.
  • Enter “My GSC Knowledge” 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.
  • Test 2 of the Google Search Console API scopes (is likely to be on web page 2).

step 5 add gsc api scope

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

step 5 add users

  • You possibly can add a number of customers, ideally those who have entry to GSC.
  • Save and Proceed.

Step 6: Set Up Google Cloud Challenge For GSC Knowledge

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

step 6 cloud dashboardScreenshot from creator, April 2024

You’ll discover that it says “Challenge quantity,” which you must choose and Copy by urgent CTRL + C.

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

step 6 app settingsScreenshot from creator, April 2024

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

Step 7: Rename Your Google Apps Script

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

step 7 project name

You’ll then:

  • Click on Untitled venture on the prime of the display.
  • Enter “My GSC Knowledge Challenge 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 Challenge 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:

step 8 edit appscript jsonScreenshot from creator, April 2024

You’ll wish to delete every little thing 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 may click on in your Code.gs file and faucet Save, after which Run. You’ll be prompted to assessment 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 Knowledge,” and execution will start.

Step 9: Modify The Dates For Web site Knowledge 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 may enter:

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

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

Every little thing is ready up, however you must add some conditional formatting to make it look higher. We’re going to concentrate on the “Clicks % Distinction” and “Impressions % Distinction” columns:

step 10 clicks impressionsScreenshot from creator, 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 formulation” textual content space, you may 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 unfavourable 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 optimistic improve in visitors to inexperienced, you’ll add one other rule for Higher than and add the 0 value.

Listed below are the formulation to make use of in G2 and H2 (you may 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 might have a straightforward solution to run reviews on a number of websites directly.

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 Knowledge:

final step run

*Be aware: This script helps about 150 domains, however in case you want extra, you may alter 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 operating reviews into a couple of minutes. You possibly can even develop the scripts to carry out different calculations or collect extra knowledge in your report.

Take a look at my different tutorial on Integrating ChatGPT With Google Sheets.

Automating your reviews 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

LEAVE A REPLY

Please enter your comment!
Please enter your name here

© Newspaper WordPress Theme by TagDiv