Copy rows and paste values to another sheet

2019-06-14 03:44发布

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 to Backup Set Up
  • copy new rows from source in sheet MEL Micro Confirmation to Backup 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);
}

0条回答
登录 后发表回答