Bulk check indexing and coverage reports in Google Sheets

Use the Google Search Console URL Inspection API in sheets to bulk check indexing and coverage reports. πŸš€

Guest Contribution: This is a guest contribution from Google Sheets whizz Mike Richardson, an SEO Consultant from the UK.

Update: This sheet will now allow you to run a longer batch of URLs (the other one would time out due to a 6 minute timeout rule that Apps Script has). Please note, red errors will occur – but this doesn’t matter! Leave the script to run in the background (don’t keep clicking Run), and the data will slowly populate.

This script will automatically run at 10 minute intervals (it will run for 6 minutes, pause for 4, and then resume again). This is because of the timeout limits.

Check out the GIF to see it in action πŸŽ₯


Google’s New Search Console URL Inspection API

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

This is just the tip of the iceberg – the API provides a lot of other fields to dive in with.


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.
  6. When the script finishes (if you have a long list of URLs it will result in an error), don’t click Run again. It will automatically pick up where it left off several minutes later.

What if the script times out?

For a big batch of URLs, it will time out due to Apps Script timeout rules. However, even if you see the red timeout message, this is expected behaviour.

Several minutes later the script will resume again.

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

Thanks for stopping by πŸ‘‹

I’m Andrew Charlton, the Google Sheets nerd behind Keywords in Sheets. πŸ€“

Questions? Get in touch with me on social or comment below πŸ‘‡


More Scripts

Submit your response

Your email address will not be published.

11 Responses on this post

  1. Hello, thanks a lot for sharing. It’s quite helpfull. I wanted to try it but I am getting an error:

    Error: Access not granted or expired.

    Do you have any idea how to resolve?

    Thank you in advance.

    1. Hi there πŸ‘‹

      I haven’t been able to replicate this issue, but it’s one a few people have mentioned. I’d imagine there could be a few explanations here:

      – On your service account, have you selected the role as project owner?
      – Have you added your client_email, client_id and private_key to the fields in the sheet correctly. You’ll need to add the full private_key (it’s quite long and includes /n)?

      Apologies it’s a rather vague error that Google provides, so it could be several things. Hope this helps! 😊

  2. Hey, is there a limit to the amount of URL’s requested per day? My sheet was working initialy for the first 146 rows and now stops working. With the debugger in the appsscript sugesting its

    “{error={code=429.0, message=Resource exhausted. Either the short term quota or the daily quota for https://www.pmtonline.co.uk/ exceeded., status=RESOURCE_EXHAUSTED}}”

    1. There is a quota, but it’s much longer than 146 rows, so that’s surprising. There’s also a quota on url fetch, but it’s like 20k. Do you pull information from APIs in any other of your sheets?

  3. Hi there. I am trying to use the new URL inspection addon for sheets but when I try to run the script I get a “This app is blocked. This app tried to access sensitive info in your Google Account. To keep your account safe, Google blocked this access.” message and I can go no further.

    I don’t suppose you know how to remedy this? I have followed the setup as per your instructions.

    Thanks πŸ™‚

    Laurie

    1. Sorry to hear you’ve got this error! I think this one is mainly an issue with privacy settings in Google accounts. Do you use a business account or personal?

  4. What if you’re not an owner (delegated access) of the Google Search Console property that you’re trying to work on and are unable to “add users”? Any ideas on a path forward?

    1. Hey Matthew, tough one! I think you’d need to ask the owner of the property if they could add a user. Not sure there’s a way around this one with the current setup.

  5. Hi Andrew,

    I’m very excited to use the tool that you created, but I’m having some trouble completing the final step in the process.

    I’ve followed all of your instructions listed in the article. When I click “Run” in the sheet and login to my Google account, I receive the following message “This app is blocked
    This app tried to access sensitive info in your Google Account. To keep your account safe, Google blocked this access.”

    I’ve Googled around a bit and tried to find a solution, but I’ve been unsuccessful so far. Do you know of a solution to this?

  6. Hi Andrew,
    I’m following the instructions to the letter. But I get “Error: Access not granted or expired.”.
    Any idea why this might appear? Google’s “details” error box says “Error: Access not granted or expired.”

    Not much help there.

    Keith

  7. I guess others have the same issue as I when running the script:

    This app is blocked
    This app tried to access sensitive info in your Google Account. To keep your account safe, Google blocked this access.

    Do you have a solution for this as well? πŸ™‚