JavaScript

Posting Slack message on Google Docs spreadsheet cell update

admin Leave a comment

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

Annoying IE Compatability Mode

admin Leave a comment

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.

Disabling certain days in jquery UI datepicker

admin Leave a comment

I wrote a site for my local pub and it involved a restaurant booking section. The user could select a date and a time (plus name, phone number and number of people)

The kitchen was closed on Mondays, so I needed to stop any bookings for Mondays. I had two options I could see,

  1. Server side, using PHP to check the date and refuse the request
  2. Client side, using JavaScript to block the request

I decided client side was best for the end user, otherwise they have to go back and pick a new date, etc.

I was using jquery UI’s datepicker and its very simple to just block Mondays:

$('.datebox').datepicker({
    "dateFormat"    : "DD dd/MM/yy",
    "beforeShowDay" : function(date) {
        var day_of_week = date.getDay();
        return [(day_of_week != 1), ''];
        }
    });

The day_of_week numbers start at 0 for Sunday, to 6 for Saturday. SIMPLES

Convert Excel headers to numbers

admin Leave a comment

Quite often I have to convert datasets from one source to another target. Sometimes I can only export them as CSV to investigate the field order/column structure

Then, once I work out the columns I can import them. But, the headings are often not in the source, so I only have Excel/OpenOffice column headers which go from A to Z and then AA to AZ, and BA to BZ, etc etc

I needed a quick way to convert these to numbers, so I could state “the 141st column is /this/ field”

So here it is:

$(document).ready(function() {
    $("#testes").on("keyup", function(event) {
        var str = $(this).val();
        if (str.length == 1) {
            $("#results").val(get_string_value(str));
        } else {
            var fstr = str.slice(0,1);
            var estr = str.slice(1,2);
            $("#results").val(get_string_value(estr) + (get_string_value(fstr) * 26));
        }
    });
});

function get_string_value(str) {
    return str.charCodeAt() - 96;
}

Or if you like, a fiddle: http://jsfiddle.net/8eAV6/

Passing variable as key in jQuery ajax

admin Leave a comment

I wanted to pass a simple key/value string to an Ajax request, but you cannot simply use the variable as the key in the data:

var $fieldname  = 'company_name';
var $value      = 'Tate-Davies Inc.';
var $promise = $.ajax({
    'url'       : '/some/action',
    'dataType'  : 'json',
    'data'      : {
                  $fieldname : $value
                  }
    )};

The above just will not work, and the $_GET will contain:

'fieldname'    => 'Tate-Davies Inc.'

What I actually want is:

'company_name' => 'Tate-Davies Inc.'

So, in order to fix this:

var $data      = JSON.parse('{"' + $fieldname + '":"' + $value + '"}');

And pass that instead:

var $promise = $.ajax({
    'url'       : '/some/action',
    'dataType'  : 'json',
    'data'      : $data 
    )};

jQuery tablesorter custom date sort

admin 2 Comments

I needed a custom sort column for jquery tablesorter (http://tablesorter.com/docs/) for dates in the format of 12-Aug-2013 as the default tried to sort it as a string

Was quite complicated to work out, but think its done:

$.tablesorter.addParser({
    'id' : 'customDates',
    'is' : function(string) {
         return false;
         },
    'format' : function(string) {
        if (string == "") {
            return '';
        }
        var thedate = string.split('-');
        var monthint = {};
        monthint['Jan'] = "01";
        monthint['Feb'] = "02";
        monthint['Mar'] = "03"; 
        monthint['Apr'] = "04";
        monthint['May'] = "05";
        monthint['Jun'] = "06";
        monthint['Jul'] = "07";
        monthint['Aug'] = "08";
        monthint['Sep'] = "09";
        monthint['Oct'] = "10";
        monthint['Nov'] = "11";
        monthint['Dec'] = "12";
        var date_day = parseInt(String(thedate[0]));
        if (date_day.length == 1) {
            date_day = '0' + date_day;
        }
        var date_month = monthint[thedate[1]];
        var date_year = thedate[2];
        return date_year + date_month + date_day;
    },
    'type' : 'numeric'
    });

And then apply it to the table

$('table').tablesorter({
    'debug' : 'true', 
    'headers' : {
        0 : {
            'sorter' : 'customDates'
            } 
        } 
    });

wkhtmltopdf repeat on subsequent pages

admin 8 Comments

wkhtmltopdf has a bug as a result of a webkit bug. This means that the nice css styles for table printing don’t work in wkhtmltopdf.

Fortunately for us, wkhtmltopdf can handle JavaScript and that enables us to modify the DOM nicely prior to PDF creation.

Add the class ‘split_this_table’ to the table(s) you need to sort out over the page break, and then insert this code into the page in question:

$('.split_this_table').each(function(index, element) {

    //the height available is dependant on another item on the page
    var expanded_line = $('#another_element').size().height / 5;

    //manual calculation of the header size
    var per_page = 20 - expanded_line;

    //how many pages of rows have we got?
    var pages = Math.ceil($('tbody tr').length / per_page);

    //if we only have one page no more
    if (pages == 1) {
        return;
    }

    //get the table we're splutting
    var table_to_split = $(element);
    var current_page   = 1;

    //loop through each of our pages
    for (current_page = 1; current_page <= pages; current_page++) {

        //make a new copy of the table
        var cloned_table = table_to_split.clone();

        //remove rows on later pages
        $('tbody tr', table_to_split).each(function(loop, row_element) {

            //if we've reached our max
            if (loop >= per_page) {

                //get rid of the row
                $(row_element).remove();
            }
        });

        //loop through the other copy
        $('tbody tr', cloned_table).each(function(loop, row_element) {

            //if we are before our current page
            if (loop < per_page) {

                //remove that one
                $(row_element).remove();
            }
        });

        //insert the other table afdter the copy
        if (current_page < pages) {

            //insert the new table
            cloned_table.insertAfter(table_to_split);
        }

        //make a break
        cloned_table.css('page-break-before', 'always');

        //reset the table to the copy
        table_to_split = cloned_table;
    }
});

Toggle radio buttons by clicking the label

admin 5 Comments

I’ve seen a few methods of this using over-elaborate JavaScript, when its really not necessary.

All you need to do is wrap the <input with a <label – as a label will inherently become a ‘clicky’ for its children:

<label><input type="radio" name="radio-option" value="cake" />cake</label>
<label><input type="radio" name="radio-option" value="biscuits" />biscuits</label>

 

Easy

preventDefault not firing on onkeyup event

admin Leave a comment

Simple one.

$(document).on('#element', 'keyup', function(event) {
    if (event.keyCode == 13) {
        event.preventDefault();
        //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.