CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptgoogle-formsgoogle-forms-api
Brent Smith
Brent Smith

Reputation: 635

Script to manipulate Google Form data

I have a Google Form that outputs responses to a Google Sheet. I would like to learn how to write a script that manipulates the data before saving the response to the sheet.

Basically, I have a few form values, among them are "Your Class?" "Your Division?" and "Your Department?". These fields are all optional, except department. However, if someone were to enter a value into division, I want it to overwrite the stored value for department for this submission. Likewise, if someone enters a value for all three I want it to store class as the value for department.

This sounds clunky but is needed for filter views used in the spreadsheet. Any guidance is appreciated.

Upvotes: -1

Views: 109

Answers (1)

Kreeszh
Kreeszh

Reputation: 809

You can't edit the form response, but you can edit the output the form saves to the spreadsheet using an onFormSubmit trigger.

Here's something you can try to get yourself started. Add the script to the Google sheet the form is saving response data. Set it up as an installed onFormSubmit trigger. For this example code, the form has only three questions, class, division, department, adjust accordingly.

function editDepartment(e) {

  const responses = e.namedValues;
  const spreadsheetId = 'your_sheet_id';
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const sheet = spreadsheet.getSheetByName('form_responses_sheetname');
  const lastRow = sheet.getLastRow();

  if(responses['Your Class?'] != '') {

    sheet.getRange(lastRow,4).setValue(responses['Your Class?']);

  } else if(responses['Your Division?'] != '') {
    
    sheet.getRange(lastRow,4).setValue(responses['Your Division?'])

  } else {
    Logger.log('No Response to Class or Division');
  }

}

Upvotes: 2

Related Questions