Adding STAT Reports to Google Sheets Using App Scripts

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Get Stunning Website in just 30 days

Services on: Website Design & Development | Website revamp | SEO | Social Media Optimization | Digital Marketing 

The creator’s views are solely his or her personal (excluding the unlikely occasion of hypnosis) and should not at all times replicate the views of Moz.

The crew at MacMillan Search has generated loads of worth by combining automated STAT reviews with Google Sheets. From including rating particulars to different instrument’s outputs, to giving the content material groups up-to-date “People Also Ask” reviews, the top consequence has confirmed to be a terrific time-saver in our week-to-week website positioning workflows by lowering guide work and offering normal outputs that simply combine with any spreadsheet.

Why did we create this script?

STAT’s wealth of key phrase rankings particulars may be very helpful for enterprise SEOs to perceive each the macro and micro particulars of their rankings. Google Sheets is among the commonest cloud-based spreadsheets platforms, and is straightforward to share between groups and organizations. That’s why SEOs use each of those instruments commonly when analyzing key phrase information.

Regardless of this, documentation on how to combine STAT into Google Sheets is proscribed. To deal with this hole, we created our personal script!

It’s confirmed helpful for a number of causes:

  1. Not everybody likes CSVs: We leverage the STAT reviews to present shoppers with course. Having to obtain a CSV and open it each week isn’t for everybody. With this script, you may set a weekly ticket with a hyperlink to the spreadsheet, and evaluation the output commonly.

  2. It saved us time: website positioning is a marathon, not a dash. Once we determine a chance, there may be ongoing work that can have us reviewing reviews commonly. The weekly ticket method to evaluation a spreadsheet shaves a while off of every activity, and over the course of the engagement, this saved time provides up.

  3. Cleaner output: Using Vlookups, Uniques, and so on., you may create a abstract web page of this data, highlighting what shoppers and/or readers care about. It’s also possible to combine this data with different information sources.

  4. Create automation with out utilizing an API: Automation, when performed accurately, saves time. Using this script with triggers opens the door to automation.

How to implement this script

1) Create a report in STAT

The STAT information base has a terrific useful resource on reviews. The one factor we might get particular on is the naming of the report and the recipient e-mail.


What you title your report just isn’t as essential as maintaining it clear and concise. This makes scaling to different initiatives with comparable reviews cleaner and simpler. Additionally, you will use this report title as one of many variables within the scripts.

We additionally counsel putting the corporate or venture title on the finish of the report title in parentheses (e.g. “(MacMillan Search)”). This makes it simpler to discover the report in your e-mail.

Recipient e-mail

It’s essential to use a Gmail-enabled e-mail for the account the place you’ll be constructing the sheet. This manner, Google has a better time getting the app script to extract the CSV from the e-mail.


For our shoppers, weekly information is essentially the most helpful — sufficient element to spot traits, however not a lot that it turns into simply noise to be ignored. For reviews with restricted fluctuations (e.g. Individuals Additionally Ask), month-to-month may be passable.


Choose “Run this report immediately” to verify that your report works, proper after creating the script. This manner, you’re prepared to set your triggers and let the information circulate.

The remainder of the settings are particular to what particulars you need out of your report.

2) Create a Google Sheet and add the script

Create a brand new sheet in Google Drive underneath the account related together with your report’s recipient e-mail. Then you definitely’re prepared to add the script:

1. Underneath the menu “Tools”, choose “<> Script editor”

2. Paste the script under into the “Script editor”.

3. A number of issues will want to be edited to work together with your information:

  • var COMPANY_NAME up to date to the corporate or venture title you used whereas creating the STAT Report

  • var REPORT_NAME up to date to the title of your report minus the corporate title and parentheses

  • var SHEET_NAME up to date to the title of the sheet within the spreadsheet

4. Verify the Script works by saving it, refreshing the sheet, and when the menu “Manual Update” hundreds, choose “Import Keywords”.

5. The primary time you run this you’ll get an “Authorization Required” pop-up:

Choose “Continue”, observe the steps, and choose “Import Keywords” underneath the menu once more.

Your spreadsheet ought to now be populated with the entire particulars out of your CSV.

3) Automate the inhabitants with triggers

Setting this sheet up to robotically replace because the report comes out may be very straightforward utilizing Apps Script “Triggers”. To arrange the triggers:

1. Return into the “Script Editor”

2. Choose the “alarm” icon “Triggers”

    3. Choose “Add Trigger”

    4. Choose the perform “importKws”

    5. Choose occasion supply “Time-driven”

    6. Choose sort of time-based set off “Week Timer” for weekly reviews, “Month Timer” for month-to-month reviews, and so on.

    7. In our time zone, our reviews normally come out late Sunday, so we decide early Monday morning:

    8. Click on “Save”

      The result’s a spreadsheet that commonly updates, populated by an emailed STAT report.

      We’ve discovered many makes use of for this script — wherever we reference rank. And, since a venture may take time to get carried out, we will present present rating data with out leveraging the API.

      We’re curious to learn the way you leverage it as nicely. In the event you discover the script helpful, attain out to us on LinkedIn and tell us what you’re utilizing it for.

Courtesy: Source

#Adding #STAT #Reports #Google #Sheets #App #Scripts

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp


Chennai's Best Website Design & Development hub. We create Professional stunning WordPress websites and doing Digital Marketing to scale up your business.

Latest Articles

Happy to help you