Google form: How do I get form responses items saved in an array and passed to a POST request to a cloud system in JavaScript/ Google App script

Question

I am trying to get google form responses on submitting to be saved in an array rather than a google sheet. I would then require to access about four elements from the responses in array to create a new project via API based on the responses. I have looked online for a code to accomplish this, but the one I got is giving me a challenge in accessing only the elements I need in the post request.

Here is the code accessing the responses on submit.

function formResponse() 
{
    var form = FormApp.openById('form-id');
    var formResponses = form.getResponses();
    for (var i = 0; i < formResponses.length; i++)
    {
      var formResponse = formResponses[i];
      var itemResponses = formResponse.getItemResponses();
      for (var j = 0; j < itemResponses.length; j++) 
      {
        var itemResponse = itemResponses[j];
        Logger.log('Response #%s to the question "%s" was "%s"',
        (i + 1).toString(),
        itemResponse.getItem().getTitle(),
        itemResponse.getResponse());
      }
    }
  return formResponses;
 }

Here a code for the POST request which is supposed to acquire data from the previous function. So basically I would like to get the project name, client name, start and end date of the project on the form submit to be passed directly to the post request.

var data = {
                'name': lastRow[0][2],
                'client': lastRow[0][5],
                'starts_at': lastRow[0][7],
                'ends_at': lastRow[0][8],
                'project_state': "Tentative",

            };
            var payload = JSON.stringify(data);
            var options = {
                'method': 'POST',
                'Content-Type': 'application/json',
                'payload': data,
            };

        }
        var url = TK_URL + 'auth=' + TOKEN
        var response = UrlFetchApp.fetch(url, options);
        if (response.getResponseCode() === 200) {
            var json = JSON.parse(response);
            var id = json["id"];

I will appreciate any help in structuring the code to allow me to get a response directly on submit and pass it to the post request.

I would use the onFormSubmit trigger from the spreadsheet with either values or namedValues — Cooper, May 18 '21 at 17:20
@MetaMan Thank you for your response. With the dynamic nature of the form, I found using spreadsheets challenging to use, due to edits constantly on the form. How do you suggest I structure the namedValue ? — Just, May 18 '21 at 17:26
You can refer to this reference: [form submit events](https://developers.google.com/apps-script/guides/triggers/events#form-submit). If you decide to use `e.namedValues`, you can access the value based on its corresponding name(form's question item). If you used `e.values`, you can access the responses as array with the same order as they appear in the spreadsheet. Additional [reference](https://stackoverflow.com/a/57198281/14606046). Let me know if you still need further assistance — Ron M, May 18 '21 at 18:39
The names for the namedValues are the question titles which actually kind of sucks. Because they don't usually make very easy to use names but since they didn't provide a field name option then were stuck with question titles. — Cooper, May 18 '21 at 19:15

Cooper · Accepted Answer · 2021-05-18T22:16:10.077

This will save everything in the Linked Sheet in an array which will be available anywhere else in the script via PropertiesService under the key of 'myArray';

function onFormSubmit(e) {
  let obj=PropertiesService.getScriptProperties().getProperties();
  obj.myArray=JSON.stringify(e.range.getSheet().getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getDisplayValues());
  PropertiesService.getScriptProperties().setProperties(obj);
}

The only problem I can think of right now is that I've found that the PropertiesService has a length limit of about 30KB so you might have to store it in a file. But either way when you take it out you must parse it before trying to use it as an object.

If you only want to save the current submission then this will suffice.

function onFormSubmit(e) {
  let obj=PropertiesService.getScriptProperties().getProperties();
  obj.myArray=JSON.stringify(e.values);
  PropertiesService.getScriptProperties().setProperties(obj);
}

Google form: How do I get form responses items saved in an array and passed to a POST request to a cloud system in JavaScript/ Google App script

1 Answers1