How to determine the amount of rows inserted in a sheet with onChange trigger?

Question

I want to replicate some data from a spreadsheet to another. The first is often changing in size and the second one has a few columns from the first and then some other columns with it's own data.

I'd like to insert/delete rows in the second one depending on the rows inserted or deleted in the first one. They have to match the previous data.

I've tried to use an onChange trigger to detect when an INSERT_ROW or REMOVE_ROW changeType was detected. It works fine when it's just a row inserted, but not when they insert more than one. Also, when they insert a row and after they undo the action changeType is EDIT and not REMOVE_ROW. Fails.

Then I decided to add a hidden column with an index number in each row, so if there is a gap of numbers between row 5 and 6 it means that I have to insert 3 rows in the second sheet after the 5th row, then I rebuilt the index to check further changes... It also works if several rows have been removed.

That way seemed to work fine until I realised that the users can duplicate the rows or copy/paste the full row and then modify the data with new info BUT the index is also copied and when I check the differnce to check if any row has been removed, then it fails.

First try:

  if (event.changeType == 'INSERT_ROW' && tab =='Hoja 1') {
    for (var i = 0 ; i < indexes.length ; i++) {
      if (indexes[i] =='') {
        destSheet.insertRowAfter(i+1);
      }
    }
  }
  if (event.changeType == 'REMOVE_ROW' && tab =='Hoja 1') {
    for (var i = 0 ; i < indexes.length ; i++) {
      if (indexes[i]-indexes[i+1] < -1 && indexes[i] != 0) {
        if (indexes[i] != lastRow) {
          destSheet.deleteRows(i+3,(indexes[i]-indexes[i+1])*-1-1)
        }
      }
    }
  }
  for (var j = 0; j < lastRow-1; j++) {
    indexs.getCell(j+1, 1).setValue(j+1);
  }
}

Second try:

function checkLines (sheet, destSheet)  {
  for (var i = 0 ; i < indexes.length ; i++) {
    if (indexes[i] =='') {
      destSheet.insertRowAfter(i+1);
    }
    if (indexes[i]-indexes[i+1] < -1 && indexes[i] != 0) {
      if (indexes[i] != lastRow) {
        destSheet.deleteRows(i+3,(indexes[i]-indexes[i+1])*-1-1)
      }
    }
  }
}

I'd like to have a copy of the selected columns of the source sheet in a destination sheet and if the first one changes its rows the same should happen in the second one.

In some cases the destination doesn't get actualised and then the information written in the following column doesn't match with the info of the first columns.

Here is an link to an example of the source sheet: https://docs.google.com/spreadsheets/d/19OnwKIEm2OFymjsjqeoQYWcA9BNAJTM0ap2rdpQlZoQ/edit?usp=sharing

And here the destination: https://docs.google.com/spreadsheets/d/10vbMIqQE1miNfuJXQ1f_MSJnVs9MJrAOcg5_ZrAPWP8/edit?usp=sharing

I'll appreciate any suggestion... And please let me know if you need further explanations... english is not my native language and I'm not sure if you understand my problem.

Thank you very much.

V.

How do they insert more than a row? – TheMaster Mar 28 '19 at 21:10 — TheMaster, Mar 28 '19 at 21:10

score 0 · Answer 1 · answered Mar 28 '19 at 22:34

You can use something like this:

function initRows() {//run once before first use
  var lastrow=SpreadsheetApp.getActiveSheet().getLastRow();
  PropertiesService.getScriptProperties().setProperty('ROWS',lastrow);
  return lastrow;
}

function getRows() {
  return PropertiesService.getScriptProperties().getProperty('ROWS');
}

function setRows(value) {
  PropertiesService.getScriptProperties().setProperty('ROWS',value);
}

function detectRowChange(e) {//attach to onChange installable trigger
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var number=Math.abs(getRows()-sh.getLastRow());
  var html=Utilities.formatString('Change Type: %s Number: %s', e.changeType,number);
  var ui=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModelessDialog(ui, 'Change');
  setRows(sh.getLastRow());
}

This utilizes a dialog to identify the type of change and the number of rows effected. You will probably want to customize it for your particular needs. Don't forget to run initRows() before setting up the trigger.

PropertiesService

Thanks Cooper, but this solution only tells me how many rows have been added or removed, not the position. — Victor Bove elrowfamily, Apr 01 '19 at 09:37

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

The active range¹ has the number of rows inserted.

Snippet:

function onChange(e){
  if (e.changeType === 'INSERT_ROW'){
    const rng = SpreadsheetApp.getActiveRange();
    const row = rng.getRow();//Row where insert row is done
    const lrow = rng.getLastRow();//Last row in the newly active range
    const numRows = 1+lrow-row;//number of rows inserted
  }
}

Also look at adding developer metadata² to rows, so that when they're moved, you might be able to recognize it.

I'd like to have a copy of the selected columns of the source sheet in a destination sheet and if the first one changes its rows the same should happen in the second one.

Easiest way is to just get all the data and paste it in the destination sheet(getValues and setValues).

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 29 '19 at 11:34

TheMaster

45,448
6
62
85

Hey TheMaster, thanks for your response. I'll try the second option (adding metadata) but it seems pretty complicated (I'm a beginner). I'll have a look. – Victor Bove elrowfamily Apr 01 '19 at 09:33
@VictorBoveelrowfamily Did you test the first option? – TheMaster Sep 08 '20 at 05:12

How to determine the amount of rows inserted in a sheet with onChange trigger?

2 Answers2

Snippet:

Linked