I have a finance tracking worksheet that has a number of formulas for each row. What I'd like to add is have these formulas replicate whenever a new row is inserted (fill it with a "default value").
Sheet before adding the row:
Sheet after adding the row:
I'd like some of these cells (B28 in this case) to contain a formula. for this row the formula is:
=IF(ISBLANK(C27), "", CONCATENATE(YEAR(C27), "-", MONTH(C27)))
You might want to consider an ArrayFormula. Here's a place for getting detail. In your case, the big trick is knowing that you can run the formula through all of Column C using the notation something like C1:C. Doing it this way is far more self documenting than burying it in a Google function and it's way more general.
Because the formula is the same for all rows, I added a function that just does the following:
function addRow() {
SpreadsheetApp.getActiveSheet().getRange('B7:B').setValue('=IF(ISBLANK(C7), "", CONCATENATE(YEAR(C7), "-", MONTH(C7)))');
}
I set this function to run on a trigger whenever the sheet is edited. The row numbers increment themselves so not problem there either.
Used this SO question for help: Default cell values when Inserting new row in Google Sheets
delete everything in your B column and paste this into B1 cell:
={"Date", ARRAYFORMULA(IF(C2:C<>"", YEAR(C2:C)&"-"&MONTH(C2:C)))}