How do I update fields based on a Google Forms dropdown value?

Question

We are using Google Forms to collect data from our field reps while they are out at a job site. We use a script to fill drop down values for 4 different fields and that runs periodically. This saves them from having to type it out and possible make mistakes.

All of those fields are pre-determined, so if they select Project #1, we already know the values of address, manager, and client. The values are stored in a second tab of the results form and could theoretically be used as a lookup.

I question whether these fields need to be included at all, but when the user submits the form, the data is then emailed using an add-on and it uses some of those fields in the subject. I suppose the recipients want the info as well.

Can I make my own triggers? If I had a trigger for when the project ID changed, I would think I could update the others from a spreadsheet.

Or if there was a OnNextPage trigger that I could use to fill those values.

Is there anyway to add/change values when the form is submitted?

I think the following is what I want to do (thanks @OMilo), but I am struggling with the lookups:

function onFormSubmit(e) { Logger.log("[METHOD] onFormSubmit");

sendEmail(e.range); }

function sendEmail(range) {

// Here we get choice the users selected in the form: var itemResponse = e.response.getItemResponses()[1557784536]; // Use whatever index is the item, in my case it's 0. var kmbProjectNumber = itemResponse.getResponse();

//LOOKUP KMB PROJECT NUMBER IN SHEET: "New - KMB Design Visit Closeout (Responses)" TAB: "KMB Lookups"
var ss = SpreadsheetApp.OpenByID('REDACTED');
var lookups = ss.getSheetByName("KMB Lookups");

//In Column named'KMB Project ID' find record from bottom where value = kmbProjectNumber
//Get value for column named 'Site Address'
//Get value for column named 'KMB Project Manager'
//Get value for column named 'Client Assigning this Work'

// FETCH SPREADSHEET //
var values = range.getValues();
var row = values[0];

//WRITE FOUND VALUES TO SPREADSHEET
//Set row[3] to Site Address
//Set row[4] to Project Manger
//Set row[5] to Client

var svProjectManager = row[4]; //Update manually  //E
var svClient = row[5]; //Update manually  //F

// EXTRACT VALUES //
var svTimeStamp = row[0];  //A
var svInitials = row[1];  //B
var svProjectID = row[2];  //C
var svSiteAddress = row[3];  //Update manually    //D
var svProjectManager = row[4]; //Update manually  //E
var svClient = row[5]; //Update manually  //F
var svNOCNeeded = row[6];  //G


// PREPARE EMAIL //
var emailRecipients = "REDACTED";
var emailSubject = "Field Visit Completed for "+svProjectManager+" "+svProjectID;
var emailBody = ""  //Format using variables set

// SEND EMAIL //

}

Iamblichus Iamblichus · Accepted Answer · 2019-10-18T12:15:50

You don't need to include these fields in the form itself. Instead, you can set different values depending on which option they choose in the function that is trigged by the onFormSubmit trigger.

I wrote a small sample to show how this can be done.

First, create the onFormSubmit trigger if you haven't done so:

function createTrigger() {
  var form = FormApp.openById('your_form_id'); // Change accordingly
  ScriptApp.newTrigger('getData')
    .forForm(form)
    .onFormSubmit()
    .create();
}

Next, create the function that will be called by the trigger, when the user submits the form:

function getData(e) {
  // Here we get choice the users selected in the form:
  var itemResponse = e.response.getItemResponses()[0]; // Use whatever index is the item, in my case it's 0.
  var response = itemResponse.getResponse();
  var values = getDataFromSheet(); // Getting the values from the sheet
  for(var i = 1; i < values.length; i++) { // Checking which row from the sheet corresponds to the choice selected in the form
    var row = values[i];
    if(row[2] == response) { // Checks if current sheet row is the one selected in the form submission
      var emailData = { // Data for email to be sent is assigned depending on form choice and sheet data
        project: row[2],
        address: row[3],
        manager: row[4],
        client: row[5]
      }
    }
  }
  // Preparing email parameters
  var emailBody = emailData["address"] + " - " + emailData["manager"] + " - " + emailData["client"];
  var emailSubject = "Field Visit Completed for " + emailData["manager"] + " " + emailData["project"];
  var emailRecipient = 'valid email'; // Change accordingly
  // Sending email
  MailApp.sendEmail(emailRecipient, emailSubject, emailBody);
}

In the above function, getDataFromSheet is called:

function getDataFromSheet() {
  var ss = SpreadsheetApp.openById('your_spreadsheet_id'); // Change accordingly
  var lookups = ss.getSheetByName("KMB Lookups");
  var values = lookups.getDataRange().getValues();
  return values;
}

I hope this is useful to you.

How do I update fields based on a Google Forms dropdown value?

1 Answers