Chris Tate-Davies

We’ve been using a bit of a google doc recently for logging stuff, and sometimes cells get missed. I wanted to just test out a bit of google doc and slack integration so wrote this short script to send a slack message to a certain channel when a user updates a particular cell.

The column in the spreadsheet is 14 FYI, which is why I check for that column and return if not.

Because this script does other things, like a HTTP request, it cannot be assigned to the default onEdit() functions, you need to create it as a custom function and assign it in the Resources menu. Resources->Current Project Triggers and create a new one for onEdit. Just point it at your function and you’re done.

Excuse my unprofessionalism regarding HTTP response codes, but this was a simple test to see how easy it was. 1 hour. Done.

/**
 * @author Chris Tate-Davies
 * @revision 0.0.1
 *
 * 10th May 2016
 * Purpose - send a slack payload to bot-database informing users of database update requirements
**/
function ceta_db_column_edit(event){

 //get this spread sheet
 var ceta_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

 //get the sheets and range from the spreadsheet
 var ceta_sheet = event.source.getActiveSheet();
 var ceta_range = event.source.getActiveRange();

 //get the cell thingy
 var active_cell = ceta_sheet.getActiveCell();
 var active_row = active_cell.getRow();
 var active_column = active_cell.getColumn();
 
 //If header row then exit
 if (active_row < 2) return;
 
 //if not the db column get out
 if (active_column != 14) return;

 //get the revision
 var revision_range = ceta_sheet.getRange(active_row, 2);
 var revision_content = revision_range.getValue();

 //get the changes in the cell 
 var db_changes_range = ceta_sheet.getRange(active_row, 14);
 var db_changes_content = db_changes_range.getValue();
 
 //if its nothing then lets not bother (they're probably deleting stuff)
 if (db_changes_content == "") return;
 
 //the url to post to
 var slack_url = "https://hooks.slack.com/services/<ENTER YOUR TOKENS HERE>";

 //get the logged in user (we can only get email I thinks)
 var current_user = Session.getActiveUser().getEmail();
 
 //if its blank (why?)
 if (current_user == "") {
 
 //at least put something in
 current_user = "An unknown";
 }
 
 //generate the payload text object
 var payload = { "text" : current_user + " has just entered text into the db field for revision " + revision_content + " - Content is: ```" + db_changes_content + "```" };

 //the URL payload
 var options = {
     "method" : "post",
     "contentType" : "application/json",
     "payload" : JSON.stringify(payload),
     "muteHttpExceptions" : true
 };

 //send that bugger
 var response = UrlFetchApp.fetch(slack_url, options);

 //we could check for response, but who cares?
}