Taking data from multiple Google Forms to put into the appropriate Spreadsheet

Question

I have multiple Google Forms set up to take in data (just numbers). That data is then grabbed using an onFormSubmit function with a manually programmed trigger. Is there a way I can use just one trigger for multiple forms and have them go to the appropriate spreadsheet?

For example, I have a form called dry storage and another called freezer (and have 6 other forms and sheets), with corresponding sheets in a spreadsheet. Is there a way that when the form is submitted, that it takes the data, and places it in the correct spreadsheet? I have it working when I manually type in the sheet name, but I would like it to be done dynamically in case more sheets and forms need to be added so a new function and trigger doesn't have to be created every time.

The forms created have already been linked to the spreadsheet. I basically want every linked form submitted to get the formID of the form it was submitted, and be able to match it to the corresponding sheet name.

In a perfect world, the spreadsheet is determined by the form it is submitted on by name. Is it possible to do this dynamically?

I would like to not have 8 triggers with 8 functions that all do the exact same thing, with the only difference is the formID and corresponding sheet being passed.

The onSubmit function does exactly what I want it to do for one form and one spreadsheet, but I would like it to be able to take in any form and map it to the correct sheet.

The onFormSubmit function was me attempting to use events to generalize it, but with no avail, as I am unfamilar with how events work. I've searched high and low for an example that matches what I am trying to do on the Google website, and StackOverflow. Any examples or points on how events work would be beneficial as well.

Thank you!

function onFormSubmit(e) 
{
  var res = e.values // get the data from the form

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dry Storage"); // manually get the sheet it should be going to, I'd like this to be dynamic
  for (var a = 0; a < res.length; a++)
  {
    sheet.getRange(7,(a+1)).setValue(e.values[a]) // set the form values in the sheet
    
  }

function trigger() 
{ 
  var form = FormApp.openById(DryStorageFormID) // formID for dry storage. is there a way that this ID can be grabbed dynaically from the submitted form?
  ScriptApp.newTrigger('onSubmit')
  .forForm(form)
  .onFormSubmit()
  .create();
}

function onSubmit()
{
var ss = SpreadsheetApp.getActiveSpreadsheet(); // get active spreadsheet
  var targetSheet = ss.getSheetByName("Dry Storage"); // data should be sent to this sheet. I would like this info to be somehow passed to the function instead of have it hard-coded.
  var form = FormApp.openById(DryStorageFormID); // DryFormID 
  try{
  var formResponses = form.getResponses();
  }
  catch(e)
  {
    console.error("No Data!");
  }
  var itemResponses = formResponses[formResponses.length - 1].getItemResponses(); 

  for (var j = 0; j < itemResponses.length; j++) 
  {
    var itemResponse = itemResponses[j];
    var offset = j + 2
      targetSheet.getRange(offset, (FR.order +1)).setValue(itemResponse.getResponse())
    
  }

}

You could link multiple forms to a single spreadsheet and you can tell which sheet was edited and thus which form the submission came by the e.range.getSheet()/ — Cooper
onFormSubmit is not the way if you want a single trigger. What exact actions should be done for each form submission? If you have already linked the forms with the spreadsheet, each form will already have its own sheet, where form submission data will be written. What do you want to do with this data? — Iamblichus
@Iamblichus I know it creates its own sheet but I need the data submitted to be sent to a custom sheet with other information that I created. The actions from each form submit should take all data in the submission, and place that information in the custom sheet in the appropriate spot. I already have that working. My issue is figuring out how to allow any form submitted to parse the data submitted, and send it to the correct sheet. I have to manually hardcode the sheetname and formID into the onsubmit function for this to work currently. I would like the function to find this info dynamically — Ryan B
You cannot pass custom parameters to the triggered function when creating the trigger, so there's no way to pass the sheet name through that. If the Form or the FormResponse (returned by the event object) contain information on the sheet name though, you could retrieve that. Also, I don't know if you're doing this already, but you could programmatically create the different triggers, without having to write 8 trigger functions (you'd just have to define and array with the different formId and loop through it. — Iamblichus
Finally, you could use a time-based trigger to update the different destination sheets based on the response sheets every minute. Do you think that workaround would appropriate for your situation? — Iamblichus

Rubén Rubén · Accepted Answer · 2020-06-26T19:23:29

The easier way to proceed, instead of having one spreadsheets for each form, is to have one spreadsheet that receive responses from multiple forms.

Besides the above, you could send the received responses to another spreadsheet using the same on form submit trigger or by other means (i.e. IMPORTRANGE).

To get the sheet receiving the form response you could use the following code line (assuming that e is the spreadsheet on form submit object):

var sheet = e.range.getSheet();

then you could use sheet.getName() to get the sheet name or whatever else you need of that sheet.

To get the "name" of the form that triggered the on form submit trigger you could use:

var url = sheet.getFormUrl();
var form = FormApp.openFormByUrl(url);
var title = form.getTitle();

Taking data from multiple Google Forms to put into the appropriate Spreadsheet

1 Answers