Back to Blog

How to Integrate Slack and 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. At some point, we decided to analyze the user activity of each channel. This would let us determine the viability of specific channels (activities) and get rid of the idle ones. For this, we needed to export the following raw data from Slack:

  • List of all public channels
  • History of all messages in the public channels

Data export from Slack can be done manually in JSON format, but we wanted to export data on a schedule, as well as import data into Google Sheets. So, we chose Coupler.io to do the job. Read on to explore the way to automate data exporting from Slack to Google Sheets without writing a single line of code!

How to set up Slack Google Sheets Integration for Data Exports

To connect Slack and Google Sheets, you’ll need to do the following:

  • Create a Slack app and install it to your workspace
  • Install Coupler.io and configure the JSON Client importer

Slack side 

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. Learn more about Slack apps. 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.

Check out the full list of Scopes and permissions

Our task is to retrieve information about channels and, likely, users as well. So, we chose the following User Token Scopes:

ScopeDescription
channels:readView basic information about public channels in the workspace
users:readView people in the workspace

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.

Google Sheets side

Install Coupler.io

Coupler.io is a Google Sheets add-on to import raw data from third-party apps, such as Jira, CSV, BigQuery, and other data sources

You can find Coupler.io on the Google Workspace Marketplace or use this direct link.

Set up a JSON Client importer

Coupler.io connects to each data source via their respective importers. Slack is not supported yet, so we’ll use a workaround – the JSON Client importer. It is a multi-purpose connector, which you can use to connect to a data source via API.

If you want to have a dedicated Slack importer, or you need to connect another data source to Google Sheets, tell us about this by filling out this form.

Open Coupler.io in the Add-ons tab of a spreadsheet, then click on the +Add Importer button and choose JSON Client.

Fill out the following fields to configure the Slack Google Sheets integration: 

  1. Title – Add the name of your data source 
  2. 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. Here are the methods we need for our task:

MethodDescription
conversations.listLists all channels in a Slack team.
conversations.historyRetrieves a history of messages and events of a conversation (public or private channels, direct messages, etc.).
users.listLists all users in a Slack team.

The full list of Slack API methods

Click on Show Advanced 

3. HTTP Method – Select GET to pull data from Slack.

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

5. Sheet Name – Type in the name of your sheet.

Automatic data refresh

If required, toggle this function on and pick the frequency for the automatic data export from Slack.

For information about other fields and Settings, refer to the Coupler.io knowledge base.

Click Add Importer to save the integration. Then run the importer to export data from Slack to Google Sheets.

Set up separate Slack Google Sheets integrations for different pieces of data you want to import into spreadsheets.

Configurations to retrieve data from Slack

Let’s take a look at the configurations we set up for our use case:

Retrieve data about all public channels in the Slack workspace

JSON URLhttps://slack.com/api/conversations.list
HTTP MethodGET
HTTP headersAuthorization: Bearer xoxb-1*********2N
Content-Type: application/json

Retrieve data about all users in the Slack workspace

JSON URLhttps://slack.com/api/users.list
HTTP MethodGET
HTTP headersAuthorization: Bearer xoxb-1**********2N
Content-Type: application/json

Retrieve history data about a specific Slack channel

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:

JSON URLhttps://slack.com/api/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

Missing_Scope

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:

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:

We imported the messages history of Slack channels and analyzed them. The most intricate part here is that you need to set up a separate Slack to Google Sheets integration for each channel. 😲 

Can I use Coupler.io to only pull data from Slack to 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 send requests to the Slack Conversations API. So, in the next blog post, we’ll explain how you can automate Slack messages of the Google Sheets data set without coding! That’s going to be insightful. See you!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free