Bi-directional sync when rows are deleted

Question

I have two sheets which are synced in Google Sheets using a GAS i found on this SO, which i put on a change trigger Is there a way to keep two sheets synchronized?

script is here

var sourceSpreadsheetID = "ID HERE";
var sourceWorksheetName = "SHEET NAME HERE";
var destinationSpreadsheetID = "ID HERE";
var destinationWorksheetName = "SHEET NAME HERE";

function importData() {
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getDataRange();
  var toSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(destinationWorksheetName);
  var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns())
  toRange.setValues(thisData.getValues()); 
}

the real issue I am facing is that if you delete a row in the middle of a synced sheet, the getDataRange calculates the source's bottom data range, and syncs it to only that range in the destination sheet, creating double rows for any out of range items

Example: I have 4 rows on my source sheet AND my destination sheet

a
b
c
d

delete row c, source sheet is now
a
b
d

destination sheet syncs and is now
a
b
d
d

i delete row 3, the script syncs 3 rows on the destination sheet, leaving the 4th row as a double

Any thoughts would be welcome on how to overcome this, I have looked at named ranges, but did not like that I had to put a ceiling on the range for number of rows, when the 1000 row is created the range would no longer work

`toWorksheet.clear()` should delete the entire sheet. – Tesseract Jun 03 '15 at 15:00 — Tesseract, Jun 03 '15 at 15:00
that works great, make an answer and I'll accept – Jay Rizzi Jun 03 '15 at 16:58 — Jay Rizzi, Jun 03 '15 at 16:58

score 1 · Accepted Answer · answered Jun 03 '15 at 22:50

1

You just have to delete the contents of the sheet before you set the new values.

toWorksheet.clear()
toRange.setValues(thisData.getValues());

answered Jun 03 '15 at 22:50

Tesseract

8,049
2
20
37

Bi-directional sync when rows are deleted

1 Answers1