Connecting Search Console API to Google Spreadsheets

If you’re looking for a guide on how to connect Google Search Console API to Google Spreadsheets, this article is just the ticket.

Why to connect Google Search Console API to Google Spreadsheets?

This setting will help you download the following data from Google Search Console (GSC):

1. Search Analytics: the one that can be found in the “Performance” section in GSC.
2. Sitemaps: adding and deleting sitemaps, getting information on definite sitemaps.
3. Sites: adding and deleting sites (in GSC), getting information and lists of sites in GSC.
4. URL Crawl Errors Counts. Getting information on the number of errors which belong to the following types: authPermissions, flashContent, manyToOneRedirect, notFollowed, notFound, other, roboted, serverError, soft404 in terms of mobile, smartphoneOnly and web.
5. URL Crawl Errors Samples. Getting information on errors, list of URLs where the errors have been detected, marking URL as “fixed” and remove it from the list.

How to connect Google Search Console API to Google Spreadsheets

1. Make a copy of the table we’re going to connect Google Search Console API to. OAuth2 library has been already connected to it.

2. Go to the “Script editor”.

3. Go to the “Cloud Platform project” section.

4. Then follow a link:

5. We got into the Google Cloud Platform. Start typing “Search Console API” in the Google Cloud Platform search line and follow a link.

6. Click on the “Enable” button.

7. Then go to “Credentials”.

8. Follow a link:

9. Create “OAuth client ID” credentials.

10. Select “Web application”.

11. Paste https://script.google.com/macros/d/{sript_ID}/usercallback line to the “Authorized redirect URLs” field.

• ({sript_ID} should be taken from the Apps Script editor. Just click on “File” — "Project properties").

12. Then you’ll see a window with your client ID and your client secret. Save the information provided in these fields; you’ll need them when working with Google Search Console API in Google Spreadsheets.

13. Then go to the script, open “Variables” tab. Paste your client ID for a “CLIENT_ID” variable and your client secret for a “CLIENT_SECRET” variable.  Click “Save”.

14. Open the document we copied in previous steps and run a script for the final authorization:

15. Click “Continue”.

16. Choose the required account:

17. Follow tips on the following screenshot:

18. Click on “Allow”:

19. Copy URL, open a new tab, insert the link and follow it.

20. If you have properly done everything, you will see a window to specify your profile and allow to access to your account (like we did in paragraphs 16 and 18).
21. If you see this window, Google Search Console API has been successfully connected:

22. To make sure that everything works properly, open the document and rerun the script (like in p.14). As a result, you’ll see all the sites available in Google Search Console account on the “Sites” tab.

Failed to connect? Feel free to describe your problems in comments. I will be glad to help.

Conclusions

1. Having analyzed the possibilities of GSC API methods, we managed to automate the process of site checking in this tool.
2. You may ask: “Why not to use the Add-on, which helps to download data from GSC?” Surely, the Add-on is more simple to use, but it allows to download reports from the “Performance” section only while Search Console API allows to download reports from other sections as well.