How Do I Prevent copyValuesToRange from Overwriting Destination with Blank or Null Data-CodePudding

For the most part, using copyValuesToRange works very well (99.9% of the time) in a function with the following statements:

let source = ss.getSheetByName("Update List");
let destination = ss.getSheetByName("Power Level");
source.getRange('TIER1DescImport').copyValuesToRange(destination, 12, 12, 6, 29); // 'PowerLevel'!L6:L29

The source is on a sheet "Update List" with a named data range "TIER1DescImport". The imported data is from a separate Google Sheet file using IMPORTRANGE.

ONCE, the destination range was entirely overwritten with empty or blank cells. How do I prevent this from happening again? I don't know why this occurred. The source range seemed fine. After closing the Google sheet and opening it 30 minutes later, everything was working properly again.

Is there a way of determining if the source range is okay, and if so, proceed with copyValuesToRange?

I'm a novice using Apps Script. I didn't know how to debug this issue. The only thing that worked was closing the files and coming back to it later. After that, it all seemed to be working again. I don't know why the destination was overwritten with blank or empty data but I'd like to prevent it from happening again.

CodePudding user response：

You can test whether the source range has a meaningful amount of data with something like this:

  const targetSheet = ss.getSheetByName('Power Level');
  const sourceSheet = ss.getSheetByName('Update List');
  const sourceRange = sourceSheet.getRange('TIER1DescImport');
  if (sourceRange.getDisplayValues.join('').length > 10) {
    sourceRange.copyValuesToRange(targetSheet, 12, 12, 6, 29);
    // ...or: sourceRange.copyTo(targetSheet.getRange('L6'));
  }