Saturday, September 26, 2015

Adding additinal data into each Google Form response when submitting using Google Apps Script

This was another Google Form challenge I got this week. The school want to add the department code into the user's response right after she/he submits her/his course selection from the Google Form. For example, if the user select course ABC and submit the form, the responses spreadsheet will have a new line with the department code filled in automatically:


What I had done to get it done are:

1. Like the previous Google Form challenge, I had to prepare a google spreadsheet contains all the courses and their department codes:


2. In the form's editor mode, select Tools >> Script editor, and write a script containing four functions:

  • A function to read the data spreadsheet (course/departmentcode):
 sheet_to_list(spreadsheetid, sheetname)
  •  A function to search through the response spreadsheet and get the selected course:
get_feedback_course(response_data, cols, last_row_index, course_question)
  •   Another function to look up the department code based on the submitted course
get_dept_code(dept_list, course_name)
  •  The final function will do call of the above and insert the department code into the responses spreadsheet:

 The full source code is here.

3. In the Script Editor windows >> Select Resources >> Current project's triggers >> Add a trigger to run the insert_dept_code() function (Form Form + On form submit)