Generating Google Form from Google Spreadsheet using Google Apps Script

Google Form is such a great tool for us to create surveys with a great web interface. But, the thing is even with the cool drag-and-drop features, It's still a pain in the ass to generate forms from a mass amount of data (questions and choices). That's where Google Apps Scripts comes into its usefulness. Here is a case study from my real life experience:

Earlier this week I had a chance to tinkering around the apps scripts to help my school create a survey with a set of data stored inside an excel file. The final goal is to create a form in which students give feedback to each of their teachers on each courses. So the excel data looks like this:

Teachers sheet: this sheet contains 3 columns, each column is a list of all the teachers of each schools (elementary, middleschool and highschool)

es,ms,hs
teacher1,teacher2,teacher3
teacher6,teacher7,teacher8
...

Courses sheet: the courses sheets is storing name of the courses each teacher is teaching

teacher1,teacher2,teacher3,teacher6,teacher7,teacher8
course1,course2,course3,course8,course9,course10
course4,,course...


So, here is how I accomplished the task:

1. First, I uploaded the excel file into my Google Drive and convert it into Google Spreadsheet. This helps me to take advantages of the API(s).
Taking note the ID of the spreadsheet (look in the URL of the spreadsheet) and name of the sheets (teachers and courses sheet). For example:

DATA_SPREADSHEET_ID = 1jZiWpP00I4MxZvoQpKGw1uw9ArNPsX4Hz3NPZABCD
TEACHER_SHEET = 'teachers';
COURSE_SHEET = 'courses';

You can view the sample data here.


2. Go to Google Apps Script and click Start Scripting

3. Write the script to read the data spreadsheet and another script to generate the form. The two important parts are:

• Read the data spreadsheet and convert it into a dictionary (keys is the column headers). (Read my previous blog post to know how)
• Using FormApp to generate forms with questions, choices (teachers and courses data), and even page navigation logic.

There are something you have to remember in order to add the correct page navigation logic in Google Forms. Follow these steps in sequence:

• First create an empty multiplechoices question item (without any choices):

var school_choices = form.addMultipleChoiceItem().setTitle('In what part of the school are you a student?');

• Next define the pages (PageBreakItem) which you want to direct users to when they choose a certain choice:

var es_page = form.addPageBreakItem().setTitle('Elementary (up to grade 5)');
var ms_page = form.addPageBreakItem().setTitle('Middle school (grades 6, 7, and 8)');
var hs_page = form.addPageBreakItem().setTitle('High school (grades 9, 10, 11, and 12)');

• Then create the choices and set the PageBreakItem(s) in the previous step as their page navigation:

var es_choice = school_choices.createChoice('Elementary (up to grade 5)', es_page);
var ms_choice = school_choices.createChoice('Middle school (grades 6, 7, and 8)', ms_page);
var hs_choice = school_choices.createChoice('High school (grades 9, 10, 11, and 12)', hs_page);

•  Finally, add those choices to the multiplechoices item:

school_choices.setChoices([es_choice, ms_choice, hs_choice]);

 Check out the full source code here.

4. Run the main() function to generate your form.


This can save me hours of work! Pretty awesome huh?!

References:
[0] https://developers.google.com/apps-script/reference/forms/
[1] https://developers.google.com/apps-script/reference/spreadsheet/