Question

0

393

Visualizações

How to write array elements to a rangelist using Google Apps Script?

I have this rangelist as the destination and a row of values that should be written to the destination. I was thinking of iterating through the row of data and also through th rangelist and set value along the way, but I can't find a way to iterate through the elements in the row of data. Here's the piece of code I'm working on:

function moveItem() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const values = sheet.getRange("A6:D9").getValues();
  let data = [];
  const ref = sheet.getRange('A13').getValue();
  const variation = sheet.getRange('C13').getValue();

  for (let i = 0; i < values.length; i++) {
    if (values[i][0] == ref && values[i][4] == variation) {
      data.push(values[i])
    }
  }

  const destRng = [
    "B17", "B18", "D17", "D18"
  ]

  const rngList = sheet.getRangeList(destRng).getRanges();
  for (let n = 0; n < data.length; n++) {
    rngList[n].setValue(data[n])
  }
}

Here's a view of the flow:

Here's a practical example: https://docs.google.com/spreadsheets/d/1IKr-faDQOk7VBpiTVnxUEMllXbe0ON9-gKwfJii3G0U/edit?usp=sharing

getValues() gets 01 row of data. Each column of this row should go into the destRng as we loop through these columns and through the destination ranges.

Now, this is getting the first element of data and writing it to all destination cells. How can I go through these data elements?

Thank you!

about 4 years ago · Juan Pablo Isaza

2 Respostas

Responde à pergunta

0

The values and the destination range should have the same number of elements, assuming that this is true, there is no need to have nested for loops, a single for loop is enough.

In other words, insted of

for (let n = 0; n < data.length; n++) {
    for (let i = 0; i < rngList.length; i++) {
      let dado = data[n] 
      rngList[i].setValue(dado)//It sets the first value throughout the rngList
    }
  }

use

for (let n = 0; n < data.length; n++) {
  rngList[n].setValue(data[n])
}

about 4 years ago · Juan Pablo Isaza Relatório

Responde à pergunta

Encontrar trabalhos remotos

Accepted Answer · 2022-07-04T17:14:58.299Z

From your additional information, I cannot understand the logic when "A13" and "C13" are "Text1" and "Text1C", "B17:B18" and "D17:D18" are Text1, Text1B and Text1C, Text1D, respectively. I thought that in your sample Spreadsheet, "Variation" of "C12" might be from the column "B". When my understanding is correct, how about the following modification?

Modified script:

From:

for (let i = 0; i < values.length; i++) {
  if (values[i][0] == ref && values[i][4] == variation) {
    data.push(values[i])
  }
}

To:

for (let i = 0; i < values.length; i++) {
  if (values[i][0] == ref && values[i][1] == variation) { // Modified
    data.push(values[i])
  }
}
if (data.length == 0) return;  // Added
data = data[0]; // Added

In this modification, when the cells "A13" and "C13" are "Text1" and "Text1B", respectively, "B17:B18" and "D17:D18" become Text1, Text1B and Text1C, Text1D, respectively.

Note:

In this modification, I used your showing sample Spreadsheet and your script. When your actual situation is different from your showing sample Spreadsheet, this modification might not be able to be used. Please be careful about this.

When setValue is used in a loop, the process cost becomes high. If you want to reduce the process cost in your situation, you can do this using Sheets API as follows. In this case, please enable Sheets API at Advanced Google services.

From

  const rngList = sheet.getRangeList(destRng).getRanges();
  for (let n = 0; n < data.length; n++) {
    rngList[n].setValue(data[n])
  }

To

  const sheetName = sheet.getSheetName();
  const reqs = destRng.map((e, i) => ({ range: `'${sheetName}'!${e}`, values: [[data[i]]] }));
  Sheets.Spreadsheets.Values.batchUpdate({ data: reqs, valueInputOption: "USER_ENTERED" }, ss.getId());

0

393