App Script: What's the shorter way to get the range of multiple cells-CodePudding

I am trying to copy the data from the Source sheet to my Target sheet. Is there a version where I can shorten this code? It seems to be that the .getRange can only accept one string.

When I try this line of code const sRange = sSheetDay.getRange('I4', '15', 'I6'); and so on, it always says it can't find the range. I know my questioning is not that good, especially I'm really a beginner at this. Hopefully someone has the same experience.

My main goal to this is to copy the data from a specific cell. And I can't seem to find the solution.

  function copyDataFilters() {
  const dTable = SpreadsheetApp.getActiveSpreadsheet();
  const sSheetDay = dTable.getSheetByName('Day 1');

  const sRange = sSheetDay.getRange('I4');
  const sRange2 = sSheetDay.getRange('I5');
  const sRange3 = sSheetDay.getRange('I6');
  const sRange4 = sSheetDay.getRange('I7');
  const sRange5 = sSheetDay.getRange('I8');
  const sRange6 = sSheetDay.getRange('I9');
  const sRange7 = sSheetDay.getRange('I12');
  const sRange8 = sSheetDay.getRange('I13');
  const sRange9 = sSheetDay.getRange('I16');
  const sRange10 = sSheetDay.getRange('I17');
  const sRange11 = sSheetDay.getRange('I20');
  const sRange12 = sSheetDay.getRange('I21');
  const sRange13 = sSheetDay.getRange('I24');
  const sRange14 = sSheetDay.getRange('I25');
  const sRange15 = sSheetDay.getRange('I27');

  const sValue = sRange.getValues();
  const sValue2 = sRange2.getValues();
  const sValue3 = sRange3.getValues();
  const sValue4 = sRange4.getValues();
  const sValue5 = sRange5.getValues();
  const sValue6 = sRange6.getValues();
  const sValue7 = sRange7.getValues();
  const sValue8 = sRange8.getValues();
  const sValue9 = sRange9.getValues();
  const sValue10 = sRange10.getValues();
  const sValue11 = sRange11.getValues();
  const sValue12 = sRange12.getValues();
  const sValue13 = sRange13.getValues();
  const sValue14 = sRange14.getValues();
  const sValue15 = sRange15.getValues();
  

  const targetSheet = dTable.getSheetByName('All filters report');
  const targetRange = targetSheet.getRange('B4:C4');
  const targetRange2 = targetSheet.getRange('B5:C5');
  const targetRange3 = targetSheet.getRange('B6:C6');
  const targetRange4 = targetSheet.getRange('B7:C7');
  const targetRange5 = targetSheet.getRange('B8:C8');
  const targetRange6 = targetSheet.getRange('B9:C9');
  const targetRange7 = targetSheet.getRange('B11:C11');
  const targetRange8 = targetSheet.getRange('B12:C12');
  const targetRange9 = targetSheet.getRange('B14:C14');
  const targetRange10 = targetSheet.getRange('B15:C15');
  const targetRange11 = targetSheet.getRange('B17:C17');
  const targetRange12 = targetSheet.getRange('B18:C18');
  const targetRange13 = targetSheet.getRange('B20:C20');
  const targetRange14 = targetSheet.getRange('B21:C21');
  const targetRange315 = targetSheet.getRange('B24:C24');

  targetRange.setValue(sValue);
  targetRange2.setValue(sValue2);
  targetRange3.setValue(sValue3);
  targetRange4.setValue(sValue4);
  targetRange5.setValue(sValue5);
  targetRange6.setValue(sValue6);
  targetRange7.setValue(sValue7);
  targetRange8.setValue(sValue8);
  targetRange9.setValue(sValue9);
  targetRange10.setValue(sValue10);
  targetRange11.setValue(sValue11);
  targetRange12.setValue(sValue12);
  targetRange13.setValue(sValue13);
  targetRange14.setValue(sValue14);
  targetRange315.setValue(sValue15);
}

CodePudding user response：

Use getRangeList From the references

// Get a list of ranges A1:D4, F1:H4.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var rangeList  = sheet.getRangeList(['A1:D4', 'F1:H4']);

References

CodePudding user response：

function elfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  //you can do this with values
  const [v4,v5,v6,v7,v8,v9,,,v12,v13,,,v16,v17,v18,v19,v20,v21,,,v24,v25,v26,v27] = sh.getRange("I4:I27").getValues();
  Logger.log(v4);
  //unfortunatey you cannot do this with ranges.  It resullts in an error
  const [I4,I5,I6,I7,I8,I9,,,I12,I13,,,I16,I17,I18,I19,I20,I21,,,I24,I25,I26,I27] = sh.getRange("I4:I27");
  Logger.log(I4.getValue());
}

So were stuck with handling multiple ranges with a rangelist as shown Ruben's answer