Using Slack Slash Commands to Send Data from Slack into Google Sheets

By  on  

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.

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:

function doPost(e) {
  if (typeof e !== 'undefined') {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Sheet1');

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:

function doPost(e) {
    if (typeof e !== 'undefined') {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getSheetByName('Sheet1');
        sheet.getRange(1, 1).setValue(JSON.stringify(e));
    return ContentService.createTextOutput(':books::nerd_face: Thank you for your book recommendation! :tada:');

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:

var parameter = e.parameter;

Then it’s a case of digging down to get what you need, e.g.:

var bookName = parameter.text;

The full code for your app should now look something like this:

function doPost(e) {
  if (typeof e !== 'undefined') { 
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var sheet = ss.getSheetByName('Sheet1');
    var lastRow = sheet.getLastRow();

    // extract the relevant data
    var parameter = e.parameter;
    var teamDomain = parameter.team_domain;
    var channelName = parameter.channel_name;
    var userName = parameter.user_name;
    var bookName = parameter.text;
    var date = new Date();
    var slackDetails = [date,teamDomain,channelName,userName,bookName];

    // paste the slack details to the sheet
    sheet.getRange(lastRow + 1,1,1,5).setValues([slackDetails]);
  return ContentService.createTextOutput(':books::nerd_face: Thank you for your book recommendation! :tada:');

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:

// function to get book details
function getBookDetails(bookName) {
  // empty array to hold book data
  var bookData = [];
  try {
    // call google books api
    var url = '' + encodeURI(bookName) + '&country=US';
    var response = UrlFetchApp.fetch(url);
    var results = JSON.parse(response);

    // choose first book
    var book = results.items[0]; 

    // Get the book info
    var title = book.volumeInfo.title || 'No Data Found';
    var subtitle = book.volumeInfo.subtitle || 'No Data Found';
    var authors = book.volumeInfo.authors.join() || 'No Data Found'; // join to put multiple authors into string

    // add book data to array 
  catch (e) {
    Logger.log("Hmm, something went wrong. Hint: " + e);

 // return to main function
 return bookData;

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).

// retrieve the book details
var bookData = getBookDetails(bookName);

// combine arrays
var fullData = slackDetails.concat(bookData);

// paste the slack details to the sheet
sheet.getRange(lastRow + 1,1,1,fullData.length).setValues([fullData]);

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:

var result = { 
  'text': ':books::nerd_face: Thank you for your book recommendation! :tada:', 
  'attachments': [
      'title': bookData[0],
      'author_name': bookData[2],
      'image_url': bookData[3]

return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);

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.

That’s it from me.

Time to choose a new book to read...

Ben Collins

About Ben Collins

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.

Recent Features

  • By
    Regular Expressions for the Rest of Us

    Sooner or later you'll run across a regular expression. With their cryptic syntax, confusing documentation and massive learning curve, most developers settle for copying and pasting them from StackOverflow and hoping they work. But what if you could decode regular expressions and harness their power? In...

  • By
    CSS @supports

    Feature detection via JavaScript is a client side best practice and for all the right reasons, but unfortunately that same functionality hasn't been available within CSS.  What we end up doing is repeating the same properties multiple times with each browser prefix.  Yuck.  Another thing we...

Incredible Demos

  • By
    Upload Photos to Flickr with PHP

    I have a bit of an obsession with uploading photos to different services thanks to Instagram. Instagram's iPhone app allows me to take photos and quickly filter them; once photo tinkering is complete, I can upload the photo to Instagram, Twitter, Facebook, and...

  • By
    MooTools 1.2 Tooltips: Customize Your Tips

    I've never met a person that is "ehhhh" about XHTML/javascript tooltips; people seem to love them or hate them. I'm on the love side of things. Tooltips give you a bit more information about something than just the element itself (usually...


  1. Kevin
    var lastRow = sheet.getLastRow();

  2. Joe Sasson

    For anyone getting an error at the Parsing the data into your Google Sheet step.

    I realized that the lastRow was not defined. You fix it by adding the following line anywhere above setValues call:

    var lastRow = sheet.getDataRange().getValues().length
  3. To @Joe and @Kevin, good spot! My bad, somehow that line didn’t make it into the post. I’ll message David to get it fixed here.

  4. Ry

    Hi, Is it possible to choose which columns the date goes into? so right now it goes into A1 – E1 etc starting from Date and ending in bookName (before adding the api details)
    is it possible to have data go into C1 for example instead?

    Also can you make the confirmation message in slack to be visible to everyone?

  5. Chanell

    I am kind of stuck! I received back the big html message via slack, but the line did not go over to a1 in my google sheet. Not sure if you know where i went wrong?

  6. Mikaela

    Is there any way to add some security around this? It makes me apprehensive to “Allow anyone, even anonymous” – I’m planning on using functionality like this to store data requests from other departments in a google sheet, but i’d rather not let anyone access the information that is being passed from Slack to GoogleSheets.

    I’m not super informed on security measures like that, so it may not be an issue, but wanted to see if you had more information!


  7. Michael Telis

    >> However, Google Apps Script is synchronous, so unfortunately there’s not a way
    >> to use this delayed response pattern.

    It’s possible to save requests to spreadsheet and create a trigger to start processing within a few milliseconds, like this:


    delayedProcessing would then fetch requests from the datasheet and process them one by one until the queue is empty.

    I have a proof of concept and it works. Need to figure out how to lock spreadsheet while I’m updating request queue.

  8. Journie

    I’d like to connect a google sheet to my slack app that belongs to my company’s internal google system. Since it’s not my personal google account, the option to have “anyone, even anonymous” access the app is not available to me. I only have the option to either make me the sole person who can access the app or anyone within my company access the app. Both of these return 401 errors. Is there a way to get around this? I’m not allowed super admin privileges so I think I need some way for the Google Sheet to recognize my slack app as a company member. Thanks!

  9. kishen

    Is there any documentation on how to retrieve data from google sheet via a slack bot?

  10. Sohail

    Hi Ben,

    Thanks for such a brilliant tutorial.
    I was doing the same and came to find your article for searching about an issue that I am facing now.

    Rather than using the published web app url which we need to republish each time we change the code, I wanted to use the test-mode web app url (that ends with /dev). But, somehow, that does not work and it seems that my doPost(e) app is unable to receive that.

    Is there any way out? or there is some limitation on this test-mode url by Google?

    Thanks in advance

  11. tento14

    Amazing tutorial. Is there any chance to set the response in slack visible in channel not only in “Only visible to you” option?

  12. Alyssa

    Hi Ben, thank you for this tutorial. Is there a way to get around the ‘401_client_error message’ without setting the who gets to use the Google Sheet app to ‘Anyone, even anonymous’? I am trying to create a Slack to Google Sheets integration for my team and using my work Gmail account to authorize the app. I do not have that option to allow anyone to use it, just anyone within my company at most. However, I still get the client error message with that setting anyway. Thank you!

  13. Christopher Mondoux

    Hey David-

    Great instructions here! My questions is: I don’t see the option to choose “Anyone, even anonymous” in the pick list when I deploy as Web App. Do you know how I make that change? I’m on an enterprise G-suite account, so it’s possible that IT manages those permissions?

Wrap your code in <pre class="{language}"></pre> tags, link to a GitHub gist, JSFiddle fiddle, or CodePen pen to embed!