Styjun

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.

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

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).