CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Timogavk
Timogavk

Reputation: 869

How to set values of array to the rows?

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

Upvotes: 1

Views: 186

Answers (2)

Andres Duarte
Andres Duarte

Reputation: 3340

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);
}

Upvotes: 1

TheMaster
TheMaster

Reputation: 50382

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

Upvotes: 1

Related Questions