Timestamps onEdit running slow - how to optimize?

378
February 25, 2018, at 04:32 AM

I created the code below based on lots of googling and inspiration from this post. I'm new to coding so you'll have to forgive my ignorance as I'm sure some of my methods are a bit rudimentary. I'm hoping you guys can offer some tips on how to improve my existing code and/or implement the modifications I laid out below.

The purpose of my code is to generate timestamps when certain cells in certain sheets are modified.

Sheet 1: Videos

Cells that trigger timestamp: Columns 1, 2, 3, 4, 5, 22, 23, 24 where Row > 10

Cells that receive timestamp: Column Y if blank, else Column Z

Sheet 2: Categories

Cells that trigger timestamp: Columns 13, 16, 17 where Row > 3

Cells that receive timestamp: Column T if blank, else Column U

My code

/*----------------------------Timestamps for Videos----------------------------*/
function onEditVideoTimestamps() {
//check to make sure on the right sheet
var s = SpreadsheetApp.getActiveSheet()
if (s.getName() == "Videos") {
//check to make sure edited row and column is within range
var cell = s.getActiveCell();
var rownbr = cell.getRow();
var colnbr = cell.getColumn();
var nbrcolumns = s.getMaxColumns();
var r = s.getRange(rownbr, 1, 1, nbrcolumns);
var rowArray = r.getValues();//now all your data for that row is in a two Dimensional array [[1,2,3,4,'My Data','etc']]  
if (rownbr > 10 && (colnbr == 1 || colnbr == 2 || colnbr == 3 || colnbr == 4 || colnbr == 5 || colnbr == 22 || colnbr == 23 || colnbr == 24)) {
  //is Y empty?
  var checkY = s.getRange('Y' + rownbr.toString()).getValue()
  if(checkY == '') {
    //Yes
    var time;
    time = new Date();
    time = Utilities.formatDate(time, "GMT-05:00", "MM/dd/yyyy HH:mm:ss");
    //the column letter is where the timestamp will appear
    SpreadsheetApp.getActiveSheet().getRange('Y' + rownbr.toString()).setValue(time);
    } else {
    //No
    var time;
    time = new Date();
    time = Utilities.formatDate(time, "GMT-05:00", "MM/dd/yyyy HH:mm:ss");
    //the column letter is where the timestamp will appear
    SpreadsheetApp.getActiveSheet().getRange('Z' + rownbr.toString()).setValue(time);
  }
}
/*----------------------------Timestamps for Categories/Albums----------------------------*/    
} else {
//check to make sure on the right sheet
var s = SpreadsheetApp.getActiveSheet()
if (s.getName() == "Categories") {
//check to make sure edited row and column is within range
var cell = s.getActiveCell();
var rownbr = cell.getRow();
var colnbr = cell.getColumn();
var nbrcolumns = s.getMaxColumns();
var r = s.getRange(rownbr, 1, 1, nbrcolumns);
var rowArray = r.getValues();//now all your data for that row is in a two Dimensional array [[1,2,3,4,'My Data','etc']]  
if (rownbr > 3 && (colnbr == 13 || colnbr == 16 || colnbr == 17)) {
  //is T empty?
  var checkT = s.getRange('T' + rownbr.toString()).getValue()
  if(checkT == '') {
    //Yes
    var time;
    time = new Date();
    time = Utilities.formatDate(time, "GMT-05:00", "MM/dd/yyyy HH:mm:ss");
    //the column letter is where the timestamp will appear
    SpreadsheetApp.getActiveSheet().getRange('T' + rownbr.toString()).setValue(time);
    } else {
    //No
    var time;
    time = new Date();
    time = Utilities.formatDate(time, "GMT-05:00", "MM/dd/yyyy HH:mm:ss");
    //the column letter is where the timestamp will appear
    SpreadsheetApp.getActiveSheet().getRange('U' + rownbr.toString()).setValue(time);
    }
   }
  }
 }
}

Problems

  1. This code can take upwards of 40 seconds to run. Ideally it would take no more than 10, even less if possible since it's triggered onEdit. As it stands, I exceed my daily GAS limit pretty quickly.

  2. I would like to add the following logic to my script:

