Automate your Google Sheet email report with Google Script
So, you are managing a product backlogs or project progress with Google Sheet, and there are multiple stakeholders involved. They all want constant updates, and you don’t have time to both updating and sending the spreadsheet every time you make some changes.
Sending them the link will via a group chat sounds easy enough, if only they could move some finger muscles to actually click it. I’ve worked with executives lazy enough to request sending the whole spreadsheet in the email, or at the least send a pdf. So, yeah…
Mind you, this is a super simple script (no more than 50 lines of code) and the whole process is just as easy as copy and paste, modify the receivers, contents and the set a schedule with Google Script Trigger tool. More fancy modifications will require some tweaks but if you are only looking into sending everything in the sheet as a table, this one is for you. Let’s begin!
What kind of spreadsheet do you have?
I’d assume you have a bunch of texts (and/or graphs). Cool, cool cool cool! Let’s say it looks something like this:
Let’s start
Now, let head to the menu and find ‘Script editor’ in ‘Tools’:
Click it, a new page will appear that looks like this (without the red annotations, of course):
This is the Google Apps Script interface. Felt like a programmer yet? No? Ok, continue…
Give your project a name, then head down to paste this to your code editor:
Looks something like this:
See those lines of code in the red box? Input your own information (ie: receiver emails) and texts to personalise the email.
Once done, save it (the floppy disk icon, of course), then try it by clicking ‘Run’.
But hold your horses, Google doesn’t just let you run any code on your own profile just like that. You’ll be greeted with this warning:
It is self-explanatory enough, yeah? Just click that ‘Review permissions’ to verify the code. Another warning will appear:
Select ‘Advanced’ -> ‘Go to <your project name> (unsafe)’.
Now for those non-coders who are murmuring “wait, is this safe? what if I get haxxed?”. Trust me, this type of hack is a “time-saving hack”. So go ahead and get your script verified. Google will list some permissions and ask you to allow your script to do something, again. You know what to do.
Once that’s done, thank god, your script will run, like this:
It’s done, you codes ran, no hiccups, hurray! Quick, go check the receiver email or your Sent folder.
Brilliant! That’s it.
“Wait, but I have to manually run it everytime?”
Hang on, how about we schedule it to send at specific time? Simple, Google let you schedule time to trigger your scripts with simple UI.
On the left menu, you can see the clock icon labelled “Trigger”. Click ‘Add Trigger’ and a pop up will show like so:
Leave the first to selection default as that will be the function to trigger. From here, you can select the type of schedule you want. For me, I want to send it every week on Monday between 8–9PM. So here’s my schedule:
You can set even more fancy schedule like sending this email if there’s a Google Calendar update.
On the right side of that pop-up, you can also see the option to notify you via email if the script failed to run. I always set it as immediately.
‘Save’ it and forget about it. Now, all you have to do is keep your table updated.
I have been using this script for a year now, keeping my internal stakeholders updated weekly on my team progress. So far, the script never failed to send even once and everyone gets their email promptly on Friday afternoon.
***Some notes:
- If you login to multiple gmails, make sure to do this on the default account, or you might not be able to use the Script editor.
- If you change your sheet name (default is “sheet1"), find getSheetByName(“sheet1”) and replace sheet1 by whatever name of the sheet you have.
- Check the Google Services quotas here if you have a large email list: https://developers.google.com/apps-script/guides/services/quotas