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 = "<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?

Annoying IE Compatability Mode

I recently had to create a site for an education facility, and they turn on Compatibility mode for all their networked PCs, for legacy support.

This was messing around with some of my JS/CSS functionality, so I needed a work around.

3 options really:

  1. Get the IT manager to remove the global compatibility layer and only apply to the legacy apps. Not going to happen!
  2. Rewrite the code to use IE friendly techniques. Possible but a ball ache.
  3. Force the browser to not use compatibility mode. Sounds best, right?

So, its really very simple, in the head section of the page:

<meta http-equiv="X-UA-Compatible" content="IE=edge" />

That worked an absolute treat.

preventDefault not firing on onkeyup event

Simple one.

$(document).on('#element', 'keyup', function(event) {
    if (event.keyCode == 13) {
        //do some other stuff

This doesn’t actually work as the Key Up event is too late down the event queue for preventDefault() to have any effect. Change your code to utilise the keydown or keypress event instead and you should be fine.

Ajax Loading GIF

So you want a little funky AJAX loader?

Add this to your JS main core file…:

$(document).ready(function() {
    //initiate an ajax start message
    $(document).ajaxStart(function() {
       //show the message
    //when ajax is completed
    $(document).ajaxStop(function() {
       //hide the message

And create a nice little div somewhere:

<div id="ajax_loading_indicator">
    <img src="/public/images/ajax_loading.gif" alt="Loading" />

I use the following CSS to style mine, so its unobtrusive but visible:

#ajax_loading_indicator {
    position: fixed;
    right: 2px;
    top: 2px;
    background-color: #efec9f;
    border: 1pt orange solid;
    padding: 5px 30px 5px 30px;
    display: none;

TIP: If you can’t be bothered to find an AJAX loading GIF, then there is a great little website that will create you one for free. Its,


You do need to have jQuery (version 1.0+) for this, in case you didn’t work that out…



Replace Microsoft chars in JavaScript

Okay, so have you ever had a user call you up and say “the website is putting garbage into my notes” – and it turns out they are copying and pasting paragraphs from a Word document into the textarea on your site. Well, Microsoft Office apps use a special character set (Windows 1252) so they can have nice looking quotes, and longer dashes. Which all look very nice in the Word document, but when they are pasted into a textarea in your site, they are converted to weird characters.

There is a simple function you can use to replace them:

 * Replace Word characters with Ascii equivalent
function replaceWordChars (text) {
    var s = text;
    // smart single quotes and apostrophe
    s = s.replace(/[\u2018|\u2019|\u201A]/g, "\'");
    // smart double quotes
    s = s.replace(/[\u201C|\u201D|\u201E]/g, "\"");
    // ellipsis
    s = s.replace(/\u2026/g, "...");
    // dashes
    s = s.replace(/[\u2013|\u2014]/g, "-");
    // circumflex
    s = s.replace(/\u02C6/g, "^");
    // open angle bracket
    s = s.replace(/\u2039/g, "");
    // spaces
    s = s.replace(/[\u02DC|\u00A0]/g, " ");

    return s;

Stick the above on an onblur event or something, and the problem has gone away. Until the next version of Office anyway…

Thanks to for this lovely code.

Preventing pesky consoles. (forgetful developers)

I always end up leaving debug code in my pages for one reason or another. One of which is console.log() for the firebug extension. Now, if you leave this and a user opens your page on IE or something like that, they will get an error.

Put the following code into a JS file, like your base file. And it will check for the existence of console and avoid the problem.

if (!window.console) {
    window.console = {};
    window.console.log = function() {};

JavaScript – Use a variable for a function call

Sometimes you want to pass a function around from script to script as a string containing the function name (well maybe not, but I have some legacy code that does require this)

So if you have a function called doSomething() and you want to call it from another page, but you’ve passed it like so:

<script type="text/javascript">
function startSequence()
    var nextFunction = 'doSomething';
    return nextFunction;

So what I’m saying is the function will be placed into the Window object, so you can call it :


And it obviously extends to the window.opener object.

I’m sure there are better ways, but this is handy to know I think.

Javascript Hoisting

When using JavaScript, you declare a variable using the var keyword.

var myvariable = "a value";

These variables are globally accessible unless they are defined within a function. Javascript performs an operation known as “hoisting” when it compiles which draws all the declarations to the top of the page.

Here is a piece of code:

var test = "my test";

When run, the above will display “my test” in an alert, as you would expect.

Consider this:

var test = "my test";

function testMe() {
    var test = "another test";


The above will display “my test” and then an “undefined” error and then “another test” in an alert. This is because we have declared a function variable within the function testMe function and this overrides the global state.

If we refactor the code as so:

var test = "my test";

function testMe() {
    test = "another test";


Because we have not used the var keyword, the variable exists throughout, and the alerts you get will be “my test” and then “my test” and finally, “another test”

Its very important to remember this, as when you have some large functions, you could be getting obscure results you could be looking for a long time to find the issue.




Viewing events attached to elements via JavaScript

If like myself, you often attach functions to elements using jQuery, for example:

$('.btnName').live('click', function() {
    //do something

There is a nice little browser extension that will show all the elements on the page with a little bubble outlining the attached events/functions.

It is written by Allan Jardine, and you can get it from



JQuery – bind event to non existant element

We all have to bind events to elements:

    do some stuff

But, what if that element doesn’t exist yet? Well, you can use the “live” handler:

$('#link').live('click', function(){
    do some stuff

This will carry the event handler on and will match any future element that matches the selector.

More information can be found here: