Back to Blog

How to Connect Slack to Google Sheets Without Apps Script

Slack is the main collaboration hub for Railsware. Our workspace has over 500 channels dedicated to different projects and activities. We regularly analyze the user activity of each channel based on the data exported from Slack. This allows us to determine the viability of specific channels (activities) and get rid of the idle ones. 

For this analysis, we export Slack data to Google Sheets on a monthly schedule. And no, we did not write any script for this. 🙂 How is that possible? Read on to find it out.

Write Slack data to Google Sheets on a schedule

You know that you can export raw data from different sources to Google Sheets using Coupler.io, right? If you don’t, check out the available Google Sheets integrations and some details about this importing tool. Coupler.io provides a Slack importer. It’s a ready-to-use integration that lets you automate export of messages, channels and users from Slack to Google Sheets. We’ll explain how it works in the next section.

Note: if you need to export other data from Slack, or even post messages from Google Sheets to Slack, you can do this using the JSON Client by Coupler.io. Jump to the sections where we explain the details.

Connect Slack to Google Sheets with the Slack importer

Sign in to Coupler.io and click “Add Importer“.

Name the importer whatever you want and complete three high-level steps to set it up:

  1. Source
  2. Destination
  3. Schedule

Step 1: Source

  • Application – select Slack as the source application to take data from. Click “Continue“.
  • Source account – Click “Connect” and log in to your Slack account. Click “Continue“. 
  • Data entity – choose the data you want to export: Messages, Channels, or Users.
  • Search query (messages) – if you’re exporting messages from Slack, you can enter the criteria (for example, @username) to filter data. 

Click “Proceed to Destination Settings“.

Step 2: Destination

  • Destination account – add your Google account to connect. Click “Connect” and log in to Google. Click “Continue”
  • Spreadsheet – select the spreadsheet to import data to. 
  • Sheet – select the sheet in the chosen spreadsheet to import data to. If you type the name of a sheet that does not exist in your Google Sheets document, Coupler.io will create a new sheet with this name. 

To use optional parameters, Cell address and Import mode, click “Continue”. If you don’t want these, click “Proceed to Schedule Settings“.

Step 3: Schedule

If you want to automate exports of data from Slack to Google Sheets, toggle on the Automatic data refresh and customize the schedule. For example, you can automate export of the new Slack messages to Google Sheets every day or every week. 

When ready, click “Save and Run“. This will connect Slack to Google Sheets and export the data. You’ll see the details after that. 

Click “Open” to open the spreadsheet with your Slack data.

Now, let’s check out some use cases you may have for exporting data from Slack to Google Sheets.

Forward Slack messages from a specific channel to Google Sheets and other filter criteria

It’s crucial to filter messages for export from Slack to Google Sheets. Otherwise, the Slack importer will retrieve messages from all channels. That’s going to be a huge number of rows in your spreadsheet. Here are the message filtering options you can utilize:

Description ModifiersExample
Search for messages shared by a specific userfrom:@{user-name}from:@elle.martin
Search for direct messages sent to youto:@{your-user-name}to:@ elle.martin
Search for messages in a specific channelin:#{channel-name}in:#team-marketing 
Search for messages filtered by date*before:{date}
after:{date}
on:{date}
during:{month-or-year}
before:01/01/2021
after:01/01/2022
on:24/02/2021
during:february
Search for a specific emoji reactionhas::emoji-code:has::eyes:
Search for messages that are pinned, saved, or contain a linkhas:pin
is:saved
has:link
*Use MM/DD/YYYY or MM-DD-YYYY date formatting in the United States, or YYYY/MM/DD or YYYY-MM-DD date formatting if you’re outside the U.S. You can also use relative time frames: yesterday, today, week, month, or year.

For example, we need to grab information about the messages sent by the user “osaka-nurlan“. Here is what you should specify in the Search query (messages) field:

from:osaka-nurlan

If you need to specify multiple filtering criteria, just separate them with spaces. For example, we want to export messages sent by the user “linda-boyson” within the channel named “#general” for the previous month, which is February. Here is the query string to apply:

in:#general from:linda-boyson during:february

