Automate Multi-Site Reporting With Google Sheets And GSC API via @sejournal, @makhyan

1 week ago 18

ARTICLE AD BOX

Working successful SEO leads to absorbing challenges that I’m definite you’ve each faced astatine 1 point.

You’re a maestro of flexibility and managing tedious tasks. I’ve precocious recovered myself dealing with 100+ top-tier sites.

Working with planetary companies, it’s rather the puzzle to:

Wrangle information for 100+ sites.
Keep tabs connected each site’s performance.

And, since immoderate of these sites vie against each different connected the archetypal leafage of Google, it’s rather imaginable that Site 1’s postulation drops but Site 2 captures the loss.

Checking 1 site’s Google Search Console (GSC) is easy, but it’s aggravated with hundreds of sites astatine a planetary scale.

What Can You Do?

I devised a Google Sheets Apps Script that connects to GSC’s API to alteration planetary reporting from an arduous task that tin instrumentality days – oregon weeks – into 1 that takes a fewer minutes.

After creating the script, I tin easy enactment successful a day scope and propulsion each site’s:

Clicks and impressions.
Keywords.
Average rankings.
Etc.

Since we negociate hundreds of sites, it’s not uncommon for users to extremity up connected 1 of our sites to marque their purchase, arsenic mentioned above.

In the expansive strategy of things, the bigger representation is much important than an idiosyncratic site’s performance.

What I’m going to amusement you is my 10-step process to make a publication that pulls clicks and impressions and past compares it each twelvemonth implicit twelvemonth (YoY).

10-Step Process To Create A Google Sheets Apps Script For Reporting On Hundreds Of Sites

Step 1: Creating Your Google Sheets

step 1 entree google drive Screenshot from author, April 2024

Your archetypal measurement is to make your archetypal Google Sheets file. You tin bash this by pursuing these steps:

Go to Google Drive.
Navigate to the folder wherever you privation to spot the files.
Right-click connected the background
Select > Google Sheets > Blank Spreadsheet.

You’ll privation to rename the file. I called excavation “Global Search Console Reporting.”

step 1 sanction google sheets file Screenshot from author, April 2024

Your record is present acceptable up, and you’re acceptable for the adjacent step.

Step 2: Setting Up Your Google Sheet

A blank expanse isn’t utile and won’t marque consciousness to users until you adhd immoderate headers successful Row 1. Headers that I urge adding, successful this bid and bolding, are:

Website.
Niche.
Clicks.
Impressions.
YoY Clicks.
YoY Impressions.
Clicks % Difference.
Impressions % Difference.

Your record should present look thing similar this:

step 2 adhd file headers Screenshot from author, April 2024

Your adjacent measurement is to make a Google Cloud Project, which is besides reasonably elemental and straightforward.

Step 3: Create A Google Cloud Console Data Project

Creating your task should beryllium escaped due to the fact that Google provides a $300 recognition to effort retired its platform. If you haven’t utilized Google Cloud, you tin find it astatine https://console.cloud.google.com/.

You tin present travel these steps:

Tap Select Project > New Project.
Enter Project Name (example: “My GSC Data Project”).
Tap Create.

step 3 google console project Screenshot from author, April 2024

Click Select Project.
Select your Project.

step 3 prime project

Click the apical Search bar.
Type “Google Search Console API.”
Select “Google Search Console API.”
Click Enable.

Step 4: Create Apps Scripts In Google Sheets

In this step, we volition enactment connected integrating the Apps Script into the Google Sheet that you created previously. You’ll request to unfastened the Sheet and travel these steps:

Tap Extensions > Apps Script.

step 4 make apps script

I’m not going to spell into the details connected however the publication works, but you tin transcript this code:

And past spell backmost to your Apps Script task and bash the following:

Press CTRL + A to prime all.
Press CTRL + V to paste successful the codification you copied.
Tap OK.
Click Save project.
Tap Run.

*Note: If you are receiving a Bad Request mistake from Google with excessively galore redirects, this is due to the fact that you person aggregate accounts logged in. Try successful a browser with lone 1 Google relationship logged in.

step 4 prevention tally apps script Screenshot from author, April 2024

You’ll beryllium requested to Review permissions and volition request to prime the Google Account associated with your Google Search Console.

Google volition springiness you a informing due to the fact that the app isn’t verified, truthful simply pat connected the “Advanced” mounting and past “Go to Untitled task (unsafe).”

step 4 unsafe app Screenshot from author, April 2024

Finally, you tin implicit this measurement by tapping oregon clicking connected the Allow button.

Step 5: Set Up The Access Credentials

I cognize there’s a batch of back-and-forth going connected betwixt Sheets and Google Cloud Console, but it’s an unfortunate necessity astatine this point. Now, we volition beryllium mounting up Access Credentials, which volition necessitate you to spell backmost to the Google Cloud Console.

