CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-formsautorun
Sathianarayanan Guandar Raju
Sathianarayanan Guandar Raju

Reputation: 1

How can I auto run script in Google sheet

I have Google Form populated spreadsheet that I added function using script code. Which is auto fill column S using script set formula.

But, after every Form submission, I have to manual run to activate the script.

I want it auto run and update the column S based on column P.

Initially, from 'myFunction', I changed to 'onEdit', but still unable to auto run.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("S3").setFormula('=IFS(P3="Pending Review","Red", P3="Released","Green",P3="In Progress","Yellow",P3="Closed (Unable to link back)","Black")');

  var lr = ss.getLastRow();
  var fillDownRange = ss.getRange(3,19,lr-2);
  ss.getRange("S3").copyTo(fillDownRange);

}

Upvotes: 0

Views: 3013

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

Instead of onEdit use onFormSubmit

This is an installable trigger that you need to bind to your function by

  • Going from the UI to Edit->Current Project triggers
  • -> Create a new trigger
  • Event type: On form submit

enter image description here

This trigger will run your function on every form submit, provided your spreadsheet is set as the destination spreadsheet for your form.

Upvotes: 2

Related Questions