Google-sheets – Importrange when copied to other cells automatically change range

You can clear only the cells with orange background (color code #ff9900) by getting the background colors of all cells with .getBackgrounds() and then checking them for being orange.

function clearOrange() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Blad1');
  var range = sheet.getDataRange();
  var bgColors = range.getBackgrounds();
  for (var i=0; i<bgColors.length; i++) {
    for (var j=0; j<bgColors[i].length; j++) {
      if (bgColors[i][j] === '#ff9900') {
        range.getCell(i+1,j+1).clearContent();
      }
    }
  }  
}

I knew the color code for orange used in Google sheets because I first ran this code with the "if" statement replaced by Logger.log(bgColors[i][j]);

The reason for (i+1,j+1) is that indices in JavaScript are 0-based while row and column numbers are 1-based.

Hypothetically speaking, if you didn't have orange background as the criterion for clearing, you'd have to specify the ranges explicitly; the script has to know what to clear. You could still shorten the script by putting the ranges in an array, as below.

function clear1() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Blad1');
  var rangesToClear = ['D5:D12', 'D15:D20', 'D22:D30'];
  for (var i=0; i<rangesToClear.length; i++) { 
    sheet.getRange(rangesToClear[i]).clearContent();
  }
}

Wrap each individual IMPORTRANGE call in IFERROR with a null array as the default length of a filled row if there is an error, like this:

=QUERY({ IFERROR(Importrange(G3,"Sheet1!A2:D"),{"","","",""}); IFERROR(Importrange(G4,"Sheet1!A2:D"),{"","","",""}); IFERROR(Importrange(G5,"Sheet1!A2:D"),{"","","",""}); IFERROR(Importrange(G6,"Sheet1!A2:D"),{"","","",""}); IFERROR(Importrange(G7,"Sheet1!A2:D"),{"","","",""}) },"Select Col1, Col2, Col3, Col4 Where Col3 Is Not Null And Col4 Is Not Null")