google

Posting Slack message on Google Docs spreadsheet cell update

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?
}

Google Fonts – nasty bold

Say you are using Google Web fonts for some CSS. Well, if you don’t include the correct bits you may encounter this problem.

Say, you use the Ubuntu font, for instance, and you have this in your <head>  block:

<link href="http://fonts.googleapis.com/css?family=Ubuntu" rel="stylesheet" type="text/css" />

Then, you use this font everywhere, and this includes a <th> section. You may find that the font displays like this:

Odd bold variant on Google Webfonts

Why has this happened? Well, its because you’ve not included the bold variant of the font in the header link.

For this particular font, it should be:

<link href="http://fonts.googleapis.com/css?family=Ubuntu:400,700" rel="stylesheet" type="text/css" />

700 is the bold variant. Once you’ve changed this, you should find that the bold is rendered correctly:

Fixed version of Google Webfont bold

Errors trying to access Google IMAP through PHP

imap_open() [function.imap-open]: Couldn't open stream {imap.gmail.com:993/imap/ssl}INBOX

This error /can/ be missleading. I thought something was up with the stream (guessing there was a misconfiguration with PHP, IMAP and openSSL) – but it turned out I had typed the wrong password in the initial request.

Just incase anyone else is struggling here, just check that first.

Then, ensure you have installed the openSSL and the php5-imap extension correctly.