App Script: How to set the values of each cell using the .getRangeList?-CodePudding

I am using the .SetValues to attempt to fill every cell I selected through this line var targetSheetRange = targetSheet.getRangeList(arr1);

Unfortunately, when I do it, it always returns me the value of the first cell on all the remaining cells in my Target sheet instead of setting the value of each individual cell from the Source Sheet.

Here's my code:

function filtersCopyData() {

  var dTable = SpreadsheetApp.getActiveSpreadsheet();
  var sSheetDay = dTable.getSheetByName('Day 1'); // Source Sheet
  var sheetRange = sSheetDay.getRangeList(['K3','K4','K5','K6','K7']).getRanges().map(range => range.getValues()); 
  
  var targetSheet = dTable.getSheetByName('All Filters report'); // Target Sheet
  var arr1 = ['B4:C4', 'B6:C6', 'B7:C7', 'B9:C9', 'B10:C10'];
  var targetSheetRange = targetSheet.getRangeList(arr1);

  
  targetSheetRange.setValue(sheetRange); 



}

K3 value is 9, K4 value is 20, K5 value is 10, K6 value is 10, and K7 value is 10.

targetSheetRange.setValue(sheetRange); When this code is run, all the cells in arr1 return the value of 9, instead of copying the value of each cell from the Source Sheet.

I hope this thing that I'm trying to accomplish does make sense on the code, PS. I'm really a beginner. Thank you everyone!

CodePudding user response：

Writing to a rangelist with a rangelist of values

function filtersCopyData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0'); // Source Sheet
  const vs = sh.getRangeList(['K3','K4','K5','K6','K7']).getRanges().map(range => range.getValue()); 
  const tsh = ss.getSheetByName('Sheet1'); 
  const arr1 = ['B4:C4', 'B6:C6', 'B7:C7', 'B9:C9', 'B10:C10'];
  const rgl = breakUpRangeList(ss,tsh,tsh.getRangeList(arr1));
  const l = rgl.getRanges().length;
  rgl.getRanges().forEach((r,i) => {
    let a1 = r.getA1Notation();
    let idx = i % arr1.length;
    r.setValue(vs[idx]);
  });
}

function breakUpRangeList(ss=SpreadsheetApp.getActive(),sh=ss.getSheetByName("Sheet0"),rgl) {
  let b = [];
  rgl.getRanges().forEach(rg => {
    rg.getValues().forEach((r,i) => {
    let row = rg.getRow()   i;
    r.forEach((c, j) => {
      let col = rg.getColumn()   j;
      b.push(sh.getRange(row, col).getA1Notation())
    })
  })
})
  //Logger.log(JSON.stringify(b));
  return sh.getRangeList(b);
}

CodePudding user response：

Description

RangeList is an Array not a Range. I'm suprised your script even ran. You have to use Array.forEach to set the values of the non-contiguous ranges.

You are creating a RangeList and then getting the A1Notation of each range. You can simply define an array of the range A1Notations.

Note, some of the names are different than yours for my test sheet.

Script

function test() {
  try {
    var spread = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spread.getSheetByName("Data");
    var rangeList = ["A1","A3","A5"];
    var values = rangeList.map( range => sheet.getRange(range).getValue() );
    rangeList = ["C1:D1","C3:D3","C5:D5"];
    rangeList.forEach( (range,index) => sheet.getRange(range).setValues([[values[index],values[index]]]));
  }
  catch(err) {
    console.log(err);
  }
}

Reference