Automate Multi-Site Reporting With Google Sheets And GSC API

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

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

Working pinch world companies, it’s rather nan puzzle to:

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

And, since immoderate of these sites compete against each different connected nan first page of Google, it’s rather imaginable that Site 1’s postulation drops but Site 2 captures nan loss.

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

What Can You Do?

I devised a Google Sheets Apps Script that connects to GSC’s API to toggle shape world reporting from an arduous task that tin return days – aliases weeks – into 1 that takes a fewer minutes.

After creating nan script, I tin easy put 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 make their purchase, arsenic mentioned above.

In nan expansive strategy of things, nan bigger image is much important than an individual site’s performance.

What I’m going to show you is my 10-step process to create a book that pulls clicks and impressions and past compares it each twelvemonth complete twelvemonth (YoY).

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

Step 1: Creating Your Google Sheets

Your first measurement is to create your original Google Sheets file. You tin do this by pursuing these steps:

• Go to Google Drive.
• Navigate to nan files wherever you want to spot nan files.
• Right-click connected nan background
• Select > Google Sheets > Blank Spreadsheet.

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

Your record is now group up, and you’re fresh for nan adjacent step.

Step 2: Setting Up Your Google Sheet

A blank expanse isn’t useful and won’t make 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 now look thing for illustration this:

Your adjacent measurement is to create 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 free because Google provides a $300 in installments to effort retired its platform. If you haven’t utilized Google Cloud, you tin find it astatine https://console.cloud.google.com/.

You tin now travel these steps:

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

• Click Select Project.
• Select your Project.

• Click nan 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 will activity connected integrating nan Apps Script into nan Google Sheet that you created previously. You’ll request to unfastened nan Sheet and travel these steps:

• Tap Extensions > Apps Script.

I’m not going to spell into nan specifications connected really nan book works, but you tin transcript this code:

And past spell backmost to your Apps Script task and do nan following:

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

*Note: If you are receiving a Bad Request correction from Google pinch excessively galore redirects, this is because you person aggregate accounts logged in. Try successful a browser pinch only 1 Google relationship logged in.

You’ll beryllium requested to Review permissions and will request to prime nan Google Account associated pinch your Google Search Console.

Google will springiness you a informing because nan app isn’t verified, truthful simply pat connected nan “Advanced” mounting and past “Go to Untitled task (unsafe).”

Finally, you tin complete this measurement by tapping aliases clicking connected nan 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 will beryllium mounting up Access Credentials, which will require you to spell backmost to nan Google Cloud Console.

Note: You must person enabled nan Google Search Console API from nan erstwhile step.

Your surface should look thing for illustration this:

You’ll request to:

• Tap Credentials > Create Credentials.
• Tap OAuth customer ID > Configure Consent Screen.

• Click External.
• Tap Create.
• Enter “My GSC Data” arsenic nan 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 nan Google Search Console API scopes (might beryllium connected page 2).

• Click Update.
• Click Save and Continue.
• Now click Add 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 still connected nan Google Cloud Project, you’ll want to click nan hamburger icon and spell to Cloud overview > Dashboard:

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:

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

Step 7: Rename Your Google Apps Script

You’ll now want to rename your Apps Script by going to Project History like this:

You’ll then:

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

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

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

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

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

You’ll want to delete everything successful nan appsscript.json record and paste successful nan pursuing script:

Once you’ve added nan 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 will begin.

Step 9: Adjust The Dates For Website Data Analysis

In nan Google Sheets file, you’ll want to adhd nan 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 group up, but you should adhd immoderate conditional formatting to make it look better. We’re going to attraction connected nan “Clicks % Difference” and “Impressions % Difference” columns:

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

In nan “Value aliases formula” matter area, you tin adhd 0.

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

If you want to alteration a affirmative summation successful postulation to green, you’ll adhd different norm for Greater than and adhd nan 0 value.

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

Now, you person an easy measurement 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 aliases a URL spot arsenic https://example.com

To tally aliases refresh nan report, usage nan typical paper Search Console > Fetch Data:

*Note: This book supports astir 150 domains, but if you request more, you tin set nan statement #14 successful your AppScripts file:

Using this very tutorial, you’ll person an easy clip turning days of gathering information and moving reports into a fewer minutes. You tin moreover grow nan scripts to execute different calculations aliases stitchery much information for your report.

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

Automating your reports is simply a awesome measurement to streamline tedious tasks, and I dream it makes your occupation a small easier.

More resources: 

• How To Use Google Sheets For Web Scraping With AI
• Try These Tools & Methods For Exporting Google Search Results To Excel
• State of SEO 2024: Disruptions, AI & Content Strategies

Featured Image: 200dgr /Shutterstock