Google Sheets/Apps Script Copy Formula to new row

2
votes

still fairly new to this. I have been able to trigger the creation of a new row on a separate tab using a checkbox. On this new tab, there is a column that has formulas in it. When the new row is created (above the first one), I want the formula in the new row. Here is what I have so far:

  if(sheet.getSheetName() == CLIENTS) {
    var checkCell = ss.getActiveCell();
    var checked = checkCell.getValue();
    var clientNameCell = checkCell.offset(0,-8).getValue();
    var clientType = checkCell.offset(0,-7).getValue();
    var sessionChargeCell = checkCell.offset(0, -6).getValue();
    var groupChargeCell = checkCell.offset(0,-5).getValue();
    var softwareFee = checkCell.offset(0,-4).getValue();
    var billFee = checkCell.offset (0,-3).getValue();
    var targetSheet = ss.getSheetByName("Client Billing Projections");
    var lCol = targetSheet.getLastColumn();
    if (checkCell.getColumn() == NEWCLIENTCHECK && checked == true) {
      var target = targetSheet.getRange(2, 1, 1, lCol);
      var formulas = target.getFormulas();
      targetSheet.insertRowBefore(2);
      var newRow = targetSheet.getRange(2,1,2,lCol);
      newRow.setFormulas(formulas);
    }
  }

I guess where I'm stuck is after the line targetSheet.insertRowBefore(2);. I think it has something to do with my use of getFormulas and setFormulas, but I really can't wrap my head around it. There's no syntax error here. As you can see, I've collected values from my first sheet (CLIENTS); I intend to copy it onto this other tab, but I have to get the formula copied up as well. Any help would be appreciated.

Here is an example of the sheet There's a lot of working code in there already. Most of what you'll be looking at here is likely between lines 222 - 238. Everything else works so far.

1
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-formula
In order to correctly understand about your situation, can you provide a sample Spreadsheet and the script for replicating your issue? Of course, please remove your personal information. - Tanaike
Please refer to How to ask to try to improve your question, and please as @Tanaike has said include a sample sheet where there is a replica of your issue. - Raserhin
I added a link to an example. removed information - Stephen Foltz
Thank you for replying and providing the sample Spreadsheet. From it, I proposed a modified script as an answer. Could you please confirm it? If I misunderstood your question and that was not the direction you want, I apologize. - Tanaike

1 Answers

1
votes
  • When the script is run, a new row is inserted to the row 2 in the sheet of Client Billing Projections.
  • You want to put the formula to the column "N" at the inserted row.

If my understanding is correct, how about this modification?

Modification points:

  • In your script, the formulas are retrieved by var formulas = target.getFormulas() from var target = targetSheet.getRange(2, 1, 1, lCol);. This is the 2 dimensional array including one row. And the retrieved formulas are put using newRow.setFormulas(formulas) with var newRow = targetSheet.getRange(2,1,2,lCol). In this case, formulas has 2 dimensional array including 2 rows.
    • I thought that the reason of your issue might be this.

Modified script:

When your script is modified, please modify as follows.

var newRow = targetSheet.getRange(2,1,2,lCol);

var newRow = targetSheet.getRange(2,1,1,lCol);

Reference: