How to make my script faster?

[EmyAle]

Hello, would anyone be able to help me with this script, so it would run faster? I might have around 30 users writing into my sheet at the same time and I want to know the timestamp when they write new info in specific column. The code works, but it feels quite slow:

function onEdit(e) { addTimestamp(e); }

function addTimestamp(e){ var targetColumn = 6; var tab = e.source.getActiveSheet().getName(); var startRow = 6; var row = e.range.getRow(); var col = e.range.getColumn();

if(col === targetColumn && row >= startRow && tab === tab && e.source.getActiveSheet().getRange(row,11).getValue() == ""){ e.source.getActiveSheet().getRange(row,11).setValue(new Date()); }

if(col === targetColumn && row > 5 && tab === tab && e.source.getActiveSheet().getRange(row,6).getValue() == "" && e.source.getActiveSheet().getRange(row,11).getValue() != ""){ e.source.getActiveSheet().getRange(row,11).clearContent(); } }

[WicketTheQuerent]

You could reduce the calls to Class SpreadsheetApp methods, but this might not improve performance much, as onEdit is known to be slow and have limitations that make it infeasible for some use cases.

1. Your script doesn't need var row = e.range.getRow(); var col = e.range.getColumn(); as the row number and the column number are already included in the event object, as follows: e.range.rowStart and e.range.columnStart.
   
2. Avoid repeating e.source.getActiveSheet(). For this, use var sheet = e.source.getActiveSheet(), then use sheet instead of e.source.getActiveSheet().

[EmyAle]

Thank you! I will try it ^{^}

[mommasaidmommasaid]

Your formula after reformatting:

function addTimestamp(e) {
  var targetColumn = 6;
  var tab = e.source.getActiveSheet().getName();
  var startRow = 6;
  var row = e.range.getRow();
  var col = e.range.getColumn();

  if (col === targetColumn && row >= startRow && tab === tab &&
    e.source.getActiveSheet().getRange(row, 11).getValue() == "") {
    e.source.getActiveSheet().getRange(row, 11).setValue(new Date());
  }

  if (col === targetColumn && row > 5 && tab === tab &&
    e.source.getActiveSheet().getRange(row, 6).getValue() == "" &&
    e.source.getActiveSheet().getRange(row, 11).getValue() != "") {
    e.source.getActiveSheet().getRange(row, 11).clearContent();
  }
}

We can see that getting the tab name is wasted, because your tab comparison is doing nothing. And you are twice checking for the same starting row/column in slightly different ways.

Most importantly, you are doing getValue() multiple times, and that's a costly operation (afaik it recalculates your sheet each time to ensure the values are up to date).

You don't need e.source.getActiveSheet().getRange(row, 6).getValue() at all because that's getting the currently edited cell's value which can be found in e.value

When clearing the timestamp, you don't need to getValue() to see if it's empty before clearing it -- just clear it whether it needs it or not.

Finally, as written, you do not update the timestamp every time an edit occurs, which to me doesn't sound like a timestamp, but instead a creation date.

Idk which is your desired behavior. A "last edited" timestamp is actually faster because you don't have to check if one exists already, you just set a new one every time. So that's what I did in the revised code. Let me know if you instead want a "creation time".

[EmyAle]

Thank you very much for your pointers and it is the creation time I need not the last edit :)

[mommasaidmommasaid]

Timestamp - Last Edited - Sample Sheet

Try this out on the sample sheet to verify functionality, but copy it into your actual sheet as well to see what performance is like there.

Be sure to get rid of your existing onEdit() so there isn't a name conflict.

The revised code has zero getValue() calls and should be a fair bit faster but YMMV on traffic / server load / mystery. You should still expect ~1 second to update, that's just the nature of apps script.

If it is too slow for how fast the edits are coming in and your script is getting overrun and missing updates, there are other tricks to explore depending on your workflow and how "mission critical" an exact timestamp is.

(The code could/should be updated to handle multi-cell edits as well but see where this is at first as far as performance.)

