Column automatically increasing

Question

I am setting a formula via my script such as this:

sheet.getRange("I1").setFormula("=CONCATENATE(\"CHECKED IN: \", SUM(I2:I))");

When I look at the field, I see the following: =CONCATENATE("CHECKED IN: ", SUM(I2:I))

Then somebody registers via my google form and I look at the value, it becomes this: =CONCATENATE("CHECKED IN: ", SUM(I3:I))

Basically, 2 became a 3. I assume this is because a new row was added to the sheet, however, my question is how can I prevent this? I want to count the sum in all the rows in that column without my formula getting adjusted by the spreadsheet.

Note: If I go and change 3 back to a 2 and then register again then it works. However, I would like to eliminate this manual step altogether.

EDIT:

To replicate follow these steps:

Create a new google form
Select Destination to a spreadsheet
Create a new Script from the Form.
Create the following script

Script Code:

var form = FormApp.getActiveForm();
var ss = SpreadsheetApp.openById(form.getDestinationId()); 
form.deleteAllResponses();
form.removeDestination();
form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());
form = FormApp.getActiveForm();

//Code below is to resolve some bugs I've seen happen that prevented people from using the signup for the second time after I would reset it.
SpreadsheetApp.flush();
ss = SpreadsheetApp.openById(form.getDestinationId());
var sheet = ss.getSheets()[0];
sheet.getRange(CHECKIN_COLUMN).setFormula("=CONCATENATE(\"CHECKED IN: \", SUM(I:I))");
sheet.getRange(VERIFIED_COLUMN).setFormula("=CONCATENATE(\"CHECKED IN: \", SUM(J:J))");
form.setAcceptingResponses(true);

Run the above code
Go look at the value of column I and J
Register through the form
Look at the value again.

Ritesh Nair Ritesh Nair · Accepted Answer · 2017-07-23T20:24:23

If you want to fix the range then add $ to your formula.

sheet.getRange(CHECKIN_COLUMN).setFormula("=CONCATENATE(\"CHECKED IN: \", SUM($I$2:I))");

Edit 2: To remove circular dependency

On the top menu of a google spreadsheet do the following:

File > Spreadsheet settings

Choose the "Calculation" tab, and change "Iterative calculation" to ON.

Also choose the entire range I:I

sheet.getRange(CHECKIN_COLUMN).setFormula("=CONCATENATE(\"CHECKED IN: \", SUM(I:I))");

Column automatically increasing

1 Answers