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? }
I tried sending a message to slack but came to know that it will not work.i made a function for slack and called it in onEdit function. Please help me as I m newbie
Perhaps show your code?