Bulk check indexing and coverage reports in Google Sheets

Made by Mike Richardson

Google made a huge announcement at the end of January; they were to finally allow webmasters to query indexing and coverage data via an API.

This sheet will pull out basic information from GSC:

• The last time Google crawled the page
• The coverage status
• Whether or not robots.txt is blocking the page
• Whether the page is indexed
• Both the user-declared and Google canonical of a page

How to use the Sheet

1) Make a copy of the sheet.
2) You will need a free Google service account to get going with this. If you haven’t got one already, it only takes a few minutes to set up:

1) Head to https://console.cloud.google.com/
2) Click the drop down in the header (just to the right of 'Google Cloud Platform'). In the resulting popup click 'New Project'.
3) Add a name for your project and click 'Create'.
4) Make sure your project is selected in the top blue bar. Then, go to the main menu in the top left of the screen, click 'APIs and Services' and then click 'Credentials'.
5) Click Create Credentials > Service account
6) Add a service account name (this can be anything you want). Click the white Create and Continue button.
7) Select the role as Project owner (click Project on the first column, then Owner down the second). Click Continue.
8) Type in your Google account email for both the user role and admin role box. Then click Done.
9) Now that you are on the account screen, click into the service account you have just created.
10) Navigate to the Keys tab, then click Add Key > Create new Key > JSON. A file will start downloading to your computer.
11) You then need to pull out the details of this file. Get the client_email, client_id and private_key.
Add them to this spreadsheet in the top fields on the sheet. You might want to then hide these rows so your keys are not visible to all.
12) Return back to Google Cloud Console and in the top search bar search for 'Google Search Console API'. Click into it and click 'Enable'.
13) The last step is to take your client_email and add it as a user in the GSC account you are going to query.
This can be done by logging into GSC > Settings > Users and permissions > Add user.

3) Once you have added your private key, client email and client ID, add the domain property (as it appears in GSC).
Remember to take all trailing slashes into account (i.e., https://www.mikejrichardson.com/). Or, if your site is verified as a domain property,
enter in the format of mikejrichardson.com.
4) Under the URL column, enter all the URLs you want to analyse.
5) Click the green ‘Run’ button. The script will start to run and the data will gradually populate in the sheet.
The first time you run, you may be asked for authorization. Continue through these steps, and then click the green Run button again

What if the script times out?

One of the big problems with Apps Script is that there is a 6-minute limit to run scripts. Once a script reaches this limit, it will timeout – even if it is unfinished.
If you are running into this problem, you can change the ‘Start from row’ to the next row you want to analyse. In other words, you can pick up from where you left off.

Are there any limitations?

Of course! This was created within hours of Google’s announcement. The API offers a host of opportunities.
As well as the additional fields that you could return, there are plenty of other things you could consider:

• Set up rules to check if the canonical tags are different
• Add conditional formatting to cells
• Adapt the script and use triggers so it can work through a long list of URLs, without worrying about the 6-minute timeout rule.