How to set values of array to the rows?

Question

I don't understand how to set values of array to the target range of rows after .map in Google Sheets.

function chekItNow() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const arrVals = sheet.getRange(1, 1, 7, 4).getValues();
  Logger.log(arrVals);
  for (let i = 0; i < 6; i++) {
    const nextVals = arrVals[i].map(x => x * 5);
    Logger.log(nextVals);
    let targetRange = sheet.getRange(1, 5, 7, 4);
    targetRange.setValues(nextVals);
  }
}

I'm new in JS. Unfortunately, can't find info about it

score 1 · Accepted Answer · answered Mar 04 '20 at 11:07

1

You need to map both the outer and inner arrays:

const rg = sheet.getRange(1, 1, 7, 4);
const arrVals = rg.getValues();
rg.setValues(arrVals.map(row => row.map(col => col*5)))

Also see related answer

answered Mar 04 '20 at 11:07

TheMaster

45,448
6
62
85

score 1 · Answer 2 · answered Mar 05 '20 at 14:23

setValues() method expects a 2D array as parameter, in which the outer array dimension is for rows and the inner dimension for columns. Before setting the values, you first need to fill the nextVals array within the for loop with each row array, which are the ones you're getting from the .map function in each iteration. I tested the following code and worked successfully:

function chekItNow() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const arrVals = sheet.getRange(1, 1, 7, 4).getValues();
  let nextVals=[];

  Logger.log(arrVals);
  for (let i = 0; i < arrVals.length; i++) {
    nextVals.push(arrVals[i].map(x => x * 5));
  }

  Logger.log(nextVals);
  let targetRange = sheet.getRange(1, 5, 7, 4);
  targetRange.setValues(nextVals);
}

How to set values of array to the rows?

2 Answers2