I have a google spreadsheet that uses the google App scripting onEdit() event to provide unit conversions when entering values. This was working as recently as last week, but no longer seems to work. Aside from the change of the Range setComment() API being deprecated in favour of setNote() I can see no reason why this script would not work anymore. Interestingly the execution log seems to imply it SHOULD be working, but the cells do not update:
[13-06-26 15:29:57:973 BST] Starting execution
[13-06-26 15:29:57:976 BST] Spreadsheet.getActiveSheet() [0 seconds]
[13-06-26 15:29:57:977 BST] Range.getWidth() [0 seconds]
[13-06-26 15:29:57:978 BST] Range.getHeight() [0 seconds]
[13-06-26 15:29:58:151 BST] Range.getValue() [0.173 seconds]
[13-06-26 15:29:58:151 BST] Range.setValue([13.670166224]) [0 seconds]
[13-06-26 15:29:58:151 BST] Range.setNote([Distance Conversion: 22km => 13.670166224]) [0 seconds]
[13-06-26 15:29:58:152 BST] Execution succeeded [0.176 seconds total runtime]
Here's the code. Grateful for any hints on what's changed!
function Milify( x )
{
function convert( str, p1, offset, s )
{
// p1 is the matched number
// 1 kilometre = 0.6a21371192 miles
return Number(p1) * 0.621371192;
}
var s = String( x );
var test = /(\d+(?:\.\d*)?)km/;
return s.replace( test, convert );
}
function onEdit( event )
{
var ss = event.source.getActiveSheet();
//var er = event.source.getActiveRange();
var er = event.range;
//er.setNote( "" );
//er.setNote( er.getNote() + "\n" + "Trigger Fired for " + er.getA1Notation() + " with " + er.getValue() + " " + er.getWidth() + "x" + er.getHeight() );
if ( ( er.getWidth() * er.getHeight() ) === 1 )
{
var valueOriginal = er.getValue();
var valueInMiles = Milify( valueOriginal, er );
er.setValue( valueInMiles );
er.setNote( "Distance Conversion: " + valueOriginal + " => " + valueInMiles );
}
}
Thanks.