We all know that we can share Google Sheets with people but, at least in my life, those people are often not regular spreadsheet users, so they aren’t comfortable opening a Google Sheet to look for updated data. For example, when we set up online forms (for event registration, lunch signup, etc), the parent volunteers or business office staff don’t interact with Google Sheets every day, so I end up fielding regular requests for updates.
What I usually do is add a sheet to the Sheet (heh) which summarizes the data into a simple table and then use the Email Collaborators command in Sheets to send them an update. But, today, I found a cool Add-On called Email Spreadsheets which makes it super-easy to automate this regular sharing.
We are currently collecting payment and registration for an upcoming ice skating social event. The form feeds the data into a Sheet. I added a Summary worksheet that calculates the total number of families who registered, the total number of skaters and the total funds collected so far.
Then, using the Add-On, I was able to use standard “dynamic cell values” notation to reference the cells which contain the summary data and add it to an email template that will be automatically sent to the people I want, on the schedule I send. So, every weekday morning, all of the people involved will get an email that looks like this:
The data in the table is pulled out of my summary sheet in real-time using this reference:
… and I added that link at the bottom using standard HTML.
The Add-On lets you specify who receives the email (both CC and BCC) and has the standard scheduling options (daily, weekdays, weekly, etc.).
You can also choose to attach your spreadsheet in various formats, but that’s not really useful for my use-case. It’s a pretty powerful tool and feel like it’s probably going to become something I use pretty often. I’d love to see functionality that allows me to trigger the email based on values in the spreadsheet, like every ten submission or when a certain cell contains a certain value, but it’s pretty useful as is.
Check it out and let me know what you think. 🙂