Automating internal company processes with Google Apps Script and Slack WebHooks

Introduction

At Solidstudio, we use a tool setup which is quite popular among IT companies. These two main components play a crucial role in almost every process we run: G Suite and Slack.

We are fans of the LEAN approach, and that’s why we decided not to invest our time and money in tools that we might use in the future, when our company becomes 10 times bigger.

Slack and G Suite are good enough for now. Still, what we missed few times was some automation. There exist Sack applications that provide some level of integration between the tool and G Suite, but they usually have limited capabilities.

Let me show you a use case we decided to try out and how we solved the challenge.

Posting a date from Google Spreadsheets

We use Google Spreadsheets for a broad range of tasks at our company. Financial data, reports, different types of KPIs - we track them all in Spreadsheets. Some of the KPIs need to be collected manually. And since they’re stored in a spreadsheet (one of the many that are also important), there is a risk that some of us forget to check it every day.

So we have two problems to solve here:

• Pinging people to remind them about updating KPIs,
• Sharing the current KPI values with all the interested parties.

Google Apps Script to the rescue

To solve these problems, I decided to connect two features/services:

• Slack Incoming Webhooks
• Google Apps Script

Slack has an Incoming Webhooks mechanism. After a few clicks, we can receive a URL that may be used to send messages to specific Slack channels. It looks like this:

https://hooks.slack.com/services/TOKEN_HERE

It’s a POST method accepting json body with the message content:

{
    "text": message
}

What about the logic responsible for generating the message? It took me just a little time to research this and I found Google Scripts service. Sounds good since the data and processing would be delivered by the same platform. And I wasn't disappointed.

How do I get a spreadsheet? By using this one-liner:

SpreadsheetApp.openById(1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms);

The long id is the string we can take from the browser when we have the spreadsheet open:

Here’s a full example which is able to post reminders if needed, as well as the current KPI value:

function postCurrentKPIValue() {
    var kpiValue = getSheetCellValue('kpi', 'H1');
    postMessageToSlack('KPI value: ' + kpiValue + '.');
}

function remindAboutUpdatingKpiValue() {
    var isKpiValueUpdated = getSheetCellValue('kpi', 'H2') === '1';
    if(isKpiValueUpdated) {
        return;
    }
    postMessageToSlack('KPI state has not been updated.');
}

function postMessageToSlack(message) {
    var payload = {'text': message};
    var params = {
        'method' : 'post',
        'contentType': 'application/json',
        'payload' : JSON.stringify(payload)
    };
    UrlFetchApp.fetch('https://hooks.slack.com/services/token_here', params);
}

function getSheetCellValue(sheet, cell) {
    var spreadsheet = SpreadsheetApp.openById(spreadsheet_id);
    var sheet = spreadsheet.getSheetByName(sheet);
    return sheet.getRange(cell).getDisplayValue();
}

Google Scripts provides a way to set up triggers for such functions, and that’s exactly what we were looking for. We have been using this tool since a few weeks and it has worked for us pretty well.

What’s next?

Having such a broad potential for implementation, the Slack notification mechanism integrated with G Suite services is very handy tool. We’re going to use it for other cases like employee reports validation, reminding about invoices that haven’t been moved from the inbox directory in Google Drive, and many, many more use cases.