I have a unique Google Sheets document with the below code that allows me to:
- copy new rows from source in sheet
MEL Micro Set Up
toBackup Set Up
- copy new rows from source in sheet
MEL Micro Confirmation
toBackup Confirmation
.
All works fine except when I delete rows from both source files it will delete rows from the backup files.
How can we avoid that?
function Copy() {
var sourceSheet1 = SpreadsheetApp.openById('17ekZcvPQYrcX1ny-RtPQczEfbmd1JZUkRVx6N7qTtCA').getSheetByName('MEL Micro Set Up');
var range1 = sourceSheet1.getRange(1, 1, sourceSheet1.getLastRow(), sourceSheet1.getLastColumn()).getA1Notation();
var sourceSheetValues1 = sourceSheet1.getRange(range1).getValues();
var destinationSheet1 = SpreadsheetApp.openById('17ekZcvPQYrcX1ny-RtPQczEfbmd1JZUkRVx6N7qTtCA').getSheetByName('Backup Set Up');
destinationSheet1.getRange(range1).setValues(sourceSheetValues1);
var sourceSheet2 = SpreadsheetApp.openById('17ekZcvPQYrcX1ny-RtPQczEfbmd1JZUkRVx6N7qTtCA').getSheetByName('MEL Micro Confirmation');
var range2 = sourceSheet2.getRange(1, 1, sourceSheet2.getLastRow(), sourceSheet2.getLastColumn()).getA1Notation();
var sourceSheetValues2 = sourceSheet2.getRange(range2).getValues();
var destinationSheet2 = SpreadsheetApp.openById('17ekZcvPQYrcX1ny-RtPQczEfbmd1JZUkRVx6N7qTtCA').getSheetByName('Backup Confirmation');
destinationSheet2.getRange(range2).setValues(sourceSheetValues2);
if (sourceSheet1.getName() !== 'MEL Micro Set Up' || range1.columnStart !== 11 || range1.value !== 0 ) return;
destinationSheet1.getSheetByName('Backup Set Up')
.appendRow(sourceSheet1.range.offset(0, -3, 1, 11)
.getValues()[0]);
sheet.deleteRow(sourceSheet1.range.rowStart);
if (sourceSheet2.getName() !== 'MEL Micro Confirmation' || range2.columnStart !== 11 || range2.value !== 0 ) return;
destinationSheet2.getSheetByName('Backup Confirmation')
.appendRow(sourceSheet2.range.offset(0, -3, 1, 11)
.getValues()[0]);
sheet.deleteRow(sourceSheet2.range.rowStart);
}