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)
Courses sheet: the courses sheets is storing name of the courses each teacher is teaching
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:
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.
- First create an empty multiplechoices question item (without any choices):
- Next define the pages (PageBreakItem) which you want to direct users to when they choose a certain choice:
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 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:
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?!