How to append Google Form responses to Google sheets using Google App Script

Question

I am trying to create a Google sheet that can read and store information from a Google form. I need this program to take the fields when the form submits and append them to my Google sheet. The code below automatically creates a sheet that holds the information from the form.

I am currently using logger to see if they are getting any of the information from the form. They are not.

How can I retrieve each value from the form and then programatically append it to a sheet I created?

function formSubmit(e)
{

  // Function level variables
  var date = e.values[0];
  var name = e.values[1];
  var phone = e.values[2];

  // Get the spread sheet that contains the data
  var ss = SpreadsheetApp.openById('1FpRbYC-
  nfltxoalGV974aDX6A_fEy_MBYBShruRRYfM');
  var sheet = ss.getSheetId(0);

  if(sheet)
  {
    Logger.log(name);
  }
  else
  {
  Logger.log(phone)
  }


  // Add submission to DB
  sheet.appendRow([date,name,phone]);

score 2 · Answer 1 · answered Oct 01 '17 at 23:15

How about this answer?

Modification and confirmation points :

An error occurs at var sheet = ss.getSheetId(0);. When the form is submitted, script below if (sheet) { is not run because of the error. The detail information of getSheetId() is here. If you want to import data to 1st page in Spreadsheet, please use var sheet = ss.getSheets()[0];.
Please confirm whether formSubmit(e) which is your function is installed as a trigger as From Spreadsheet and On form submit. By installing a trigger, you can retrieve the submitted values by your script. You can see the detail information at here.

Modified script :

In your case, if you want to retrieve the values of the check-boxs using e.values, when the question in only one, you can do it using e.values[1].split(", "). When there are several questions, you can retrieve each value using e.namedValues["### question name ###"].

From your script, here, it supposes the former.

function formSubmit(e) {
  var values = e.values[1].split(", ");
  var date = values[0];
  var name = values[1];
  var phone = values[2];
  var ss = SpreadsheetApp.openById('1FpRbYC-nfltxoalGV974aDX6A_fEy_MBYBShruRRYfM');
  var sheet = ss.getSheets()[0];
  if (sheet) {
    Logger.log(name);
  } else {
    Logger.log(phone);
  }
  sheet.appendRow([date,name,phone]);
}

If I misunderstand your question, I'm sorry.

score 0 · Answer 2 · answered Oct 01 '17 at 23:16

Is your event trigger from the form or from the spreadsheet. If it's from the spreadsheet then these are the parameters passed by the event object: authmode, values, range and namedValues. If it's from the form then these are the parameters: authmode, response and source.

How to append Google Form responses to Google sheets using Google App Script

2 Answers2

Modification and confirmation points :

Modified script :