CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mefitico
Mefitico

Reputation: 1116

Insert row and preserve formulas on Google Spreadsheet

I'm making a spreadsheet that updates itself daily. For simplicity, let's say I generate a number every noon, and at night the spreadsheet is supposed to copy the number into a log column, and show the sum of all stored values for this column (there are actually several columns, and their number might grow in the future). With my current implementation, the sum formula is updated automatically, and thus keeps saving only the values previously available. I wish I could prevent this behavior.

Example:

The spreadsheet reads:

Total     1
Today     2
History 
1/29/2014 1

Tomorrow, before I update it, it should be:

Total     3
Today     
History 
1/30/2014 2
1/29/2014 1

The formula I inserted on "Total" line is: 

=sum(B4:B)

Basically, the code I've made for the script contains:

//Inserts a new line to compose history log:
sheet.insertRowsAfter(firstHystoryLine-1, 1);

And after the script is run, the formula is updated to 

=sum(B5:B)

Therefore my problem.

Upvotes: 1

Views: 5542

Answers (3)

Stefan van Aalst
Stefan van Aalst

Reputation: 778

Alternatively, you can add in your code

sheet.getRange("B1").setFormula("=sum(b4:b)")

This will reset your formula correctly.

Upvotes: 1

user3363280
user3363280

Reputation: 1

set your field to be =Sum(B$4). Using the dollar sign should prevent it from sliding. If you were adding in columns as well you'd want to do =Sum($B$4).

Upvotes: 0

Stefan van Aalst
Stefan van Aalst

Reputation: 778

Try Sum(b3:b). Inserting a row should then not affect the formula.

Upvotes: 0

Related Questions