Google-sheets – Add row to Google Sheets and keep formulas from previous row

google sheetsgoogle-apps-script

I want to add a row to my spreadsheet but keep the formulas – and only the formulas – of the previous row. The #1 solution provided here doesn't help me because it also copies non-formula information, so I tried using .getFormulas() and .setFormulas(), as suggested in solution #2. However, if the last row used, say, some formula containing A36, the new row would still use A36 while it should be using A37. So, how can I keep the formulas from the previous row without this issue? My current code is:

var ss = SpreadsheetApp.getActiveSheet();
var lRow = ss.getLastRow();
var lCol = ss.getLastColumn();
var formulas = ss.getRange(lRow, 1, 1, lCol).getFormulas();
ss.insertRowAfter(lRow);
ss.getRange(lRow + 1, 1, 1, lCol).setFormulas(formulas);

Best Answer

Use getFormulaR1C1 / getFormulasR1C1 and setFormulaR1C1 / setFormulasR1C1

The get methods

Returns the formula (R1C1 notation) for a given cell, or null if none

The set methods

Updates the formula for this range. The given formula must be in R1C1 notation.

Note: On the Google Sheets UI the formulas always are displayed using the A1 Notation.

Related Solutions

Google-sheets – How to Modify Matched Cells Across Multiple Sheets with Google Script

From the question

Could a script be run when the user sets the delete column to Y

Yes, it's possible by using an on edit simple/installable or on change installable triggers.

References

https://developers.google.com/apps-script/guides/triggers

Google-sheets – Create a 7 day running total and running average using ARRAYFORMULA

This can be done by using the running total as an intermediate result (you can put in some column further on the right and/or make it hidden, if you don't want to see it).

I'll use column C for the cumulative total and column D for 7-day total; finding the average is simple division.

The formula for cumulative total is found here. I'll use it in this form

C2 =ARRAYFORMULA(IF(LEN(A2:A), SUMIF(ROW(B2:B),"<="&ROW(B2:B), B2:B), ""))

The conditional statement about LEN(A2:A) prevents output in rows where no data is present.

And here is the computation of 7-day running total:

D2 =ARRAYFORMULA(IF(LEN(A2:A), C2:C-IFERROR(VLOOKUP(A2:A-7, A2:C, 3), 0), ""))

The key player here is the VLOOKUP function which find the latest date that is less than or equal to "a week ago", and returns the cumulative total for it. This represents the total of "old" entries, which should be subtracted from the current total so that only the entries from the last 7 days are counted.

The IFERROR statement handles the case when there is nothing to subtract, i.e., all preceding dates are within the 7-day window.

Here is sample output to verify correctness:

+-----------+------+---------------+-------------+
|   Date    | Data | Running Total | 7-day total |
+-----------+------+---------------+-------------+
| 1/4/2015  |    5 |             5 |           5 |
| 1/5/2015  |    3 |             8 |           8 |
| 1/9/2015  |    1 |             9 |           9 |
| 1/11/2015 |    9 |            18 |          13 |
| 1/12/2015 |    4 |            22 |          14 |
| 1/13/2015 |    2 |            24 |          16 |
| 1/27/2015 |   64 |            88 |          64 |
+-----------+------+---------------+-------------+

Best Answer

Related Solutions

Google-sheets – How to Modify Matched Cells Across Multiple Sheets with Google Script

Google-sheets – Create a 7 day running total and running average using ARRAYFORMULA

Related Topic