Question

0

169

Views

Storing all forms answers in one single file

I am trying to store every response from the forms submited by users in AppScript. I am using this code, but I get the error:

'TypeError: id_form.getResponses is not a function'

My code:

//store answers in one file

EDIT:

function setDestination_responses(form) {
  var id_form = FormApp.openById(form.getId());
  var spreadsheet_responses = SpreadsheetApp.openById('1TX4GOsihKuXFhbZMhtFWPYmGwrxii0XhyGhyTKzPKhc');

  var sheet_spreadsheet_responses = spreadsheet_responses.getSheets()[0];
  var titles = id_form.getItems().map(e => e.getTitle());
  var formResponses = id_form.getResponses();
  var values = formResponses.map(f => f.getItemResponses().reduce((o, i) => {
    var r = i.getResponse();
    return Object.assign(o, {[i.getItem().getTitle()]: Array.isArray(r) ? r.join(",") : r});
  }, {})).map(o => titles.map(t => o[t] || ""));

  if (values.length==0) {console.log(`VALUES EQUAL TO 0 ${values}`)} 
  else{sheet_spreadsheet_responses.getRange(sheet_spreadsheet_responses.getLastRow() + 1, 1, values.length, values[0].length).setValues(values)};
}

about 4 years ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-11T12:34:58.514Z

Modification points:

getResponses() returns FormResponse object. In your situation, I think that it is required to retrieve the response values using getItemResponses().
When appendRow is used in a loop, the process cost will become high. Ref

When these points are reflected to your script, it becomes as follows.

Modified script:

function myFunction() {
  var id_form = FormApp.openById(form.getId());
  var spreadsheet_responses = SpreadsheetApp.openById('1X1rhvgTfFq0-SoecLcgDP1Zu8x93Vba0EKMTBkRFJcw');
  var sheet_spreadsheet_responses = spreadsheet_responses.getSheets()[0];
  var titles = id_form.getItems().map(e => e.getTitle());
  var formResponses = id_form.getResponses();
  var values = formResponses.map(f => f.getItemResponses().reduce((o, i) => {
    var r = i.getResponse();
    return Object.assign(o, {[i.getItem().getTitle()]: Array.isArray(r) ? r.join(",") : r});
  }, {})).map(o => titles.map(t => o[t] || ""));
  sheet_spreadsheet_responses.getRange(sheet_spreadsheet_responses.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}

When you run this script, all responses are retrieved from the Google Form and they are appended to the 1st sheet of the Spreadsheet.
About var id_form = FormApp.openById(form.getId());, in your script, form might be the same with FormApp.openById(form.getId()).
At Google Form, when the empty answer is submitted, the question has no value. By this, it is required to consider this. So at first, the titles are retrieved from the items, and the values are created using the item titles. I thought that this might be an important point.

0

169

Storing all forms answers in one single file

1 answers

0

Modification points:

Modified script:

References:

Find remote jobs

Andres GPT