A lot of my reporting is repetitive mindless work. The fun part of data for me is the analysis but if I spend time updating my sheets and sending reports I get lost in the tedium of sending reports and don’t get time to read my own reports and figure out the relevant analysis.
One of the first things I like doing when I start a new task is to see how much of my workload can be automated and updated. For that (since I love Google Sheets) I use Google App Script. If you don’t know or understand Google App Scripts, I suggest you read up on it.
Basically Google App Script ” is a rapid application development platform that makes it fast and easy to create business applications that integrate with G Suite.” GAS uses JavaScript so if you are good with JavaScript its a piece of cake.
Now step by step how to create a slack bot from Google Sheets:
Step One: Create a Slack App:-
Add alt text
Go to https://api.slack.com/apps, select Create a new App.
Select From Scratch because you are a genius/ a very modest person. Write down App name (Remember that’s what will show as sender once the bot is setup so make sure it’s not something weird) and select your Workspace.
Now go in and from the left hand menu select “Incoming Webhook” and activate incoming webhooks. Add new webhook to workspace will be right at the bottom. Now you can select any channel to post to. I would recommend testing out the formatting on a private channel first so your first bot message to the world is perfect.
Step Two: Updating the App Script
I am just going to paste my entire script and then give you guys a run down.
function buildReport() //first function
const ss = SpreadsheetApp.getActive(); //Get the active Spreadsheet that you are working on
let data = ss.getSheetByName('Dashboard').getRange("G2:J15").getValues();//Selects the data you want to send on Slack
let payload = buildAlert(data); //gathers the data
sendAlert(payload);
}
function buildAlert(data) { //Second Function to build the data
let campaigns = data.slice(1).map(function(row) {
return "TotalBudget " + row[0] + " Spent " + row[1] + " Conversions " ;
}).join("\n");
let payload = {
//start block
"blocks": [
{
"type": "section",
"text": {
"type": "mrkdwn",
"text": ":warning: *Campaigns For Today* :warning:"
}
},
{
"type": "divider"
},
{
"type": "section",
"text": {
"type": "mrkdwn",
"text": campaigns
}
}
] //end block
};
return payload;
}
function sendAlert(payload) {
const webhook = "https://hooks.slack.com/services/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";
var options = {
"method": "post",
"contentType": "application/json",
"muteHttpExceptions": true,
"payload": JSON.stringify(payload)
};
try {
UrlFetchApp.fetch(webhook, options);
} catch(e) {
Logger.log(e);
}
}{
function buildReport()
const ss = SpreadsheetApp.getActive();
let data = ss.getSheetByName('Dashboard').getRange("G2:J15").getValues();
let payload = buildAlert(data);
sendAlert(payload);
}
To build the payload you should use https://app.slack.com/block-kit-builder. I just added a heading and kept my text simple but you can make it fancier too if you want.
To add scripts go to Tools on your Google Sheets and click Script Editor. Remove the preadded code and copy paste above code, tweak as necessary.
If you want to automate the sending add triggers. Triggers can be spreadsheet based, time based or calendar based. I used time based because I want to send this automatically everyday.