This article is for those who are interested in integrating this small automation system for your work.

Although Workflow Builder for slack is a faster way to link google sheet with slack without any code, it cannot be used for conditioning.

For instance, user would like to filter out the only messages needed to be written on to google sheet, or trigger some other actions when particular stamp or reply is added.

By using Google App Script and some simple codes, the customizable range is almost infinity!

Create Slack App through api.slack.com/apps

  1. Create new app on slack api website
  2. Choose from scratch on the next page
  3. Enter App Name you like, then pick a workspace for your app
  4. A homepage for the app will be created.
  5. For this use case, we will be using the Event Subscriptions setting feature.

Create Google Sheets and write your code on Google App Script

Prepare the sheet you are going to use. Then go to App Script Editor.

Everyone can use Google App Script as long as one has a google account.

You need to deploy your GAS project to be able to receive POST requests for the events you subscribed. First, we need to have something to process the request.

  1. In the Apps Script editor, you’ll see a default Code.gs file. You can start writing your script in this file or create new files by clicking on the “+” button in the left sidebar.
  2. Add the code below in doPost function to respond to Slack api’s verification POST request. This is just a simple code to let slack know that you are responding to them when slack sends POST request to your GAS URL when events happen.

Deploy your GAS

  1. On the top right of the editor, there is a blue button to deploy your script. Open it and choose new deploy
  2. On the left hand side of the pop up modal, you can choose the type to deploy. We will choose web application here.
  3. Then write some easy-to-remember description for your GAS, execute as yourself, and give access to anyone (for slack to be able to execute your app).

Event Subscriptions

Add the URL to your slack app, pick the events you want to subscribe for your bot to be noticed whenever the particular events happen in the channel your bot is joined.

For my use case, I subscribe message.channels event for every new message posted in the channel, reaction_added and reaction_removed event for every reaction users added and removed in the channel.

When subscribing to bot events, OAuth & Permissions will be automatically added, so user need not to worry about permissions.

But also make sure your app has the particular permissions.
For above events, you need channels:history and reactions:read for your app.

Install your Slack app into your slack channel

First, go to App Home on your slack app page, then edit your app bot name to your desired name. This name will be your bot’s name on slack.

Next, you will be able to install your app into your workspace from Basic Information page.

  • After you install your app, every time you make any changes to it, you have to reinstall it to your workspace to apply changes.

After that, add your app to the desired channel by opening your channel details (click the channel name on the top of your channel) > integration tab > App.

Your app is ready to use!

Now your app is successfully installed into your channel and you will get every event you’ve subscribed to!

Then you can do the real magic in GAS now!

For example, in your GAS editor, below code can write the Slack message URL on to your sheet whenever new message is posted in the channel.

if (json.event.type == "message"){ 
    var slackChannelId = json.event.channel; 
    var messageTS = json.event.ts; 
    var slackUrl = `https://YOUR_WORKSPACE.slack.com/archives/${slackChannelId}/p${messageTS}`; 
    spreadsheet.getRange(spreadsheet.getLastRow() + 1, 1).setValue(slackUrl); 
}

Or when user reacted with some reactions you want to record for, you can also process them.

if (json.event.type == "reaction_added" && json.event.reaction == "hand"){ 
    var userID = json.event.user; 
    // look for your url in google sheet 
    checkUrl = spreadsheet.getRange("A:A").getValues();  
    var dataRow = -1; 
    for(var i = 1; i < checkUrl.length; i++){ 
        if (checkUrl[i][0] == slackUrl){ 
          dataRow = i + 1;  
          break; 
        } 
      } 
    if (dataRow == -1){ 
        // do nothing when url is not found 
        // or other code for your case 
        return;  
    }  
    spreadsheet.getRange(dataRow, 2).setValue(userID + " raised hand."); 
}

Customize your codes to meet your need by referring to the slack document(https://api.slack.com/events), then you have a sheet to manage your Slack messages!