Detect user inserting row or column in a google spreadsheet and reacting in a script

Question

In Google Apps Script, one of the basic tools is the onEdit trigger in a spreadsheet, which enables us to detect when a user edits a cell, and react to it.

How about when a user inserts a row or column ? Is there a way to detect that ?

Would that trigger an onEdit ? If so, I guess maintaining in the ScriptDb a count of the number of rows or column, and then checking each time would do, but would be very time costly, since getMaxRows() is already pretty slow, and reaching out to ScriptDb is as well.

What do you think ?

Answer 1

There are a number of editing actions that do not trigger onEdit(), this isn't a comprehensive list, there are many more reported exclusions:

• Form submissions Issue 4568.
• Inserting or Deleting Rows or Columns Issue 1363
• Changes in appearance (Font, Format, Alignment, Borders) Issue 2476
• Range fill (e.g. start with 1,2,3; select those cells, and drag the 'handle' to fill more cells with 4,5,6...) Issue 399
• Values written by scripts. Issue 338
• Find / Replace Issue 1754
• Issue 1568, Issue 1119

If you do want to know how many rows are in a spreadsheet, this takes about 120ms to execute:

var numCols = SpreadsheetApp.getActiveSheet().getRange("1:1").getLastColumn();
var numRows = SpreadsheetApp.getActiveSheet().getRange("A:A").getLastRow();

I've already shown that it's faster to write a value to a sheet than to use ScriptDB. You can expect an insignificant time to write a small range, around 1ms.

So, if you could detect a row or column being added, it would cost you less than 2 tenths of a second to register the change. This onEdit() demonstrates a technique to measure the extent of a spreadsheet, and reports changes in sheet dimensions. (To test, add or delete rows or columns, then make an edit that triggers onEdit().) It also contains timers - feel free to experiment with other ways of measuring and/or storing values, to see what works best for you.

function onEdit() {
  // Use start & stop to time operations
  var start = new Date().getTime();

  // We want the size of the sheet, so will select ranges across and down the
  // whole sheet. Cannot use getDataRange(), as it selects only occupied cells.
  var numCols = SpreadsheetApp.getActiveSheet().getRange("1:1").getLastColumn()
  var numRows = SpreadsheetApp.getActiveSheet().getRange("A:A").getLastRow();

  var stop = new Date().getTime();
  var timeToMeasure = (stop-start);

  // Did things change?
  var oldSize = SpreadsheetApp.getActiveSheet().getRange("A1:B1").getValues();
  if (oldSize[0][0] != numCols || oldSize[0][1] != numRows) {
    // Yes, they did - Let's store the new dimensions
    start = new Date().getTime();

    SpreadsheetApp.getActiveSheet().getRange("A1:B1").setValues([[numCols,numRows]]);

    var stop = new Date().getTime();
    var timeToStore = (stop-start);  

    Browser.msgBox("Sheet is "+numCols+" by "+numRows+"."
                  +" ("+timeToMeasure+"ms to measure, "+timeToStore+"ms to store.)");
  }
}