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 ?

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

Solution 2

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.

Solution 3

There is another way that I've just used recently. Every time onEdit() is triggered, it returns an event object (e) that gives you some valuable information about what's happening.

For example it gives you the range, which you can retrieve from e.range. From there you can transverse in many different ways and get to know, for example, which row is being edited. But there's more helpful data as well in the e object. It gives you the "oldvalue" (e.oldValue) of the cell you edited, and the new value (e.value).

One possible way to mix all this information together would be to get the range corresponding to the row you're editing, then check if al the cells are empty (but the one you just edited) and if there's no oldValue.

That doesn't correspond necessarily to the last row of your spreadsheet, but an empty row. If you're consistent with how you fill your data this might work for you:

//val = inserted value (e.value);
//old = old Value (e.oldValue);
//col = number of column being edited
//arr = array with the indexes of the columns that should be completed so as to make a new row [0,1,2...n]
function isInsert(old, val, col, arr){
   if((typeof val != "object")&&!old&&(arr.some(isNotEmpty, col)))
     return true;
   else
     return false;
}

function isNotEmpty(el){
   if(this == el)
     return true;
}

Solution 4

I was having trouble with this until I gave permissions to the script. Otherwise the PropertiesService functionality will not work. Once I did, I was able to detect which row had been inserted with the following code:

var props = PropertiesService.getUserProperties();

function onEdit(e) {
  props.setProperty("firstRow", e.range.getRow());
  props.setProperty("lastRow", e.range.getLastRow());
}


function onChange(e){
if(e.changeType=="INSERT_ROW")
    SpreadsheetApp.getUi().alert("Inserted Rows: " +
                                 props.getProperty("firstRow") + 
                                 " - " +
                                 props.getProperty("lastRow"));
}

Solution 5

I've been playing around with onEdit and onChange. The onEdit response allows you to access the rows that were edited. Unfortunately, the onChange response does not allow you to do this. So, for a robust solution, it looks like you need to appeal to both triggers. If your sheet does not require empty rows/columns, the below script removes any newly added rows/columns, deletes all blank rows/columns (in case the user bulk added rows/columns), then warns the user that they cannot add rows or columns:

//////////////////////
// Global Variables //
//////////////////////

var SHEET = SpreadsheetApp.getActiveSheet();
var PROPERTIES = PropertiesService.getScriptProperties();

////////////////////
// Event Triggers //
////////////////////

/**
 * Track original sheet row/column count and register onChange trigger.
 */
function onOpen()
{
    // Set original dimensions
    PROPERTIES.setProperty('rows', SHEET.getMaxRows());
    PROPERTIES.setProperty('columns', SHEET.getMaxColumns());

    // Create onChange trigger
    ScriptApp
        .newTrigger('deleteNewRowsAndColumns')
        .forSpreadsheet(SpreadsheetApp.getActive())
        .onChange()
        .create();
}

/**
 * If new rows or columns were added to the sheet
 * warn the user that they cannot perform these
 * actions and delete empty (new) rows and columns.
 *
 * @param e
 */
function deleteNewRowsAndColumns(e)
{
    switch(e.changeType) {
        case 'INSERT_COLUMN':
            removeEmptyColumns();
            warn();
            break;
        case 'INSERT_ROW':
            removeEmptyRows();
            warn();
            break;
        default:
            return
    }
}

///////////////
// Utilities //
///////////////

/**
 * Remove empty columns.
 *
 * This function assumes you have a header row in which
 * all columns should have a value. Change headerRow value
 * if your headers are not in row 1.
 */
function removeEmptyColumns() {
    var maxColumns = SHEET.getMaxColumns();
    var lastColumn = SHEET.getLastColumn();
    if (maxColumns - lastColumn != 0) {
        // New column(s) were added to the end of the sheet.
        SHEET.deleteColumns(lastColumn + 1, maxColumns - lastColumn);
    } else {
        // New column was added in the middle of the sheet.
        // Start from last column and work backwards, delete
        // first column found with empty header cell.
        var headerRow = 1;
        var headers =  SHEET.getRange(headerRow, 1, 1, lastColumn).getValues()[0];
        for (var col = lastColumn; col >= 1; col--) {
            if (headers[col -1] == '') {
                SHEET.deleteColumn(col);
                // Since can only insert one column to the left
                // or right at a time, can safely exit here;
                break;
            }
        }
    }
}

/**
 * Remove empty rows.
 *
 * This function assumes that all rows should
 * have data in the first cell.
 */
function removeEmptyRows() {
    var maxRows = SHEET.getMaxRows();
    var lastRow = SHEET.getLastRow();
    if (maxRows-lastRow != 0) {
        // New row(s) were added to the end of the sheet.
        SHEET.deleteRows(lastRow + 1, maxRows - lastRow);
    } else {
        // New row was added in the middle of the sheet.
        // Start from last column and work backwards, delete
        // first empty column found.
        var values = SHEET.getRange('A:A').getValues();
        var startIndex = values.length - 1;
        for (var i = startIndex; i >= 0; i--) {
            if (values[i] && values[i][0] == '') {
                SHEET.deleteRow(i + 1);
                // User can bulk add rows to the bottom of the file
                // but can only add 1 above or below at a time in the
                // middle of the file, so it's safe to exit here.
                break;
            }
        }
    }
}

/**
 * Return user warning message about adding new rows and columns
 */
function warn()
{
    SpreadsheetApp.getUi().alert('You cannot add new rows or columns.');
}

View more solutions

Comments

[Vic Seedoubleyew]

Hi Mogsdad !Thanks a lot for your answer, it's really cool to have such a precise discussion. I have tried to set up an onedit trigger on a function, but indeed it doesn't get called when a row or a column is inserted. That's a pity, since one of the things I am trying to do, is detect this instantly, so that I can rollback the change. In a way, prevent the user from inserting rows or columns. Isn't there any trigger at all that we could use to directly or indirectly wake something up in these situations ?

[Mogsdad]

The best you could do is set up a time-based trigger - although I don't know how you'd figure out what was added or deleted. I've updated my answer with links to a number of reported issues - go and star them to get updates on progress, and also add your own comments to encourage Google to address them. If your question has been answered as well as can be expected, remember to "accept" the answer. (And an upvote is always appreciated!)

[bvj]

Nearly 3 years later, and the steps are very similar. Great answer, +1, and all that, but consider keeping this post up-to-date. Different day, same Sheet. Or not.

[wulftone]

I'm having trouble too, can you tell me how you "gave permissions to the script"? I don't see anything in the docs about that!