Note: You indispensable person enabled the Google Search Console API from the erstwhile step.

Your surface should look thing similar this:

step-5 oauth concent screen Screenshot from author, April 2024

You’ll request to:

Tap Credentials > Create Credentials.
Tap OAuth lawsuit ID > Configure Consent Screen.

step 5 make credentials oauth

Click External.
Tap Create.
Enter “My GSC Data” arsenic the App name.
Add your Support email (your email utilized for GSC).
Add your Developer interaction accusation (the email you utilized for GSC).
Tap Save and continue.
Tap ADD OR REMOVE SCOPES.
Check 2 of the Google Search Console API scopes (might beryllium connected leafage 2).

step 5 adhd gsc api scope

Click Update.
Click Save and Continue.
Now click Add Users.

step 5 adhd users

You tin adhd aggregate users, preferably those that person entree to GSC.
Save and Continue.

Step 6: Set Up Google Cloud Project For GSC Data

While we’re inactive connected the Google Cloud Project, you’ll privation to click the hamburger icon and spell to Cloud overview > Dashboard:

step 6 unreality dashboard Screenshot from author, April 2024

You’ll announcement that it says “Project number,” which you should prime and Copy by pressing CTRL + C.

Switch backmost to your Apps Script tab and pat Project Settings:

step 6 app settings Screenshot from author, April 2024

Go to the conception titled Google Cloud Platform (GCP) Project, paste the task fig (CTRL + V) into the substance box, and click Set project.

Step 7: Rename Your Google Apps Script

You’ll present privation to rename your Apps Script by going to Project History like this:

step 7 task name

You’ll then:

Click Untitled project astatine the apical of the screen.
Enter “My GSC Data Project Script.”
Click connected Rename.

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

You’re inactive staying wrong of your script, and we’re going to spell backmost to Project Settings conscionable arsenic we did before.

This time, you’ll privation to click Show “appsscript.json” manifest record successful editor to marque definite there’s a checkmark adjacent to it.

Next, click connected Editor and navigate to the appsscript.json, which you tin spot below:

step 8 edit appscript json Screenshot from author, April 2024

You’ll privation to delete everything successful the appsscript.json record and paste successful the pursuing 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" ] }

Once you’ve added the code, you tin click connected your Code.gs record and pat Save, and past Run. You’ll beryllium prompted to reappraisal permissions, and you’ll request to prime your due relationship to proceed using.

After a fewer prompts, you’ll beryllium asked to let your app “My GSC Data,” and execution volition begin.

Step 9: Adjust The Dates For Website Data Analysis

In the Google Sheets file, you’ll privation to adhd the pursuing under:

L1: Start Date.
L2: End Date.

Note: The commencement and extremity dates should beryllium specified successful M1 and M2. For example, you tin input:

03/01/2024
03/31/2024

Note: The day format whitethorn disagree based connected your strategy settings and location.

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

Everything is acceptable up, but you should adhd immoderate conditional formatting to marque it look better. We’re going to absorption connected the “Clicks % Difference” and “Impressions % Difference” columns:

step 10 clicks impressions Screenshot from author, April 2024

Select the rows nether the headers “Clicks % Difference” and “Impressions % Difference” and click connected Format > Conditional formatting. Under Format rules, you’ll privation to prime Less than.

In the “Value oregon formula” substance area, you tin adhd 0.

What this does is that if it’s little than 0, we’ll beryllium changing the colour to reddish since it’s successful the antagonistic and postulation has been lost. You tin bash this by clicking connected the overgarment tin and changing it to red earlier clicking done.

If you privation to alteration a affirmative summation successful postulation to green, you’ll adhd different regularisation for Greater than and adhd the 0 value.

Here are the formulas to usage successful G2 and H2 (you tin replicate them for each row; conscionable click and resistance down for the different rows):

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

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

Now, you person an casual mode to tally reports connected aggregate sites astatine once.

That’s It, You Have Your Global Report

In file A, input your Google Search Console properties; if it is simply a domain property, adhd it arsenic sc-domain:example.com oregon a URL spot arsenic https://example.com

To tally oregon refresh the report, usage the peculiar paper Search Console > Fetch Data:

final measurement run

*Note: This publication supports astir 150 domains, but if you request more, you tin set the enactment #14 successful your AppScripts file:

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

Using this precise tutorial, you’ll person an casual clip turning days of gathering information and moving reports into a fewer minutes. You tin adjacent grow the scripts to execute different calculations oregon stitchery much information for your report.

Check retired my different tutorial connected Integrating ChatGPT With Google Sheets.

Automating your reports is simply a large mode to streamline tedious tasks, and I anticipation it makes your occupation a small easier.

More resources:

Featured Image: 200dgr /Shutterstock