Videos

  • If column 1 is changed to "Approved", a new timestamp is generated in the initial timestamp column (Y) even if Y is already populated. Column 1 is a status field that can be changed at any time to "Approved", "Pending", "Removed", or "Denied". If it's changed to "Approved", I want Y to get the timestamp regardless of its contents. Otherwise, my current logic applies: If Y is populated, then the timestamp goes in Z (the Last Modified Timestamp).

Categories

I really just want the same change for this sheet, just different cell references.

  • If column 13 is changed to "Approved", a new timestamp is generated in column T regardless of whether or not it's already populated. If any other change is made within the range, and T is already populated, then the timestamp is placed in U.

Like I said, I'm very much a beginner, so any help is appreciated. Let me know if I need to clarify anything. Thanks

Answer 1

There is a lot of redundancy in your code. For example, in each boolean test, you initialize a time variable. You should do this once and then use the timestamp in the appropriate case.

The other immediate change you can implement is to use the onEdit event Object. This holds a Range parameter that you can use to find the active row and column without doing so many calls to the spreadsheet, which should speed up the script.

Here's an optimized script for your current needs. In testing, it ran in less than one second each time. I haven't added the extra functionality. You'll just need a couple more boolean tests, so you should be able to do that.

function onEdit(e) {
  var time = Utilities.formatDate(new Date(), "GMT-05:00", "MM/dd/yyyy HH:mm:ss");
  var vidColNbr = [1,2,3,4,5,22,23,24];
  var catColNbr = [13,16,17]
  var sheet = e.range.getSheet();
  //check to make sure on the right sheet
  if (sheet.getName() == "Videos") {
    //check to make sure edited row and column is within range
    if((e.range.getRow() >= 10) && (vidColNbr.indexOf(e.range.getColumn()) > -1)) {
      //is Y empty?
      var checkY = sheet.getRange('Y' + e.range.getRow()).getValue();
      if(checkY == '') {
        //Yes
        SpreadsheetApp.getActiveSheet().getRange('Y' + e.range.getRow()).setValue(time);
      } else {
        //No
        SpreadsheetApp.getActiveSheet().getRange('Z' + e.range.getRow()).setValue(time);
      }
    }
  /*----------------------------Timestamps for Categories/Albums----------------------------*/    
  } else {
    //check to make sure on the right sheet
    if (sheet.getName() == "Categories") {
    //check to make sure edited row and column is within range
      if ((e.range.getRow() >= 3) && (catColNbr.indexOf(e.range.getColumn()) > -1)) {
        //is T empty?
        var checkT = sheet.getRange('T' + e.range.getRow()).getValue()
          if(checkT == '') {
            SpreadsheetApp.getActiveSheet().getRange('T' + e.range.getRow()).setValue(time);
          } else {
            SpreadsheetApp.getActiveSheet().getRange('U' + e.range.getRow()).setValue(time);
        }
      }
    }
  }
}

The event Object reference is very helpful. You can access most methods and classes through the onEdit event which can really speed up execution.

Rent Charter Buses Company
READ ALSO
Create dynamic webpage based on data from external CSV

Create dynamic webpage based on data from external CSV

I have a semi-dynamic HTML webpage which displays some data (ie

188
Why PHP setCookie() sets another variable as a cookie than I want?

Why PHP setCookie() sets another variable as a cookie than I want?

I want to set price[0] as a cookie, but it seems to set $ln as a cookie because it doesn't matter whether I write $price[0] or $ln as a parameter it shows me in both variations the same output$price[0] is a float value

153
open cart : Authorize .net payment module not visible under checkout even after installing module

open cart : Authorize .net payment module not visible under checkout even after installing module

i am new to open cart : i have added one product in my cart and trying to checkout with Authorizenet payment module but the thing is that Authorize

222
Redraw google chart based on user input via AJAX request

Redraw google chart based on user input via AJAX request

I have a google chart pulling data from my database that works as I want it toBased on a get request in the URL it draws the data from the selected table

152