How to: Google Sheet Script to Edit Cell Contents

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.
See the video (1:19) on the behavior so you can a feel for what the script does.  I think it is cool and I am sure our clients will appreciate it.

Google Sheets Starter Script

Here is the script.  Simply cut and paste this entire script into Tools,  Script Editor menu.  Save the script in the editor.  Then reload the Google Sheet so that it loads up the script.  A new menu will  be shown allowing you to activate the feature on the column you are working on.  Finally, edit a cell and navigate a way.  The sheet should update the cell with the date stamp.
//------------------------------------------------------------------
// 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.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's 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 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to setup a conversation.

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

| Tags: | Category: Technical | Leave a comment

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>