Geospatial Solutions Expert: Google Apps Script (GAS) - Make GoogleForms from GoogleSheets

On this post, I will demonstrate how tom make a GoogleForms from GoogleSheets using Google Apps Script (GAS). There are several advantages of using GAS to automate your form creation over manual procedure.

GAS is a Googles owned scripting language that is flavor of a modern JavaScript, so if you already know javascript, then you should be confortable with GAS. With GAS, you can do more with Google apps — like GoogleCalendar, GoogleDocs, GoogleDrive, GoogleGmail, GoogleSheets, and GoogleSlides.

The form we will make is a multi choice question, and the obvious reason why we will use GAS to make it is because it has a lot questions to be added. Hence, it makes sense to prepare the questions and there option in a GoogleSheets, then generate the form from there. That is the approach we are going to take.

Create a GoogleSheet where the first column A is the question and the columns B, C and D are the answer options (only one option is the correct answer) as seen below.

I renamed my GoogleSheet to "Location_Questions" while I maintained the default sheet name "Sheet1". You can change them to whatever you wanted, just take note of the names as we will use them in the script.

Open the 'Script Editor' window by going to 'Tools' menu then select 'Script Editor' as seen below.

The 'Script Editor' window will open in a new tab. You can give it a descriptive name and also rename the function to something descriptive. Here I renamed the script window to "createForm_Script" and the function to "createForm". Yoy are free to use whatever name you want for you project.

Now is time to code the script that will use our sheet data to generate the form. As you already know, column A is the question and the columns B, C and D are the answer options.

GAS needs the question in an array and each answer options in separate arrays. The GAS create form command will then loop through arrays (question and answer options) to generate the form.

To give you an idea, below is how the arrays will look like:-

question = ["In what country is the city 'Tokyo'?", "In what country is the city 'Delhi'?", "In what country is the city 'Cairo'?", "In what country is the city 'Chongqing'?", "In what country is the city 'Orlando'?", "In what country is the city 'Abuja'?"]

answer = [['Japan', 'India', 'Brazil'], ['India', 'United States', 'China'], ['Argentina', 'Bangladesh', 'Egypt'], ['Philippines', 'Pakistan', 'China'], ['Mexico', 'Angola', 'United States'], ['Spain', 'Nigeria', 'Canada']]

With the above preamble out of the way, our GAS will have three basic parts as follow:-

Read in the GoogleSheet
Convert the question and answer columns into the expected array structures above
Loop through the arrays to create the form

In between the above stages, we may add codes to check variable structure and get counts/lengths of variables.

function createForm() {
  
  
// 1) Read in the GoogleSheet
    // Read Googlesheet data (Location_Questions.xlsx)
    const ThisSpreadsheet = SpreadsheetApp.getActive();
    const SheetQuestions = ThisSpreadsheet.getSheetByName("Sheet1");
  
  
   // Load 'Questions' sheet data as a 2-dimensional array.
    const data = SheetQuestions.getDataRange().getValues();

  
//  =====================================================
// 2) Convert the question and answer columns into the expected array structures above
   // Lets log the content of data to see what it is...
    data.forEach(function(x){
       Logger.log(x);
    });
   
  // Each item in data an array that looks like this: [In what country is the city 'Tokyo'?, Japan, India, Brazil]. So, we need to separate the questions from the answers
  
   var questions = [];
   // Populate the empty questions array with the read data. We have to do this because 'data' is a list of arrays
   data.forEach(function(row){
     questions.push(row[0]);
   });
  
  
  // For the answers, we will remove first item from array (which is the question) and keep others...
     var answers = [];
     data.forEach(function(row){
       row.shift() // remove first item
       answers.push(row);
     });
    
    Logger.log(answers);
  
  
    // Get number of rows
    var numberRows = SheetQuestions.getDataRange().getNumRows();
    Logger.log(numberRows);
  
  
//  ===================================================== 
// 3) Loop through the arrays to create the form
  // Create a new form
  var form = FormApp.create("City_Question_Form");
  
  for(var i=0; i<numberRows; i++){
      form.addMultipleChoiceItem()
        .setTitle(questions[i])
        .setChoiceValues(answers[i])
        // .showOtherOption(true)
        .setRequired(true);  
   }
  
       
  
}