Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Adding Stat Reports to Google Sheets using App Scripts
//menu
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Manual Update')
.addItem('Import Keywords', 'importKws')//Copy to add additional functions
.addToUi();
}
var SEARCH_QUERY = "from:noreply@getstat.com subject:Download your STAT report: " //stat report email address and subject
var COMPANY_NAME = "MacMillan Search" //company name variable that we put in parentheses when we create the report in Stat
//grab the link from the email
function getEmails_(q) {
var emails = [];
var threads = GmailApp.search(q, 0, 1); //get only the latest email - limits!!!!
for (var i in threads) {
var msgs = threads[i].getMessages();
for (var j in msgs) {
emails.push([msgs[j].getPlainBody().split('Link: ')[1].split('\n')[0].replace(/\s/g, "")]); //extract the link from the email and remove final whitespace
}
}
return emails;
}
//does the heavy lift of taking the email link, unzipping and adding it to the sheet
function updateSpreadsheet(NAMING_ARRAY) {
var url = getEmails_(SEARCH_QUERY+NAMING_ARRAY[0]+" ("+COMPANY_NAME+")"); //gets the link
var zipblob = UrlFetchApp.fetch(url).getBlob();
var unzipblob = Utilities.unzip(zipblob);
var unzipstr=unzipblob[0].getDataAsString('UTF-16'); //charset this took me way to long to figure out
var csv = Utilities.parseCsv(unzipstr, "\t");
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(NAMING_ARRAY[1]);
ss.clearContents().clearFormats(); // clear the old contents
ss.getRange(1, 1, csv.length, csv[0].length).setValues(csv)
}
//The settings for each report these 2 variables & the function name are all you need to change
function importKws() {
var REPORT_NAME = 'Rankings'; //report name minus the company or project name and parentheses
var SHEET_NAME = 'Rankings'; //sheet name
var NAMING_ARRAY = [REPORT_NAME,SHEET_NAME]
updateSpreadsheet(NAMING_ARRAY);
}
@mhawksey
Copy link

mhawksey commented Jan 18, 2022

Thanks for sharing. However, looks like the script has been cut short at lined 37?

@MacMillanSearch
Copy link
Author

MacMillanSearch commented Jan 18, 2022

Thanks for pointing that out @mhawksey I just updated it with the missing lines

@mhawksey
Copy link

mhawksey commented Jan 19, 2022

@MacMillanSearch brilliant - thanks for the quick update

@rollanb
Copy link

rollanb commented Feb 28, 2022

@MacMillanSearch thank you for this. I tried tracing your steps but I can't get it to work.

I keep getting:
"TypeError: Cannot read property 'clearContents' of null"

I've attached a screenshot of the script editor. Do you have a recommendation?
Screen Shot 2022-02-28 at 12 05 11 PM

@MacMillanSearch
Copy link
Author

MacMillanSearch commented Feb 28, 2022

@rollanb did you follow all of the steps from the Moz article? https://moz.com/blog/add-stat-reports-to-google-sheets

@rollanb
Copy link

rollanb commented Feb 28, 2022

@MacMillanSearch I thought I did, that's where I found you :)

I thought the key steps were:

  1. Naming the company between brackets
  2. Editing the report name
  3. Editing the sheet name

I can't figure out what's causing "TypeError: Cannot read property 'clearContents' of null"

@MacMillanSearch
Copy link
Author

MacMillanSearch commented Mar 3, 2022

@rollanb are you running it from the editor? or the sheet?

I might need to be shared on it to help trouble shoot it further

@MacMillanSearch
Copy link
Author

MacMillanSearch commented Mar 3, 2022

@rollanb I just had a thought, is the var SHEET_NAME the name of the tab in the sheet? Or the Spreadsheet?

@rollanb
Copy link

rollanb commented Mar 4, 2022

@rollanb are you running it from the editor? or the sheet?

I might need to be shared on it to help trouble shoot it further

Hi Michael,
Thanks for the response. I was running it from the sheet from the Manual Update > Import Keywords button.

Screen Shot 2022-03-03 at 6 11 55 PM

@rollanb
Copy link

rollanb commented Mar 4, 2022

@rollanb I just had a thought, is the var SHEET_NAME the name of the tab in the sheet? Or the Spreadsheet?

The name of the Spreadsheet.

Should it have been the name of the tab at the bottom of the Spreadsheet? Image attached.

Screen Shot 2022-03-03 at 6 14 23 PM

@MacMillanSearch
Copy link
Author

MacMillanSearch commented Mar 4, 2022

Yes, the name of the tab at the bottom. "Technically" it is a "sheet", but I can appreciate the confusion. I'll reach out to Moz to get that details clarified on the post, let me know if it works now.

@rollanb
Copy link

rollanb commented Mar 4, 2022

Gotcha! It works now. I was able to run it a few times successfully, thank you 🙏 I'm going to play with this over the next few weeks.

Is it possible to run more than one script on one Spreadsheet, on different tabs?

@MacMillanSearch
Copy link
Author

MacMillanSearch commented Mar 4, 2022

@rollanb We have run it up to 3 and it could go higher in theory. There are some row limits you might need to deal with.

@rollanb
Copy link

rollanb commented Mar 4, 2022

I'll give it a go. I appreciate your responses and for making this script. Thank you 🙏

@rollanb
Copy link

rollanb commented Jul 18, 2022

@MacMillanSearch Hi Michael, I've been using this script successfully on multiple Spreadsheets and it's been a huge timesaver. Thanks again!

I've been trying to implement the script on multiple tabs (sheets) on the same Spreadsheet but with no luck. Would you be able to share an example of three scripts running on the same Spreadsheet?

I have two, sometimes three emailed STAT reports in my inbox, that I would like to put into the same Spreadsheet using your script but so far only one sheet would update.

I made sure I edited the COMPANY_NAME, REPORT_NAME and SHEET_NAME, but I think I'm missing additional functions? I tried adding additional functions with unique names but I don't think I did it correctly. Again, only one sheet would update.

Would you be able to share an example I could learn from?
Thank you.

@MacMillanSearch
Copy link
Author

MacMillanSearch commented Jul 19, 2022

@rollanb I am super glad to hear, please connect with me on https://www.linkedin.com/in/michaelkmacmillan/ as I would love to hear more about your use cases

the trick is to make copies of the function

function importKws() { var REPORT_NAME = 'Rankings'; //report name minus the company or project name and parentheses var SHEET_NAME = 'Rankings'; //sheet name var NAMING_ARRAY = [REPORT_NAME,SHEET_NAME] updateSpreadsheet(NAMING_ARRAY); }

For each report you want to pull in and give them all unique function names and then name your variables as needed, an issue I ran into is having report names that share words, so make sure there is no overlap in those variables.

Let me know how you make out.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment