Since I work from home, most of my daily work interactions happen through Slack. It’s my equivalent to the water cooler. A place to hang out and discuss ideas with friends.
Looking for an easy spreadsheet integration? Try Formsite's Google Sheets integration & send form results to a Google spreadsheet. Learn more today! Download Slack 2021 for Windows - Slack for Windows Latest Version 2021 for Windows PC 10, 8, 7 (32-bit and 64-bit) free download. Slack is an application program that operates to work together. Set up the Google Sheets trigger, and make magic happen automatically in Slack. Zapier's automation tools make it easy to connect Google Sheets and Slack.
I’m part of a book recommendations channel. People share books all the time, but they disappear quickly, lost in a sea of messages.
I needed a way to log the books people recommended.
So I built a little Slack app to send book recommendation data directly into a Google Sheet, using Google Apps Script.
By the end of this tutorial, you’ll have created your own Slack App to log data in a Google Sheet.
Here it is in action:
What are Slack Slash Commands?
Slack Slash Commands let users trigger an interaction with your app directly from the message box in Slack.
Type the forward slash into your Slack message box and you’ll see the list of default slash commands:
E.g. /active, /away, /archive, /invite, /mute, /shrug
are all examples of Slack Slash Commands.
By the end of this tutorial, you’ll have created your own Slash Command, which will show up in this list too:
Check out the Slack documentation for more on Slash Commands.
Using Slack Slash Commands to send data to Google Sheets
Initial Setup with Google Sheets and Apps Script
Obviously for this to work, you need a Google Sheet to act as the data store, so first step is to create a new Google Sheet. We’ll use Apps Script, the Javascript-based scripting language, to add functionality to this Google Sheet.
Then open the Apps Script editor from the Tools menu: Tools > Script editor
Delete the boilerplate myFunction() code and replace it with the following few lines:
When your Slack app sends your Google Apps Script app an HTTP POST request, the Apps Script runs this doPost(e) function, which pastes the data as a string in cell A1 of Sheet 1 of your Google Sheet.
Before it can do that though, you’ll need to publish this Apps Script project as a Web App.
From the Script Editor window, choose Publish > Deploy as web app…
You need to set the access to “Anyone, even anonymous” otherwise you’ll see a 401_client_error message in Slack when you try to use the Slash Command.
When you click Deploy, you’re prompted to authorize your app.
When this is complete, you see a confirmation popup with the Current web app URL. Copy this as you’ll need it later in the Slack App.
Later, as you make changes to the script code, you need to come back to Publish > Deploy as web app… to create a new version and Update it:
Ok, that’s the Google Sheet setup (for now). Let’s head back over to Slack and create a new app.
Creating your Slack App
Head to the Slack API homepage and click “Start Building”. (You’ll have to login if you’re not already.)
In the “Create a Slack App” popup, give your app a name and choose the Slack Workspace it’s attached to.
When your app has been created, you’ll want to choose “Slash Commands” under the Features menu, and then Create New Command
In the new window, set your Slash Command (e.g. /book) and paste in your Google App URL as the Request URL:
Click Save.
The last step is to “Install your app to your workspace”, which is under the Settings > Basic Information menu in the sidebar.
Click Install App and on the subsequent screen you’ll need to Authorize the App.
When that’s done you’ll see two green tick marks under the Basic Information of your App:
That’s all the setup done! Easy, huh?
Let’s test it!
Sending Data From Slack to Google Sheets
In your Slack app workspace, enter the Slash Command and a book title, e.g.:
/book the innovator’s dilemma
Hopefully you’ll get an ugly looking garbled mess of HTML right back in your Slack message box. We’ll fix that in a moment.
But look back at your Google Sheet. You should see a long string of data in cell A1, with information about your Slack channel and the book title buried in there.
Boom! That’s what you’re after.
That’s the hard part done. The rest is just details. (But as we know, the devil is in the detail, right?)
What’s left to do:
- Send a confirmation message back to Slack
- Parse the JSON packet to extract the relevant data (e.g. who posted the recommendation and what it was)
Sending a Return Message Back to Slack
At the moment, the script does not send a reply back to Slack to confirm receipt of the data. The user is served an ugly looking bunch of HTML with an error message. Obviously this is no good.
You want to send a confirmation back to Slack to avoid this. You can also customize this return message with some helpful text.
Add this line to the original code:
Don’t forget to create a new version and update your web app through the Publish > Deploy as a web app… menu.
The :books:, :nerd_face: and :tada: strings are converted into emojis in Slack, so that the user is now served a receipt confirmation message:
Parsing the data into your Google Sheet
Back in your Google Sheet, if you look at the long string of data in cell A1, you can see all the fields you need nested under the parameter key.
So you need to get hold of that:
Then it’s a case of digging down to get what you need, e.g.:
The full code for your app should now look something like this:
Again, you’ll need to create a new version and update the web app.
To post data into the Google Sheet, you need to set the range and then set the values, using a double array notation [ [ ] ]. Each inner array is a row of data.
Try posting a book recommendation again, and the output in your Google Sheet will look something like this:
Much better!
That’s the basic app done. But it’s not particularly useful yet.
To make it more useful, let’s make the app:
- Call the Google Books API to get additional details (e.g. book cover, author, number of pages, preview link, etc.)
- Send a comprehensive reply message to Slack.
Getting Book Data from the Google Books API
The idea here is to send the book title to the Google Books API to fetch some additional data, to make the archive more useful. We can connect Google Sheets to external APIs using Apps Script.
Of course, there’s lots of ways to structure your code, but I’ve separated the call to the Google Books API into a separate function and called it with the book title as an argument.
The heart of this new function is the call to the Google Books API:
The data returned from the Google Books API is an array of matches resulting from a search for the book name you supplied.
The script selects the first book result (hey, when you’ve finished this tutorial why not add buttons to the return message to let the user choose which book?) and then parses the relevant fields. This data is sent back to the main function so they can be added into your Google Sheet.
Update the main function to include a call to the getBookDetails function, then concatenate the Slack and Book data arrays and update the range references for pasting data into Sheets (because our row now has more fields).
When you then update and publish a new version, you’re prompted to authorize the Apps Script file to connect to an external service (Google Books API).
Now try posting a new book title through Slack and you will get an output like this in Google Sheets:
A new row of data with additional information about the book!
You can spruce up your Google Sheet by adding titles and formatting.
Another nice touch is to grab the thumbnail Url for the book, and display that in the Google Sheet using the IMAGE function.
The full code for this example, including these additions, is available here on GitHub.
Sending a more complex message back to Slack
Instead of just the single line of text in the current response, you can send much richer replies back to Slack (and even add interactivity like buttons etc.).
In this Apps Script case, you can create a JSON object and then send it back as a string to Slack.
The code for this is:
Now, your users will see a nicer reply message in their Slack window:
Delayed Response Messages?
Slack expects a response to a Slash Command within 3000 milliseconds (3 seconds) otherwise your user will see a “Timeout was reached” error message.
So if you receive data from Slack and want to do other things with it, there’s a danger of hitting this timeout limit.
To deal with this, Slack Slash Commands provide a delayed response URL in the data packet you get. The idea is that your app sends an immediate response to say it received the data (“Hey, I got the book recommendation!”) and then later it can reply to the response URL when it’s finished other tasks.
However, Google Apps Script is synchronous, so unfortunately there’s not a way to use this delayed response pattern.
In this example though, the call to the Google Books API is lightning fast since the data involved is so tiny, so it all happens pretty instantaneously.
It’s worth knowing about for other apps though, where this might be required.
Slack Bot Spreadsheet
That’s it from me.
Time to choose a new book to read...
Slack Excel Spreadsheet
About Ben Collins
Slack Spreadsheets
Ben Collins is an educator, data analyst and Google Developer Expert focussed on G Suite Technology, specifically Google Sheets, Apps Script and Data Studio. He is the founder of The Collins School of Data where the newest course is Apps Script Blastoff, a free, introductory course for Google Apps Script. He creates online courses and write about working with data, with a particular focus on tutorials for data analytics, automation and dashboards, using Google Sheets, Apps Script and Data Studio. Ben previously taught Data Analytics for General Assembly and began his career as a forensic accountant.