Automatically keep old data loaded via =IMPORTRANGE if source returns only empty fields as new data

user3392296 0
Viewed 47

In Sheet1, data is regularly updated via an =importJSON script. This data is imported into Sheet2 via =IMPORTRANGE("xyz"; "Sheet1!H5:R33"). The data in the range in Sheet1 is cleared via a trigger every 5 minutes completely, but unfortunately this also removes the imported data in Sheet2.

How can I keep the data in Sheet2 even if the data in Sheet1 is deleted? The data in Sheet2 should only change when new data is loaded into Sheet1 via importJSON. But not all fields in the range have data, there are also empty fields as new data

google-sheets google-apps-script
1 Answers

Issue:

Using IMPORTRANGE is a bad idea here, since there's no way to avoid updating the returned data if the source is updated.

Solution:

As an alternative, I'd suggest a script that would use:

  • isBlank to check whether the source range is empty.
  • copyTo to copy the source range values to the destination range.

Code sample:

function importRangeIfNotBlank() {
  const ss = SpreadsheetApp.getActive();
  const sourceRange = ss.getSheetByName("Sheet1").getRange("H5:R33");
  const targetRange = ss.getSheetByName("Sheet2").getRange("A1");
  if (!sourceRange.isBlank()) {
    sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  }
}

Note:

The downside here would be that the function won't execute automatically whenever the source data is updated. Therefore, I'd suggest having a time-driven trigger to fire importRangeIfNotBlank periodically.

Iamblichus0
Related