CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-forms
Teed Ferguson
Teed Ferguson

Reputation: 317

setFormula() in a FormResponse Sheet

I am using GAS to generate a Google Form via Google Sheets. After the Form is generated and the "Responses" tab is added to the Sheet, the script adds a formula to cell E1 that is just supposed to copy the Timestamp from the A column to the E column. It looks like this:

ss.getSheets()[0].getRange("E1").setFormula("={\"Timestamp Moved\"; ARRAYFORMULA(IF($A$2:$A<>\"\",$A$2:$A,\"\"))}");

And shows this in the cell: ={"Timestamp Moved"; ARRAYFORMULA(IF($A$2:$A<>"",$A$2:$A,""))}

The formula drops into place just fine and looks right when I go to the Sheet and click on E1. However, whenever the Form is submitted, the "A$2:$A" moves down one row. So after the first submission, the formula in E1 changes to:

={"Timestamp Moved"; ARRAYFORMULA(IF($A$3:$A<>"",$A$3:$A,""))}

And so on. However, if I go into E1 and manually type the formula ={"Timestamp Moved"; ARRAYFORMULA(IF($A$2:$A<>"",$A$2:$A,""))} it works just fine. The A2 will stay A2 across all submissions.

The setFormula function is only running once, so it isn't like the GAS script is updating it. Any ideas why it is moving itself down?

EDIT To minimally reproduce this, open GAS from within a Google Sheet and add this script:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets()[0].getRange("C1").setFormula("={\"Timestamp Moved\"; ARRAYFORMULA(IF($A$2:$A<>\"\",$A$2:$A,\"\"))}");
}

Next, from within the Sheet, select "Tools" and "Create a new form". Make a one question Form, then run the script. Go to the Responses tab and you should see this: 1

Then, fill out the Form once and you will now see this: 2

The timestamp is not moved and the formula shifts.

Upvotes: 0

Views: 83

Answers (1)

idfurw
idfurw

Reputation: 5852

Google form response is inserted right after the row of the last response.

So the range changes from A2:A tp A3:A.

You should either add the formula after there is a response in row 2,

or use INDIRECT("A2:A") instead of A2:A.

Upvotes: 1

Related Questions