Forward messages from another Slack workspace to Google Sheets

When you clicked “Connect” to integrate Slack to Google Sheets, Coupler.io connected a specific Slack workspace. If you want to export data from another workspace, you’ll need to add another Slack account with this workspace selected. For this, go to the Source section, click Source account and then “+Add account“. 

In the open window, choose the workspace you need and click “Allow“. Now you can use this Source account to export data from the chosen workspace.

Slack and Google Sheets integration for exporting other data

Messages, channels and users are data entities that are available at Coupler.io ready to use Slack and Google Sheets integration. And what if you need other data to export? For example, you may need to list all pending workspace invite requests

God only knows why you may need this, but let’s use this as an example 😀 

You can get this data if you create a custom Slack – Google Sheets integration using the JSON Client by Coupler.io. It’s a tool that allows you to connect to the REST API and transfer data with it. But first you’ll have to get a Slack OAuth Access Token.

How to get an OAuth Access token in Slack

Create a Slack app

A Slack app is needed to access a range of APIs, which allow you to read, write, and update all kinds of data in Slack. So, let’s build one.

Open api.slack.com and click on Start Building:

Enter a name of your Slack app (you’ll be able to change it any time) and pick a Slack workspace. Click Create App when ready:

Configure Permissions

On the next page, you’ll need the Add features and functionality section. Expand it and click on Permissions:

Now you need to select OAuth Scopes (app’s capabilities and permissions) depending on what you’re going to do with the Slack data:

  • Bot Token Scopes govern what your app can access.
  • User Token Scopes access user data and act on behalf of users that authorize them.

Our task is to retrieve information about all pending workspace invite requests. So, we chose the following User Token Scopes:

ScopeDescription
admin.invites:readView a workspace’s invites and invite requests

After you’ve chosen scopes, you can scroll up and click on Install App to Workspace:

Click on Allow to grant permission to your app to access the workspace. 

If you forgot to specify other scopes, you’ll be able to do this later.

That’s it! You now have your OAuth Access Token, which is needed to set up Slack integration with Google Sheets.

Connect Slack and Google Sheets 

With the Slack importer, we set up the configuration in the browser using the web flow. But Coupler.io also has a Google Sheets add-on to connect your source to Google Sheets right from your spreadsheet. Let’s check out how it works as well. For this, you need to install the add-on from the Google Workspace Marketplace.

Set up a JSON Client importer

Once the add-on is installed, open Coupler.io in the Add-ons tab of a spreadsheet, then click on the +Add Importer button and choose JSON Client.

Similar to the web flow, complete three high-level steps to configure the Slack Google Sheets integration:

Step 1: Source

  • Title – Add the name of your data source 
  • JSON URL – Insert the JSON URL in the form:
https://slack.com/api/{method}

{method} – This is an API method to work with Slack data.

The method to list all pending workspace invite requests is admin.inviteRequests.list

So, the JSON URL will be the following:

https://slack.com/api/admin.inviteRequests.list

Here you can explore the full list of Slack API methods

Click “Show Advanced” and fill out the following fields as well:

  • HTTP Method – Select GET to pull data from Slack.
  • HTTP headers – Apply the following HTTP headers to make authenticated requests to Slack:
Authorization: Bearer {your-oauth-access-token}
Content-Type: application/json

{your-oauth-access-token} – Replace with the OAuth Access Token, which you got after you installed the Slack App to the workspace. 

Here is what the Source should look like:

Step 2: Destination

  • Destination account – click “Connect” and log in to your Google account.
  • Sheet Name – type in the name of the sheet that will be receiving data. If you enter the name of the sheet that does not exist, Coupler.io will create one.

Here is what the Destination should look like:

Step 3: Settings

If you want to automate exports of data from Slack, toggle the Automatic data refresh function on and pick the frequency you want.

automatic-data-refresh-2

Once you’re ready, click “Save & Run” to save your custom Slack to Google Sheets integration and initiate the first data export.

For different data entities, you’ll need to set up separate Slack Google Sheets integrations based on JSON Client. Let’s check out the following examples.

How to export information in a Slack channel to Google Sheets

