How to add a new line to google spreadsheet without affecting existing formulas

Question

I am using this Script in a google spreadsheet to add a new line:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
sheet.insertRowAfter(1);

The problem is that any formula referencing rows gets updated, for example

=A$2

becomes

=A$3

after the row insert. Is there a way to avoid this?
(By either changing the forumlas or the script.)

I understand the behaviour, the spreadsheet wants to keep all formulas pointing to the old cells - but it messes with my sums (these should always sum up the whole column from a$2:a$999 (a1 is the header). But those get updated to then sum up a$3:a$1000 (and so on) so they miss the new rows completely.

Answer 1

You can use =SUM(INDIRECT("A2"):INDIRECT("A999")) to sum those values. Spreadsheet will not touch those addresses because they are strings.

Answer 2

I have recently done this in Android.

service = new SpreadsheetService("Spreadsheet");
service.setProtocolVersion(SpreadsheetService.Versions.V3);
service.setUserCredentials("username", "password");

URL metafeedUrl = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full");
feed = service.getFeed(metafeedUrl, SpreadsheetFeed.class);
List<SpreadsheetEntry> spreadsheets = feed.getEntries(); //List of Spreadsheet
worksheetFeed = service.getFeed(spreadsheets.get(0).getWorksheetFeedUrl(), WorksheetFeed.class); //Using the first spreadsheet from list
List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
worksheet = worksheets.get(0);
URL listFeedUrl = worksheet.getListFeedUrl();

// Create a local representation of the new row.
ListEntry row = new ListEntry();
row.getCustomElements().setValueLocal("column_name", "value");
row = service.insert(listFeedUrl, row);

It works well for me. Hope it helps you.