CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
ffejrekaburb
ffejrekaburb

Reputation: 731

Google Forms needing post processing writing to a Google Sheet

We're using a Google Form which writes to a Google Spreadsheet. I would like to do some post processing of the data as it enters the datasource.

I have the following code started. Is there a way to implicity obtain a reference to the active spreadsheet or do I need to modify it to have a hard reference to the id?

Is it possible to intercept a datastream and modify values before arriving at the Google Sheet?

function onFormSubmit(event) {
  // This function will be called everytime the form is submitted.
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1').getRange(SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1').getLastRow(), 4).setBackground('#4287f5');
}

enter image description here

Upvotes: 0

Views: 286

Answers (1)

Marios
Marios

Reputation: 27350

Solution:

You simply need to take advantage of the event object:

function onFormSubmit(event) {
 const ss = event.source;
 const as = ss.getActiveSheet();
 if (as.getName()=='Form Responses 1'){
    as.getRange(as.getLastRow(),4).setBackground('#4287f5');
 }
}

Please Note:

  • There are two onFormSubmit triggers: one for google sheets and one for google forms. In your case, since you want to edit something in the spreadsheet, it makes more sense to use the first one.

  • Both are installable triggers which means that you need to install it either by a script or manually. To install it manually you can click on project's triggers, create a trigger, select onFormSubmit as the function and event type On form submit.

Upvotes: 1

Related Questions