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

Read Time: 1 min

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.

*/
Google Sheets
GAS - Google Apps Script
Google Forms

Related Articles

Google Forms

A Google App Script function to find all the item ID's of a Google Form

Here's a GAS script to find all the ID's of a Google Form.

The ID's are written to a spreadsheet.

This is a rewrite of this script t

function formQuestionID(){

var ss = SpreadsheetApp.getActive();
var config = ss.getSheetByName("ConfigFile");
var form = FormApp.openByUrl(
    'https://docs.google.com/forms/d/YOUR FORM ID/edit'
    );
var items = form.getItems();

var list = [];
  list.push(['Name','ID']);
  
for (var i in items) { 

  var row = []
    row.push(items[i].getTitle(),items[i].getId())
    list.push(row);
  //Logger.log(items[i].getTitle() + ': ' + items[i].getId());
 
}
config.getRange(1,1,list.length,list[0].length).setValues(list);
}
Free Scripts

Order your sheets in a SpreadSheet alphabetically using GAS

Little script to organise sheets alphabetically in a Google Spreadsheet.

Source

Free Scripts

Keep track of how often a (public) sheet has been opened using google app script

Little script to keep track on how often a (demo) sheet has been opened.

 

Free Scripts

COVID-19 registration form : automatically delete responses to Google Forms using Google App Script

 

Are you using a  Google Form to collect user data ?

Do you need to remove responses based on the date the response was made ?

EG : a COVID-19 contact tracing form, where customers leave there contact details and you need to (automatically) remove the data older then 14 days ?

Then you can use this script.