Google Apps Scripting - GAS : Function to replace formula after form submit

Did you ever have this problem ?

When your form receives a new respons, your formula's are being updated and the ranges of the formula doesn't match the correct range anymore ?

Here's a little script that can solve your problem !

This script uses the setFormula(formula) function.

Enjoy !

 

function formulaOnFormSubmit() {
var ss = SpreadsheetApp.getActive();
var formulaB2 = "=arrayformula(someformulahere)"; //formula to enter in cell B2 on the targetsheet

var targetsheet = ss.getSheetByName("YourtargetSheetNamehere");
var range = targetsheet.getRange("B2"); //targetrange where the formula should come
range.setFormula(formulaB2);

SpreadsheetApp.flush(); //apply the changes to the sheet, works a bit like a save button

}

/*
Set up a trigger for this function :

Select event source : From Spreadsheet
Select event type : On Form submit

Choose your Failure notification settings and save.

*/