Post Data from Google Sheets to Slack Automatically (Build it Yourself in Five Minutes)

You will learn how to post data from Google Sheets to Slack by writing a custom function in Google Apps Script.

If you’re in love with Google Sheets (love-hate relationship counts too), you probably set up some automation or use it to track crucial data of whatever business you are in. You might be tracking your costs, revenue, new signups or use Google Sheets to organise your employees’ annual leave.

How about getting some of your data posted from Google Sheets to Slack every day or week? It is probably easier than you think.

Let’s suppose you want to get a daily summary of who is away into Slack every morning. You might be tracking your team’s leave in a similar way to this sample sheet:

Today is the 8th: Jessie is off sick, Gary and John are on annual leave and Tom is travelling.

Before digging into how we get this posted to Slack, I always like to create a new sheet to look up the data I want to get to Slack. This makes it easier to set up the connection to Slack later and debug any errors. For the above example, I created a new sheet called Slack:

=HLOOKUP($B$1, Planner!$B$1:$AF$8, ROW(A2))

You can see I am horizontally looking up the current date (cell B1 contains =TODAY()) in the Planner sheet ($B$1:$AF$8) and returning the index value of my current row since the row number is the same across both of my sheets.

To define what data I want to later access in Google Apps Script, I also created a Named Range called ‘slackData’. You will understand the why shortly. To create your range select the data you will be accessing in your script, select ‘Data’ — ‘Named ranges’ and choose your range name.

Now let’s get to the fun part! Getting this nicely formatted into Slack using Google Apps Script.

First of all, let’s open Google Apps Script and create a brand new project which takes you directly to the Code Editor. Let’s define some constants at the top before we start writing our functions:

const slackWebhookUrl = 'https://hooks.slack.com/services/...'
const sheetId = '1jZpbuftMHJLKugYQEcEajGiqkjnjgxBcp1bvtryCNJw'
const sheetRange = 'slackData'

You can get your Slack webhook URL by selecting an existing app or creating a new one from your Slack Admin and going into ‘Incoming webhooks’. Make sure they’re activated and click on ‘Add New Webhook to Workspace’. Then select the channel where you want your data posted and copy the webhook URL.

Your Google Sheet ID is included in the URL of your sheet as you can see below and sheetRange is what you called your Named Range.

Second of all, let’s write our function that will nicely format the Slack message (if you want fancier formatting then Slack’s Block Kit Builder is your place) and send it to Slack. Copy the below code (just the function if you have already defined your constants) into your Apps Script code editor.

Notice on line 8 I have added a condition to only run the code when it’s not a weekend. This is because we are only able to set the code to run automatically every day without specifying the exact days of the week — so this is just a hacky way to get around it. Customize to your needs.

The code is easy to understand but in case you’re wondering: it gets data from the sheet into an array called ‘data’, then it pushes names of every person with the relevant type of leave to the relevant array and finally it builds the Slack blocks before sending them to our webhook.

Save the code and click Run to test it. The first time you run this you will be prompted to authorize access to Google Sheets (you may need to click the small ‘Advanced’ text on a security alert to proceed). The beautiful thing about using Apps Script for this purpose is that Google will handle the authorization and you don’t have to worry about generating and securely storing any keys.

In your Slack, you should now see something like this:

The last bit is to make sure this runs automatically every morning. Head into ‘Triggers’ in Google Apps Script (icon with the alarm clock in the left panel), click ‘Add trigger’ (bottom right) and edit the type of time based trigger and time of day:

Once you save this, your code will now run automatically at your set intervals 🚀

Follow me to read more about cool hacks related to Slack, Google Sheets or software in general! @adamcharvat