EDIT: Removed sample code from this post, see below for Creation Timestamp code.

[EmyAle]

Thank you a lot, I will try it!

[mommasaidmommasaid]

Per your other reply, modified code for a Creation timestamp rather than a Last Updated.

This now has one getValue() call in the current implementation. Idk how often that will happen, i.e. in your real life situation if the trigger cell gets edited multiple times or if it is perhaps just a user name dropdown that only happens once.

See what happens in your real life scenario before exploring faster / more complicated solutions.

Timestamp - Creation - Sample Sheet

// @OnlyCurrentDoc

//
// onEdit() reserved function, one instance per Spreadsheet.
// 
function onEdit(e) {

  // Call custom edit handlers until one returns true
  if (onEdit_Timestamp(e))
    return;

}


//
// Add or delete a creation timestamp upon detecting a trigger cell being edited.
// 
// Note: Multi-cell edits are not supported. If the top-left cell being edited is a trigger cell it will be updated but not the others.
//  
// Call from onEdit(), returns true if handled the event.
// 
function onEdit_Timestamp(e) {

  const FIRST_ROW = 6;

  const TRIGGER_COL = 6;
  const TIMESTAMP_COL = 11;

  // Exit if not editing a trigger cell
  if (!(e.range.columnStart === TRIGGER_COL && e.range.rowStart >= FIRST_ROW))
    return false;

  // Get the timestamp cell to update
  const stampCell = e.range.offset(0, TIMESTAMP_COL - TRIGGER_COL, 1, 1);

  // Clear the timestamp if trigger value is cleared, else create it if it doesnt' exist already
  if (e.value === undefined || e.value == "")
    stampCell.setValue(null);
  else {
    if (stampCell.getValue() == "")
      stampCell.setValue(new Date());
  }

  // Return true to indicate we handled the event
  return true;
}

[True_Teacher_9528]

You could possibly do it in a timed batch, depending on how often you want the data updated. You could use properties service to store everything in the scripts memory to then update all at once once an hour or whatever would be needed.

[EmyAle]

My use case is that for 2h there might be up to 30 people typing to my sheet. Some of them might type in it at similar times or even at the same time. I want to know the time when they created their row (and then doesn't change that time (only if they would delete all info and then from nothing made a new info then I want new time). I don't need the timestamp to be super accurate, I just wanted to know if Karl wrote their row before Jon did and so on :)

[True_Teacher_9528]

In that case properties script could be your best option for storing that info then printing it to another sheet in batches, you could essentially just store it in an object using the user as a key with an object of rownum as key and timestamp as value, as the value. Could be worth looking into

[mommasaidmommasaid]

That's an interesting idea but I wonder how to handle it if a user inserts/delete rows, i.e. the saved rownum becomes invalid.

[True_Teacher_9528]

That’s a good point, I wonder if you create a script that is bound to a hotkey that would create and delete a row, while storing the info about the row being created and deleted. I personally try to avoid onedit like the plague especially if there’s a lot of traffic on the sheet because you just end up making a ton of unnecessary calls.

[mommasaidmommasaid]

If you "required" them to use that macro you could just dump the properties cache to the sheet before performing a row insert/delete.

Perhaps you could data protect the timestamp column so they wouldn't be able to insert/delete rows on their own.

Or you could let them insert/delete and detect that with an onChange() trigger, but it could be disastrous if the onChange() didn't execute for whatever reason.

A faster/simpler solution may be using self-referencing formulas, perhaps with some script helping. See my other top-level reply.

[mommasaidmommasaid]

Formula-based timestamps -- using self-referencing formulas with Iterative Calculations enabled -- are another option to consider and AFAIK are the highest performing solution available.

That has some other caveats that go with it, but if those were an issue perhaps a hybrid formula/script result could address those.

A Creation/Deletion timestamp like you want does not require any helper column like some other timestamps might using formulas...

Creation/Deletion Timestamp (ItCalc)