Back to Blog

How to Schedule Messages to Slack from Google Sheets Without Coding

Previously, we explained how you can set up Slack to Google Sheets integration. This allows you to automate export of different data (channels, users, messages, etc.) from Slack to a spreadsheet. Today, we’ll tweak this integration, to post messages to Slack on a schedule.

Set up Google Sheets Slack Integration for Posting Scheduled Messages

Here is the workflow to connect Slack to your spreadsheet:

  • Configure the Slack side
    • Create a Slack app and install it to your workspace
    • Add scopes required to post messages
    • Create a message to post
  • Configure the Google Sheets side
    • Install Coupler.io 
    • Set up the JSON Client importer

Slack side 

Create a Slack app

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

Name your app and select a Development Slack Workspace where the app will be installed. Click Create App:

Configure Permissions

Expand the Add features and functionality section 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)

Check out the full list of Scopes and permissions

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. 

Google Sheets side

Install Coupler.io

Coupler.io is a Google Sheets add-on to import raw data from third-party apps, such as Airtable, Xero, Jira, and other data sources. You can find Coupler.io on the G Suite Marketplace or use this direct link.

Set up a JSON Client importer

Coupler.io does not provide a ready-to-use integration for Slack yet, but you can set it up using the JSON Client importer. It is a multi-purpose connector, which you can use to connect to a data source via API. 
In a spreadsheet, select the Add-ons tab and open Coupler.io, then click on the +Add Importer button and choose JSON Client.

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

Step 1: Title 

Add the name of your data source.

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

  chat.postMessage – an API method to send messages to Slack channels. Discover the full list of Slack API methods.

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*
      
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*
How to Export WordPress Stats to Google Sheets Without Coding
- type: section text: type: mrkdwn text: |- :book: *How to Integrate Slack and Google Sheets Without Apps Script*
How to Integrate Slack and Google Sheets Without Apps Script
- type: divider

Where to get {channel-ID} ?

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

Option #2: Copy the link of your Slack channel. It looks as follows: https://testforcouplerio.slack.com/archives/C017FR4A1NG
The last value in the link is the channel’s ID: C017FR4A1NG

  • Path – Type in message in the Path field.
Step 3: Destination
  • Sheet Name – Enter the name of your sheet.
Step 4: Settings
  • Automatic data refresh

If required, toggle on this function and select the frequency of the automatic data refresh.

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

Let’s sum up the parameters:

TitleAny name you wish
Source:
JSON URLhttps://slack.com/api/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
Destination:
Sheet NameAny name you wish
Settings:
Automatic data refreshChoose the frequency of automatic data refresh

Click Save (only  saves the integration) or Save&Run (saves the integration and runs it). If you clicked Save, you’ll need to run the importer manually.

Use 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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Access your data
in a simple format for free!

Start Free