As part of a bigger script I'm using the following to append data to my sheet
scSsOutputData.forEach(x =>
{scSh.appendRow(x);
scSh.getRange(scSh.getLastRow(), headers.ADJUSTMENTS+1).setValue("FORMULA HERE")});
The appendRow is working fine but the next line is behaving weird. On the first iteration it does nothing then on the second it puts the value in the row above.
Issues:
Your current code appends a value to a new row by using appendRow(x) and then it sets the value of another cell in the same row using setValue. This is a very computational expensive method given also the fact that is inside a for loop. Maybe a SpreadsheetApp.flush() between the two operations would solve the issue, although your goal is to optimize it even better.
It is a good practice to avoid using appendRow() inside a for loop.
If you want to create a new row, use insertRowsAfter(scSh.getLastRow(), scSsOutputData.length) to create scSsOutputData.length number of rows after the last row with content and then use getRange().setValues() to insert the desired values.
First solution:
scSsOutputData.forEach(x =>
{
scSh.appendRow(x);
SpreadsheetApp.flush();
scSh.getRange(scSh.getLastRow(), headers.ADJUSTMENTS+1).setValue("FORMULA HERE");
});
Second solution (recommended):
Transform scSsOutputData to a format like that:
[
[x,,,"FORMULA HERE"],
[x,,,"FORMULA HERE"],
[x,,,"FORMULA HERE"],
.....
]
the commas between x and FORMULA HERE indicate the columns that you want to skip when pasting values. In the above example you skip 2 columns.
Therefore you could use:
scSh.insertRowsAfter(scSh.getLastRow(), scSsOutputData.length);
scSh.getRange(scSh.getLastRow()+1, 1, 1, 2+columns you want to skip in between).setValues(scSsOutputData);
In the second solution you use only two operations. In the first solution you use two operations per scSsOutputData element.