Google Apps Script for Converting Google Spreadsheet to Google Form
Please consider the following problem. We have some kind of questions bank which we stored in spreadsheet like Excel or anything similar. One day, we want to create an online quiz with Google Form based on the questions bank we had. How can we generate the online quiz in easy and fast way? By using Google Apps Script we can automate the task of converting the questions from Google Spreadsheet into Google Form. The script will generate the final Google Form which we can use and share to our respondents later.
In order to do that, please follow these steps. By the way the user interface in the following steps is in Bahasa Indonesia but the place of each menu is exactly the same with other language so I think it will not be an issue to follow the explanation below.
- First of all, open the following link for downloading and copying some required files: https://github.com/w3shaman/spreadsheet-to-form.
- After that, please download the create_form.xlsx. Please pay attention to some important sections of that file. There are specific section for us to put the quiz title, description, list of questions, answer options, correct answer, point, and also the separator to create new page or section in Google Form.
- We are also able to create non-multiple choice question by omitting the answer options, and correct answer.
- New, Create a new Google Spreadsheet.
- Copy the content of create_form.xlsx then paste it to your new Google Spreadsheet.
- Click Tools -> Script Editor.
- Download Code.gs then copy its content to the script editor then save it.
- Reload the Spreadsheet.
- Now there will be a new menu Kuis after the Help menu. Click it then click Buat Google Form.
- If you were asked for authorization, just click Continue then login with your Google Account. After that, click Allow. We might need to repeat the step in number 9 once again.
- Wait until the dialog box appeared.
- Check your Google Drive, there will be a new Google Form for that quiz. You can edit it again or just publish it.