2

I have figured out how to run a Google App Script project/function on a form submit using the information at https://developers.google.com/apps-script/guides/triggers/events#form-submit_4.

Once I have e I can call e.response to get a FormResponse object and then call getItemResponses() to get an array of all of the responses.

Without iterating through the array and checking each one, is there a way to find the ItemResponse for a specific question?

I see getResponseForItem(item) but it looks like I have to somehow create an Item first?

Can I some how use e.source to get the Form object and then find the Item by question, without iterating through all of them, so I could get the Item object I can use with getResponseForItem(item)?

IMTheNachoMan
  • 5,343
  • 5
  • 40
  • 89
  • I had but wasn't sure what the relationship is with a Form. I want to do something on Form submit. Isn't a sheet form submit different? – IMTheNachoMan May 11 '19 at 20:38
  • 1
    So, I am confused by what you are asking for. Are you trying to get the most recent response for an item easily, or all the responses for an item? – Hink May 11 '19 at 20:39
  • @Hink specific answers/responses for the current submission. Say a form has 5 questions, when the user submits I want to get the answers to specific questions (by question) on form submit. – IMTheNachoMan May 11 '19 at 20:41
  • @Cooper it takes me to the same page/area I posted in the question. Not sure I am understand what you're wanting me to see. – IMTheNachoMan May 11 '19 at 20:43
  • 1
    Try this one https://developers.google.com/apps-script/guides/triggers/events#form-submit – Cooper May 11 '19 at 20:45
  • @Cooper humm. So I'll need to attach the GAS script to the Sheet form submit event instead of the Form form submit event? I will try that as soon as I'm back at my desk. – IMTheNachoMan May 11 '19 at 20:51
  • 1
    It's not 100% what you are asking for, but I have a code snippet I use to pull the current set of answers into a object, so the most current response for the question Your Name becomes form.yourName which I found to be the easiest way to find responses by question – Hink May 11 '19 at 20:59
  • 1
    e.values is an array with all of the answers to all of the questions. – Cooper May 11 '19 at 21:06
  • 1
    e.namedValues values is an object where the properties are the questions and the values are the answers. It's pretty easy to use. You may want to look at [this question](https://stackoverflow.com/questions/54834837/how-can-i-be-getting-multiple-unwanted-event-blocks-from-the-same-onformsubmit-t) though because we've been seeing problems of getting more that one trigger from one submit event. – Cooper May 11 '19 at 21:09
  • 1
    The value of e.range can be used to determined which linked sheet is receiving on any given onFormSubmit event. This makes it possible to handle more than one form on a given spreadsheet. `var sheetname=e.range.getSheet().getName()` – Cooper May 11 '19 at 21:16
  • @Cooper: Thanks. So I still need to use the Form on submit because I needed to be able to get the `FormResponse` object so I can get `FormResponse.toPrefilledUrl()`. And I can't find a way to reverse into the `FormResponse` from a Sheet on submit. I thought I could use `e.triggerUid` on the Sheet on submit to find the `FormResponse` but no dice. – IMTheNachoMan May 16 '19 at 01:43
  • @Hink That is pretty much what I ended up doing. How did you do it? Mind sharing your code? I just built a function that iterates `FormResponse.getItemResponses()` in Form on submit. – IMTheNachoMan May 16 '19 at 01:44

1 Answers1

2

This is the code I use to pull the current set of answers into a object, so the most current response for the question Your Name becomes form.yourName which I found to be the easiest way to find responses by question:

function objectifyForm() {
  //Makes the form info into an object
  var myform = FormApp.getActiveForm();
  var formResponses = myform.getResponses()
  var currentResponse = formResponses[formResponses.length-1];
  var responseArray = currentResponse.getItemResponses()
  var form = {};
  form.user = currentResponse.getRespondentEmail(); //requires collect email addresses to be turned on or is undefined.
  form.timestamp = currentResponse.getTimestamp();
  form.formName = myform.getTitle();
  for (var i = 0; i < responseArray.length; i++){
    var response = responseArray[i].getResponse();
    var item = responseArray[i].getItem().getTitle();
    var item = camelize(item);
    form[item] = response;
  }
  return form;
}

function camelize(str) {
  str = str.replace(/[\.,-\/#!$%\^&\*;:{}=\-_`~()@\+\?><\[\]\+]/g, '')
  return str.replace(/(?:^\w|[A-Z]|\b\w|\s+)/g, function(match, index) {
    if (+match === 0) return ""; // or if (/\s+/.test(match)) for white spaces
    return index == 0 ? match.toLowerCase() : match.toUpperCase();
  });
}


//Use with installable trigger
function onSubmittedForm() {
  var form = objectifyForm();
  Logger.log(form);
  //Put Code here

}

A couple of important things.

  • If you change the question on the form, you will need to update your code
  • Non required questions may or may not have answers, so check if answer exists before you use it
  • I only use installable triggers, so I know it works with those. Not sure about with simple triggers
  • You can see the form object by opening the logs, which is useful for finding the object names
Hink
  • 2,271
  • 1
  • 11
  • 8