Apps Script "setFormulas" in Google Sheet: how to set the correct row in reference to othe-CodePudding

I have a Google spreadsheet for which I've created an Apps Script to insert a row. It works great when the cells are within the same sheet. However, when the cell reference is to another sheet in the same workbook, it doesn't update the reference to the correct row.

So, when I run the Apps Script, it copies Sheet2!B2 from Sheet1's cell B2, inserts a new row (moving row 2 to row 3), then pastes Sheet2!B3 into the new row's cell instead of Sheet2!B2.

Also, when the new row is inserted into Sheet2, its reference to Sheet1's date field (in column A) is set to the literal value of the date instead of a reference to Sheet1!A2. Why?

  var objRow = 2;
  var numRows = 1;
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i = 0; i < sheets.length ; i   ) {
    var sheet = sheets[i];
    var lCol = sheet.getLastColumn();                       // store the last column
    var range = sheet.getRange(objRow, 1, numRows, lCol);   // store the range to copy from
    var formulas = range.getFormulas();                     // store the formulas to copy from
    sheet.insertRowsBefore(objRow, 1);                      // insert a new row above the new row
    var newRange = sheet.getRange(objRow, 1, 1, lCol);      // store the location of the new row
    range.copyTo(newRange);                                 // copy saved row data to the new row
    newRange.setFormulas(formulas);                         // set the formulas on the new row
  }

How do I get the correct value into the cell?

CodePudding user response：

I don't know exactly how that works, but maybe you can solve it with an ArrayFormula in B1 like this:

=ARRAYFORMULA(IF(row(B:B)=1,"Sheet 5",Sheet2!B:B))

Since it's in the headers, it will not be affected by the new appended rows

You can do the same with the dates in Sheet2!A1:

=ARRAYFORMULA(IF(row(A:A)=1,"Date",Sheet1!A:A))

If you want to continue with the code, I suggest you just create the new row in a first step, and then copy the third row in a second step. I think that's the issue with your system. When it creates the row in the second sheet, the formulas in the first one are misplaced:

function insertRows() {
  var objRow = 2;                               // store row 2 (numbering starts at 1)
  var numRows = 1;  const now = new Date();
  var dateToday = Utilities.formatDate(now, 'America/New_York', 'MM/dd/yyyy');

  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i = 0; i < sheets.length ; i   ) {
    var sheet = sheets[i];
    sheet.insertRowBefore(2)
  }
   for (var i = 0; i < sheets.length ; i   ) {
    var sheet = sheets[i];
    sheet.getRange(objRow 1,1,1,2).copyTo(sheet.getRange(objRow,1,1,2))
    sheet.getRange(objRow, 1).setValue(dateToday);
  }
}