Linearize a BOM, issue when using setValues(result) instead of appendRow-CodePudding

Input is as follows (this is a simplified example)

LEVEL	NAME	JOB
1	A	Alpha
2	B	Bravo
3	C	Charlie
4	D	Delta
2	E	Echo
3	F	Foxtrot
2	G	Golf
2	H	Hotel
3	I	India
4	J	Juliet

I have to linearize to obtain that output

NAME level 1	JOB level 1	NAME level 2	JOB level 2	NAME level 3	JOB level 3	NAME level 4	JOB level 4
A	Alpha	B	Bravo	C	Charlie	D	Delta
A	Alpha	E	Echo	F	Foxtrot
A	Alpha	G	Golf
A	Alpha	H	Hotel	I	India	J	Juliet

I achive that by using a temporary array (temp) and appendRow, which is a bit slow. When I manage to use a big array (result) and setValues(result), I only get the last row

NAME level 1	JOB level 1	NAME level 2	JOB level 2	NAME level 3	JOB level 3	NAME level 4	JOB level 4
A	Alpha	H	Hotel	I	India	J	Juliet
A	Alpha	H	Hotel	I	India	J	Juliet
A	Alpha	H	Hotel	I	India	J	Juliet
A	Alpha	H	Hotel	I	India	J	Juliet

I can't understand what is wrong in my script ! Any help to understand will be usefull.

https://docs.google.com/spreadsheets/d/1zoT9kk-Am_yUOLCAAvccJOTH0UZ7lrRiLYpPtqb9RXY/copy

function linearize() {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Original')
  const data = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getDisplayValues()
  const nbData = sh.getLastColumn() - 1

  const bd1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BD1') // for test with appendrow
  const bd2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BD2') // for test with result
  bd1.clearContents()
  bd2.clearContents()

  let result = []
  let levelMax = 0
  let headers = []
  data.forEach(r => levelMax = Math.max(levelMax, r[0]))
  for (let i = 1; i <= levelMax; i  ) {
    headers.push(['NAME level '   i, 'JOB level '   i])
  }
  bd1.appendRow(headers.flat())
  result.push(headers.flat())
  // everything ok until this step ==============
  let temp = []
  data.forEach(function (r, i) {
    // save values
    var level = r[0]
    for (let x = 0; x < nbData; x  ) {
      temp[nbData * (level - 1)   x] = r[x   1]
    }
    // blank values from level 1 to levelMax
    if (level < levelMax) {
      for (let y = (level * 1   1); y <= levelMax; y  ) {
        for (let x = 0; x < nbData; x  ) {
          temp[nbData * (y - 1)   x] = ''
        }
      }
    }
    // output when the following level will not increase or at the final row
    if (i < data.length - 1) {
      if (data[i   1][0] <= data[i][0]) {
        bd1.appendRow(temp)
        result.push(temp)
      }
    }
    else {
      bd1.appendRow(temp)
      result.push(temp)
    }

  })
  bd2.getRange(1, 1, result.length, result[0].length).setValues(result)
}

CodePudding user response：

I believe your goal is as follows.

By modifying your script, you want to achieve the situation of I have to linearize to obtain that output using bd2.getRange(1, 1, result.length, result[0].length).setValues(result).

In this case, how about the following modification?

From:

if (i < data.length - 1) {
  if (data[i   1][0] <= data[i][0]) {
    bd1.appendRow(temp)
    result.push(temp)
  }
}

To:

if (i < data.length - 1) {
  if (data[i   1][0] <= data[i][0]) {
    bd1.appendRow(temp);
    result.push([...temp]); // <--- Modified. Or result.push(temp.slice())
  }
}

I thought that in your script, temp is used as the pass-by-reference. By this, the issue of I only get the last row occurs. I thought that this might be the reason for your issue. So, in this case, temp is copied with [...temp] and/or temp.slice(). By this, it becomes the pass-by-value.