Trigger for onChange on add new row only

Question

I want the trigger which work for 'add new row' only, but not other edit function. Right now the changeType of the onChange are Edit(include add new row), INSERT_ROW (but not add new row), etc., which is strange. Since onEdit is not including 'add new row', so we need to use onChange. But when in onChange it defines the 'add new row' as 'EDIT'.

So it makes a mess, since it will trigger everytime people edit anything, not just adding the new row.

Answer 1

You want to execute a trigger when a new row is inserted but you are concerned about how the onChange trigger may confuse an "edit" with "add new row".

The correct Trigger is onChange. This supports nine (9) change types. One of these is EDIT and another is INSERT_ROW. However you are forgetting that your code should evaluate the Event Objects to ensure that only the desired change has taken place (and possibly on the desired sheet) before any further code is processed.

Consider the script shown below. It uses an IF statement to test whether:

• was the change type: INSERT_ROW
• was the change on the preferred sheet: Sheet1.

The script will process code only if BOTH of these criteria are met. If the change type is "EDIT" then the script will not process your additional code.

---

function myFunction(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheetname = "Sheet1"
  var sheet = ss.getSheetByName(sheetname)

  // Logger.log(JSON.stringify(e)); //DEBUG

  // create some variables from Event Objects
  var type = e.changeType;
  var editedsheet = e.source.getSheetName()
  //Logger.log("DEBUG: The sheet name is "+e.source.getSheetName()+", and the change type is "+type);

  if (sheetname == editedsheet && type == "INSERT_ROW" ){
    // inserted a row on Sheet1
    Logger.log("DEBUG: Inserted a row on Sheet1")
  }
  else{
    // either change type wasn't insert_row
    // or the change wasn't on sheet1
    Logger.log("DEBUG: didn't insert a row on Sheet1")
  }
}

Answer 2

It is not 'perfect' but a lot of integrations (including some of Google's own) to Google Sheets are not designed to actually INSERT_ROW as the default action. They will edit new data on empty rows if present as an EDIT.

In short, whatever you are using to update your sheet, it is not creating a brand new row, it is merely editing the next free cell at the end of the data.

I do have a really simple work around for this, though, skip to the end for that.

Testing This

If you created your trigger correctly (either by service or the GUI) you will, as you state, see many EDIT updates, instead of INSERT_ROW.

To test out where things are being made or done correctly, you run the following tests to show you how various interfaces handle the actions.

1. Firstly, in Google Sheets let's create the control data:
   1. Control Test 1 - Add data on a new empty row at the end.
   2. Control Test 2 - Right click a row and select the 'Insert 1 above' or 'Insert 1 below' option.
2. Now, in Power Automate:
   1. Make a new 'Insert row' sheets action.
   2. Test that.
3. Now, in Slack:
   1. In a Workflow use 'Add a spreadsheet row'.
   2. Test that.
4. Now, in Zapier:
   1. In a ZAP use 'Create Spreadsheet Row in Google Sheets'.
   2. Test that.

From the above tests (or others that you choose) you will see definitive differences.

1. Google Sheets - Adding data to an empty row at the end is an EDIT, and inserting a row above/below is an INSERT_ROW.
2. Power Automate - This gives an EDIT.
3. Slack - This gives an EDIT.
4. Zapier - This gives an INSERT_ROW.

So we can surmise that not all integrations are born equal. Only Zapier is inputting the data as you would expect, which is most disappointing, given how many other folks will be relying on the other two.

Work Around

Delete ALL empty cells at the end of your data.

That's it.

To be clear, that is:

1. Left click a row number once so that it is highlighted.
2. Hold down CTRL+SHIFT (win/linux), and tap down until it's reached the last available row on the sheet (you will see the 'Add 1000 more rows at the bottom' option).
3. Right click any visible row number and select 'Delete rows # - ##'

Now, if you run tests 2 and 3 again, you will note that they are forcing themselves to insert the new row. The change is correctly logged as a INSERT_ROW.