Sending Daily Reports to a Slack Channel from Google Sheets

If you’ve ever done any kind of commission or service work for many people, and at some point started to outsource or hire out that work, you’ve probably experienced the stress that comes with depending on other people to make sure work gets done in a timely manner. There’s always the chance someone will forget to finish the work for one of your clients, especially if you have several new clients signing up every day.

Where I work, clients and services are organized in a Google Sheets spreadsheet, with every purchase having it’s own row containing information about the purchase type, what services were purchased, whether or not we have the information we need to run that service, and the date it was ready by (among other info). The spreadsheet is now several thousand rows large, with limited sorting capabilities. It take several minutes to scan through the list and find out if some clients have been waiting longer than they should be to get what they purchased done. It’s something that’s easy to forget to do, yet something that’s necessary to make sure nothing is forgotten.

To solve the issue and make sure everyone is kept up to date with the current status of the different services that need to be done, I hooked into Slack’s bot API from inside Google Sheets. Early every morning (before the work day started), it would send a report with all of the services ready to fix (usually 5-20), the age of each one, and some other useful data.

slack-report.png

How Did I Do it?

Using Google’s (quite extensive) Scripts service, I scanned the spreadsheet using the Sheet class and the UrlFetchApp library to send an API request to Slack’s API, all in JavaScript. Then, I set up a Scripts Time trigger to run the function that sends the report, every morning at 4-5am.

This feature is something I definitely plan to use for other types of reports, I may even expand it to make queries from Slack to retrieve & transform data from the spreadsheet.