Detect user by inserting row or column into google spreadsheet and reacting to script

Question

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

How about when the user inserts a row or column? Is there any way to detect this?

Could this cause onEdit? If this is the case, I assume that ScriptDb will indicate the number of rows or columns, and then checking each time will be done, but it will be very expensive, since getMaxRows () is already quite slow and the ScriptDb call is doing as well.

What do you think?

Answer 1

There are a number of editing actions that do not run onEdit() , this is not a complete list, there are many more exceptions reported :

• Form Submissions Problem 4568 .
• Insert or delete rows or columns. Problem 1363
• Appearance Changes (Font, Format, Alignment, Borders) Issue 2476
• Filling the range (for example, start with 1,2,3, select these cells and drag the handle to fill in more cells 4,5,6 ...) Problem 399
• Values ​​written by scripts. Problem 338
• Find / Replace Problem 1754
• Issue 1568 , Issue 1119

If you want to know how many rows are in the spreadsheet, it will take about 120 ms:

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

I have already shown that writing a value to a sheet is faster than using ScriptDB. You can expect a little time to write a small range, about 1 ms.

So, if you can find the added row or column, the change will cost less than 2 tenths of a second to register. This onEdit() demonstrates a method for measuring the scale of a spreadsheet and reports changes in sheet size. (To check, add or remove rows or columns, then do the editing that runs onEdit() .) It also contains timers - feel free to experiment with other methods of measuring and / or saving 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 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.)"); } } 

Answer 2

Google added an “On” event, which detects insertion / deletion of a row / column along with other types of changes, types for which you can see here under the valid values ​​for changeType . Below are instructions here that detail how to add a trigger to your project so that you can call your function when the event is “On.” takes place.

To manually create an installation trigger through a dialog in the script editor, do the following:

• In the script editor, select Modify> Current project triggers .
• Click the link that says: No triggers. Click here to add it now .
• In the Run section, select the name of the function that you want to call.
• In the Events section, select the Time-driven or Google application that the script is associated with (for example, from a table ).
• Select and configure the type of trigger that you want to create (for example, an hourly timer that starts every hour or on to open a trigger).
• If necessary, click Notifications to configure how and when you will be connected by e-mail if your called function fails.
• Click Save .

In step 4, you select From table , and in step 5, select On. . This should have the effect you are looking for. There are also options for adding triggers programmatically and requesting authorization if you are trying to use this in an add-in for distribution to users. Both are described in detail in the Installable Triggers documentation.

Answer 3

There is another way that I used recently. Each time onEdit () is triggered, it returns an event (e) object , which gives you valuable information about what is happening.

For example, it gives you a range that you can get from e.range. From there, you can crosswise in different ways and find out, for example, which line is being edited. But there is more useful data in the e-object. It gives you the "oldvalue" ( e.oldValue ) of the changed cell and the new value ( e.value ).

One possible way to combine all of this information would be to get a range that matches the row you are editing, and then check to see if there are any cells in it (but the one you just edited) and if there is no oldValue.

This is not the last row of your table, but empty. If you agree on how you fill out your details, this may 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; } 

Answer 4

I had problems with this until I gave permission to the script. Otherwise, the functionality of the PropertiesService will not work. Once I did this, I was able to determine which row was 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")); } 

Answer 5

I played with onEdit and onChange. The onEdit answer allows you to access editable lines. Unfortunately, the onChange answer does not allow you to do this. Thus, for a reliable solution, it seems you need to refer to both triggers. If your sheet does not require empty rows / columns, below the script deletes all newly added rows / columns, deletes all empty rows / columns (in case the user adds the number of rows / columns), and 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 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.'); }