To export data from Slack about a specific channel, you’ll need to use the conversations.history method, as well as specify the channel’s ID in the URL query string field, as follows:

Specify the channel’s ID in the URL query string field
JSON URL.../conversations.history
HTTP MethodGET
HTTP headersAuthorization: Bearer xoxb-1**********2N
Content-Type: application/json
URL query stringchannel: {channel-ID}

Where to get {channel-ID}

You can find {channel-ID} in the data exported from Slack using the conversations.list method. Here is how it looks:

Or you can do the following:

  • Copy the link of your Slack channel. It looks like this:
https://testforcouplerio.slack.com/archives/C017FR4A1NG
  • The last value in the link is the channel’s ID: 
C017FR4A1NG

Slack to Google Sheets is not sending outgoing data

We’ve tried to export the history of a Slack channel, but got an error – missing_scope:

This means that our app doesn’t have the permission required to retrieve the specified data. To fix that, go to Your Apps, select your app, and add the missing scopes. In our case, this is:

channels:history

Then scroll up and click on Reinstall App to enable the added Scopes:

Now you can run your importer to retrieve the history of a specific channel:

How to add comments on Slack thread to Google Sheets

If you need to retrieve a thread of messages posted to a channel, you’ll need to use the conversations.replies method, as well as specify the channel’s ID in the URL query string field. Here are the parameters:

JSON URL.../conversations.replies
HTTP MethodGET
HTTP headersAuthorization: Bearer xoxb-1**********69-12**********8-uP********************2N
Content-Type: application/json
URL query stringchannel: {channel-ID}

The required scopes include one of the following: 

  • channels:history  
  • groups:history  
  • im:history  
  • mpim:history

Analysis of the data exported from Slack

So, what can you do with the data pulled from Slack? At Railsware, we use the Slack data to monitor both the activity of users (the number of messages sent in different channels) and the relevance of channels (the frequency of activity within the channel). This lets us understand which projects the users are engaged in the most and how to allocate Railswarians efficiently. 

Use case #1 – Allocation Monitor

In this example, we can see that a certain Railswarian’s activity is focused mostly on the Coupler.io project. But they also dedicate 35% of their messages to the Slack channel related to RW Branding.  

The idea of such monitoring is not to be Big Brother 🙂 This analysis allows us to understand the project preferences of each Railswarian. For example, a user may not be a team member of the X project. However, they dedicate a significant share of messages (help, consultancy, etc.) in the channels associated with the X project. So, we can consider them for allocation to this project in the future. That’s why it is important to analyze data.

Use case #2 – Resource Remapper

What else you can do with the Slack data is to identify the idle channels. You can have hundreds of channels in your company, but are you sure that all of them are active? So, we imported data from Slack to do a deep clean. Here is how our spreadsheet looks:

Truth be told, in this particular case, we did not rely solely on Coupler.io. We have a large number of Slack channels, and we’d have to set up a separate Slack to Google Sheets integration for each channel…so, it will be around 500 separate importers! 😲

But if you need to analyze up to 20 channels, it’s quite doable with Coupler.io.

Bonus stage: How to post messages to Slack from Google Sheets

In the examples above, we used the GET HTTP Method to retrieve different data from Slack. But you can also use the POST method to automate Slack messages of the Google Sheets data set without coding! Basically, you’ll need to complete three high-level steps:

  • Get a Bot User OAuth Access Token 
  • Create a message to post
  • Set up the Google Sheets to Slack integration based on JSON Client

How to get a Bot User OAuth Access Token 

Note: If you don’t know how to create a Slack app and get the required credentials, please check out the section “How to get an OAuth Access token in Slack“.


Go to the Add features and functionality section of your Slack app and click on Permissions:

Select one of the following permissions:

  • chat:write – to send messages to channels that the user or the Slack app is a member of (available for User and Bot Token Scopes)
  • chat:write.public – to send messages to channels the Slack app is not a member of (available for Bot Token Scopes only)

What’s the difference between Bot and User Token Scopes?

If you add chat:write or chat:write.public within Bot Token Scopes,  the messages will be posted on the behalf of your Slack app. If you add chat:write within User Token Scopes, the messages will be posted on the user’s behalf. Here is what the difference looks like:

