I'm trying to grab a row of formulas from a sheet and copy them across the remaining rows in the sheet wtih values and update the cell references for the copied formulas to the column A in that row.

To try to accomplish this...

1. I build an array of strings with the formulas to be copied
2. Then I duplicate those arrays into a two dimensional array with the number of rows to be updated
3. Then I search each cell of each row for the appropriate row number to change
4. Then I set all the rows in the range to be updated.

    //sync the formulas in rows with entries in column A
  const lastColumn = auctionListSheet.getLastColumn(); 
  const lastRow2 = auctionListSheet.getLastRow(); //TODO: doesn't only look in col A
    // row to copy across other rows
  const masterRow = auctionListSheet.getRange("B2:" String.fromCharCode(lastColumn   64) "2").getFormulas(); 
    //range to update with properly referrenced formulas to col A of that row
  const changeRange = auctionListSheet.getRange("B3:" String.fromCharCode(lastColumn   64) lastRow2.toString()); 
    // Initialize the formulas array with the same number of rows as the changeRange
    //var formulas = new Array(changeRange.getNumRows()).fill().map(() => masterRow[0]);
    // * tried replacing ^ line with this for loop
  var formulas = new Array(changeRange.getNumRows());

    // Iterate over the rows of the formulas array
  for (var i = 0; i < formulas.length; i  ) {
    // Set the value of the current row to the value of the first element in the masterRow array
    formulas[i] = masterRow[0];
  }
  
  const numColumns = changeRange.getNumColumns();
  const numRows = changeRange.getNumRows();

    //iterate and copy formulas cell by cell
  for (var i = 0; i < formulas.length; i  ) {
    var refRow = "A"   (3   i);
    for (var j = 0; j < formulas[i].length; j  ) {
      var element = formulas[i][j].toString();
      element = element.replace(/A\d/g, refRow);
      formulas[i][j] = element; // should only replace one cell, but updates entire column
    }
  }

  changeRange.setFormulas(formulas);

}

What am I doing wrong?

When I try the code above, each column in every row is changed all at once.

[[ 2, 2, 2],
 [ 2, 2, 2],
 [ 2, 2, 2]]

is supposed to become

[[ 3, 3, 3],
 [ 4, 4, 4],
 [ 5, 5, 5]]

but in row iteration #1 it becomes

[[ 3, 3, 3],
 [ 3, 3, 3],
 [ 3, 3, 3]]

and finally ends up as

[[ 5, 5, 5],
 [ 5, 5, 5],
 [ 5, 5, 5]]

Logging the 2d array at each cell string replacement the entire column of cells is updated each time. So after the first row, first cell replacement the array looks like this:

[[ 3, 2, 2],
 [ 3, 2, 2],
 [ 3, 2, 2]]

I found this answer that said to not use fill() since that points to the same place in memory, but even replacing that with a for loop provides the same behavior. It does feel like I'm working with pointers here.

CodePudding user response：

Arrays are passed by reference.

In your case masterRows is a 2D array of formulas so masterRow[0] is an array ["=form1()","=form2()","=form3()"].

When you set formulas[i] = masterRow[0]; you are passing the reference to an array.

Looking at it another way

formulas[0] = [masterRow[0][0],masterRow[0][1],masterRow[0][2]];
formulas[1] = [masterRow[0][0],masterRow[0][1],masterRow[0][2]];
formulas[2] = [masterRow[0][0],masterRow[0][1],masterRow[0][2]];
formulas[3] = [masterRow[0][0],masterRow[0][1],masterRow[0][2]];

So now when I change formulas[3][0] to a value say 5, I am also changing the value in formulas[0][0] to 5, because they share the same address masterRow[0][0].