This article is relevant if you want to quickly learn how to use script to dynamically edit the cells of a Google Sheet.

Background

In our NetSuite implementation practice, we use Google Sheets for complex task management.  Every row in the sheet is an action task.  We devote one column to status.  To promote accountability, we like to update respective status cell with today’s date so we can have visible history.  The technique we use is to produce a date stamp with a format of ‘yyyymmdd’ where the date October 30, 2016 would show up ‘20161030’.  With this format, we can sort and have information line up nicely which is easy on the eye.

The practice though can be somewhat slow and tedious to type in on every task update.  Cut-and-Paste operation helps but I knew I could do better.  So I decided to finally invest the time to learn the Google Sheets API syntax to do the work for me.

Google Sheets API and the Event Object

The key to this feature is to perform the following:

1. Active Cell: We need to know what cell the user is editing.
2. Before and After Values: We need the before and after values.  Fortunately, the Google API offers an Event object that supplies the new “value” and “oldValue” which is perfect for our operation.
3. Conditional: We need a way to know if the cell we are editing deserves the date stamp treatment.  Here, I leverage the “Note” area on the top column cell to store some extra information to act as a flag.

Google Sheets Starter Script

//------------------------------------------------------------------
// Copyright 2016, All rights reserved, Prolecto Resources, Inc.
// Leaders in NetSuite Systems Integration (blog.prolecto.com)
//
// This script may be copied and used so long as this header accompanies.
//
// Author: Marty Zigman 20161030
//------------------------------------------------------------------

var APP_PREFIX = "prefixDate:true";

Date.prototype.yyyymmdd = function() {
  return this.toISOString().slice(0,10).replace(/-/g,"");
};

function isObject(val) {
  if (val === null) { return false;}
  return ( (typeof val === 'function') || (typeof val === 'object') );
}

function onEdit(e){
  //if the column has the feature active; conditional for oldvalues
  if (SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, e.range.getColumn(),1).getNote() == APP_PREFIX ){
    var d = new Date().yyyymmdd() + ": ";
    var old = "";
    if (e.oldValue) {
      old = '\n' + e.oldValue;
    };
    //uncomment following line to inspect the event object
    //Logger.log(JSON.stringify(e));
    if (! isObject(e.value) ) {
      e.range.setValue(d + e.value + old);
    }
  }
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Prolecto Utils')
      .addItem('Activate YYYYMMDD Prefix', 'prefixDate')
      .addToUi();
}

function prefixDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = sheet.getActiveCell();
  sheet.getRange(1, cell.getColumn(), 1).setNote(APP_PREFIX)
  SpreadsheetApp.getUi().alert('Activated Prefix Dating on Column: ' + cell.getColumn())
}

Revisions

Since the time I crafted this article, I have produced revisions:

1. 20170713: Add user alias to edits.

Automate Your Repetitive Tasks

The script above is a good illustration of how a repetitive tasks can be automated with a little bit of script. Feel free to comment below to make it even better.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube