Skip to content

How to Fetch Data from a Google Sheet and Post it to a Slack Channel Using a Custom Bot

Master the Art of Building a Slack Bot with Google Apps Script

Get ready to create a synergy between Google Apps Script and Slack! In this tutorial, you will learn how to fetch data from a Google Sheet and automatically post it to a designated Slack channel using a custom bot. Whether it’s for daily updates, regular reporting or sharing fun facts, this bot is here to help you automate the process. Let’s get started!

Setting Up Your Workspace

Before we get into the scripting, make sure you have:

  • A Google account (for Google Sheets and Google Apps Script)
  • A Slack workspace where you have permissions to install apps

You should also be familiar with basic JavaScript, as Google Apps Script is based on it.

Step 1: Design Your Data Sheet

The first step is to create a Google Sheet and populate it with the data you want to share with your Slack channel.

1. Open Google Sheets.
2. Create a new blank sheet.
3. Input your data. For this tutorial, we'll assume you have headers in the first row, with data in subsequent rows.

Step 2: Setting Up Your Bot on Slack

Next, we’ll create a new bot user on Slack and get the API token, which allows our script to interact with Slack.

1. Visit the [Slack API website](https://api.slack.com/apps).
2. Click on 'Create New App' and then select 'From scratch'.
3. Name your app and select the Slack workspace you want to tie it to.
4. After creating the app, navigate to 'Bot Users' on the sidebar and click 'Add a Bot User'.
5. Keep note of the 'Bot User OAuth Access Token' - we'll need this later.

Remember to install your bot in your workspace!

Step 3: Create the Google Apps Script

Now, we will create the Google Apps Script that fetches data from the sheet and posts to Slack.

1. Go to your Google Sheet.
2. Click on 'Extensions' > 'Apps Script'.
3. This will open the Google Apps Script Editor.

In this script editor, paste the following script:

function postToSlack() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();

  var botToken = 'your-slack-bot-token-here';

  var channel = 'your-slack-channel-name-here';

  for (var i = 1; i < data.length; i++) {
    var message = data[i].join(', ');
    var url = 'https://slack.com/api/chat.postMessage';
    var options = {
      method: 'post',
      headers: {
        'Authorization': 'Bearer ' + botToken,
        'Content-type': 'application/json'
      },
      payload: JSON.stringify({
        'channel': channel,
        'text': message
      })
    };

    UrlFetchApp.fetch(url, options);
  }
}

Remember to replace 'your-slack-bot-token-here' with your actual bot token and 'your-slack-channel-name-here' with your designated Slack channel.

This script gets the data from your Google Sheet and sends each row as a separate message to your designated Slack channel.

Step 4: Run and Test the Bot

Now we can run the script and test our bot.

1. Save the script with a name, such as 'SlackBot'.
2. To run the script, select 'postToSlack' in the function dropdown and click the play button.

If the script is successfully executed, your bot should post the data from your Google Sheet into your Slack channel. Remember to grant the necessary permissions when prompted.

Congratulations! You’ve just created a custom Slack bot that fetches data from a Google Sheet and posts it to a designated Slack channel. Remember, this is just a simple example. With some tweaking, you can customize your bot to do much more! Enjoy exploring the endless possibilities.