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

Google added an "On Change" event that detects row/column insertion/deletion along with other types of changes, the types for which you can see here under the allowed values for changeType. Following are the instructions from here detailing how to add a trigger to your project, so that you may have your function called when the "On Change" event occurs.

To manually create an installable trigger through a dialog in the script editor, follow these steps:

1. From the script editor, choose Edit > Current project's triggers.
2. Click the link that says: No triggers set up. Click here to add one now.
3. Under Run, select the name of function you want to trigger.
4. Under Events, select either Time-driven or the Google App that the script is bound to (for example, From spreadsheet).
5. Select and configure the type of trigger you want to create (for example, an Hour timer that runs Every hour or an On open trigger).
6. Optionally, click Notifications to configure how and when you will be contacted by email if your triggered function fails.
7. Click Save.

In step 4 you would select From spreadsheet and in step 5 you would select On change. That should have the effect you're looking for. There are also options for adding triggers programmatically and requesting authorization, if you are trying to use this in an add-on to be distributed to users. Both are detailed in the Installable Triggers documentation.

Answer 2

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.)");
  }
}