Spreadsheet To Slack

I wanted to help my operation team, by automating their tasks. So, I asked them if there are some repetitive tasks, that we can automate. They came up with a bunch of ideas and one of them was,  Every time they update the spreadsheet(they have a whole todo thing on a spreadsheet), they would send a slack message to the channel/person, that they have made an update on the spreadsheet. So, if there was a way that the message would automatically be sent to slack, as soon as the update is made ???

Then, I did some “research”, and found that there are many tools like zapier, automate.io etc for integrating spreadsheets with slack and with other apps also. But after I used them, I found that they were not that effective for what I wanted. They would just send a message like “A row has been updated on a spreadsheet”, which was not that helpful. So I found this on StackOverflow ;)

All I needed to do was, set up a trigger on google app script and then send a request to slack using incoming webhooks. That’s what I did, and SPOILER ALERT, it worked. So, the main question is how did it work ??? I will tell you in the next part of this post. Stay tuned…. Just kidding. Ok, let’s go to the code part.

So, the spreadsheet looks like this,

If somebody updates some row by adding some task description or extending the deadline etc, I want the message to be like this,

or if the task is completed is true,

Now, let’s go to the script, click on Tools and then script editor,

//Gives you the url of the current page
function getSheetUrl() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var activeSheet = ss.getActiveSheet()
  var url = ''
  url += SS.getUrl()
  url += '#gid='
  url += ss.getSheetId()
  return url
}
function spreadSheetToSlack() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet()
  var currentSheet = sheet.getName()
  var currentSheetUrl = getSheetUrl()
  var values = sheet.getDataRange().getValues()
  for (var i = 0; i < values.length; i++) {
    if (values[i][7] === 'submit') {
      var task = values[i][0]
      var taskDescription = values[i][1]
      var responsible = values[i][2]
      var deadline = values[i][3]
      var completed = values[i][4]
      var comments = values[i][5]
      sendToSlack(
        task,
        taskDescription,
        responsible,
        deadline,
        currentSheet,
        completed,
        comments,
        currentSheetUrl
      )
    }
  }
}

getSheetUrl function with just return the URL of the spreadsheet, which we are going to use later.

spreaSheetToSlack function will get all the values from each cell of the row that we have updated we will pass those values to the sendToSlack function.

sendToSlack function will look like this,

// function to send message to Slack
function sendToSlack(
  task,
  taskDescription,
  responsible,
  deadline,
  currentSheet,
  completed,
  comments,
  currentSheetUrl
) {
  // custom slack webhook
  // change the XXXXX's to your own slack webhook. Get it from:
  // https://my.slack.com/services/new/incoming-webhook/
  var url = 'https://hooks.slack.com/services/xxxxx/xxxxx/xxxxxxx'
  if (completed === 'Yes') {
    var payload = {
      channel: '#general',
      username: 'from spreadsteet',
      icon_emoji: ':rocket:',
      text:
        ' `' +
        task +
        '` in `' +
        currentSheet +
        '`,is now complete. Please Take A look.\n ' +
        currentSheetUrl +
        '.'
    }
  } else {
    var payload = {
      channel: '#general',
      username: 'from spreadsteet',
      icon_emoji: ':rocket:',
      text:
        'There has been update on `' +
        task +
        '` in `' +
        currentSheet +
        '`, which `' +
        responsible +
        '` is responsible for and deadline is `' +
        deadline +
        '`. The task descriptions are \n' +
        taskDescription +
        '. \n Please Take A look.\n ' +
        currentSheetUrl +
        '.'
    }
  }
  var options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload)
  }
  return UrlFetchApp.fetch(url, options)
}

Here, we get the webhook URL from slack, you can get yours from this link. There you just put the channel where you want your message to be posted and then click on Add incoming webhook integration.. After that, you get your URL like this

https://hooks.slack.com/services/xxxxx/xxxxx/xxxxxxx

just replace xxxxxx with your URL. The payload is pretty simple, as you can guess from the keys of the object. Then it’s just a post request.

Now, go to your spreadsheet, update your row, write submit on column 8 of the same row, then click on the submit button. The submit button is assigned with the spreadSheetToSlack function.

What is the point of writing submit and clicking the button ?????

There is an inbuilt trigger called onEdit, which runs automatically as soon as you edit a cell. The problem with onEdit is that, as soon as you edit a cell and you go to next cell to edit, the onEdit function is already triggered and the slack message is already sent. If you update 5 cells in the same row you will send 5 slack messages.

So, to make it more efficient, we added our own trigger, by writing submit at the end of the row after editing or updating everything. It’s just to know which row was edited so we can get the values from that row.

You can do this by creating a rectangle and placing a text with submit. Then, select the rectangle and you will see 3 dots, click them and you will see Assign Script text, click there and paste the spreadSheetToSlack there, without parentheses.

Then, you will receive a slack message like above.