CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
SportyJordy
SportyJordy

Reputation: 17

Trigger onEdit() upon insert row

TL;DR: I have a spreadsheet with a running cash total for which I'd like to create a simple script to run to clean up the balance column's formulas, and have that formula is triggered upon the insertion of a new row.

Steps:

  1. In this simplified example sheet, notice that (from cell D3 downward) column D sums the previous balance with the new revenue/cost to calculate the new balance
  2. The user manually inserts a row above row 5
  3. The user enters -$500 cost for the new entry (in cell C5)

Current outcome:

This new line item will not be reflected in the running total, and the EOW balance (cell D11) shows $1,253, rather than the desired $753. So, the user must select cell D4, and autoFill its formula down through D5 and D6, and then the EOW balance will show the desired $753.

The autoFill piece alone can be achieved with a script like:

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().offset(-1, 0).activate();
  var destinationRange = spreadsheet.getActiveRange().offset(0, 0, 3);
  spreadsheet.getActiveRange().autoFill(destinationRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getCurrentCell().offset(0, 0, 3, 1).activate();

Desired outcome:

My request here is to auto-trigger the above script snippet immediately upon the insertion of the row (step 1). I'm guessing the script trigger would entail onEdit() and INSERT_ROW, but would appreciate pointers in how to incorporate it.

Upvotes: 0

Views: 536

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9355

SportyJorday, I would suggest that you don't need script at all. Try this:

1.) Move your $1000 that's currently in cell D2 over to C2. Since you aren't using actual accounting DEBIT/CREDIT (i.e., two-column) accounting, it makes sense to put it there.

2.) Delete D:D, header and all.

3.) Place the following array formula into the now-empty cell D1:

=ArrayFormula({"Balance";IF(B2:B<>"",MMULT(TRANSPOSE((ROW(C2:C)<=TRANSPOSE(ROW(C2:C)))*C2:C),(C2:C)^0),"")})

4.) Format D:D as currency if it isn't already.

In this setup, you can add rows above (or blank rows below) without any issue — and without the need for script.

Upvotes: 2

Related Questions