We’ve chosen chat:write.public (chat:write will also be added by default) so we can send messages to any public channels within the workspace. After that, scroll up and click on Install App to Workspace:

You can add or remove Scopes at any time later.

Now you have a Bot User OAuth Access Token to connect Google Sheets to Slack.

We’ll use it later. While we’re still on the Slack side, let’s tailor the message we’re going to post.

Create a message to post in Slack

Open the Block Kit Builder to tailor your message. You can use a ready-made template or build the message from scratch. You can change the text of your message blocks in the right part of the Block Kit Builder (Payload).

The payload is in JSON format, while the message should be sent to Slack in YAML format. Use json2yaml to convert JSON to YAML. Once the message is ready, click on Copy Payload. Paste the payload in the JSON section and copy the YAML result.

Now let’s switch to the spreadsheet. 

Set up the Google Sheets to Slack integration

You can either use the Coupler.io web version to configure the JSON Client importer or install the add-on to do this from your spreadsheet as we described in the “Connect Slack and Google Sheets” section. Here are the Source parameters you need to apply:

  • JSON URL – Insert the following JSON URL:
https://slack.com/api/chat.postMessage

Click on Show Advanced 

  • HTTP Method – Select POST to post data to Slack.
  • HTTP headers – Apply the following HTTP headers to make authenticated requests to Slack:
Authorization: Bearer {your-bot-oauth-access-token}
Content-Type: application/json

{your-bot-oauth-access-token} – replace with the Bot OAuth Access Token, which you obtained after installing the Slack app to the workspace. 

  • Request body – Specify the channel ID to post the message to and insert the payload of your message in YAML format. Here is how it may look:
channel: "{channel-ID}"
blocks:
- type: section
  text:
    type: mrkdwn
    text: "*Coupler.io blog posts you should read*"
- type: divider
- type: section
  text:
    type: mrkdwn
    text: |-
      :book: *How to Import JSON to Google Sheets Without Coding*
      https://blog.coupler.io/how-to-import-json-to-google-sheets-without-coding/
- type: section
  text:
    type: mrkdwn
    text: |-
      :book: *How to Export WordPress Data to Spreadsheets via WP REST API Without Coding*
      https://blog.coupler.io/wordpress-to-google-sheets/
- type: section
  text:
    type: mrkdwn
    text: |-
      :book: *How to Integrate Slack and Google Sheets Without Apps Script*
      https://blog.coupler.io/slack-google-sheets/
- type: divider

Note: If required, check out “Where to get {channel-ID}

  • Path – Type in message in the Path field.

Let’s sum up the Source parameters:

JSON URL.../chat.postMessage
HTTP MethodPOST
HTTP headersAuthorization: Bearer {your-bot-oauth-access-token}
Content-Type: application/json
Request bodychannel: "{channel-ID}"
{your-message-payload-in-yaml}
Pathmessage

Set up the Destination and Schedule (Settings in the add-on version) and click Save (only  saves the integration) or Save & Run (saves the integration and runs it).

Use case: Post rows daily to a Slack channel from Google SheetsUse case: Post a daily reminder to a Slack channel

Let’s take a look at how the Google Sheets – Slack integration works. We need to send a message to a Slack channel on the app’s behalf automatically every day. The message will remind about the Coupler.io blog posts recommended for reading. We used the Block Kit Builder to tailor it:

The Slack channel where the message will be posted to is called #data-import. We copied its link

https://testforcouplerio.slack.com/archives/C0172TL8WDC

to grab the channel ID – C0172TL8WDC. Now we can amend our integration with these parameters and run the importer:

The message will be posted every subsequent day at 00:01am (+/- 30 minutes) according to the spreadsheet time zone.

Can I use Coupler.io to post updatable data from Google Sheets to Slack?

Coupler.io does not currently provide such functionality. However, if Slack integration is in demand, our team will consider delivering a dedicated Slack importer. Tell us if you want to have this, or if you need to connect another data source to Google Sheets, by filling out this form. Good luck with your data!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free