How I got a good order for Google Forms setup and what I had to face

Every time you start a new project, you never know what surprises it may bring. At first glance, a simple task of setting up a Google Form can turn into an exciting journey with unexpected turns. Today I want to tell you the story of one such order, in which we not only coped with the challenges, but also found an original solution.

Starting a project: the task and the first difficulty

It all started with a simple request from a client: it was necessary to create a series of surveys based on Google Forms to assess anxiety levels. What could be simpler? But here's the surprise: about ten such forms had to be created, and each of them contained from 100 to 150 questions!

Given the volume of work and the urgency of the order, I realized that simply manually transferring data from tables to Google Forms would take too much time. And since the order was urgent and well-paid, I needed to urgently come up with a solution that would allow me to cope with the task quickly and efficiently.

Setting up a table: organizing data

The first step was to organize the table correctly. All the questions were entered in the first column, and the answer options were in the following ones. It seems simple, but it was important to make sure that the table did not have empty lines, extra spaces or invisible characters that could interfere with further work.

Each question had to have four answer options: “No, that's not true,” “I guess so,” “True,” “Absolutely true.” And although the questions were different, the answer options were repeated. This immediately made me realize that there might be problems with the automatic creation of questions in Google Forms.

Coming up with a script: automating the process

Google Apps Script is a powerful tool that allows you to automate many tasks in Google Drive. Creating a script to automatically transfer questions from a spreadsheet to a Google Form was the next step. But before we could start writing the code, we had to find one important detail: the form ID.

Where can I get the form ID?

The Google Form ID is a unique code that allows the script to “understand” which form to work with. It's pretty easy to find. When you open your Google Form, you'll see something like this in the browser URL:

https://docs.google.com/forms/d/1AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTt/edit

In this line, everything that is between /d/ And /editis the form identifier. In our case, the form identifier is 1AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTt

Script Development: Step by Step

Now that we have the form ID sorted out, we can start writing the script. Here is the script we used:

function importQuestionsToForm() {
  var form = FormApp.openById('1AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTt'); // Ваш идентификатор формы
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var values = sheet.getDataRange().getValues();

  for (var i = 1; i < values.length; i++) { // Начинаем со строки 2, чтобы пропустить заголовки
    var questionText = values[i][0].trim(); // Удаляем лишние пробелы
    
    Logger.log('Строка ' + (i + 1) + ' - Вопрос: "' + questionText + '"'); // Логируем текст вопроса
    
    if (questionText === "") {
      Logger.log('Пропущена строка ' + (i + 1) + ': вопрос отсутствует, но есть ответы.');
      continue; // Пропускаем строки без вопроса
    }

    var answerOptions = values[i].slice(1).filter(String).map(option => option.trim()); // Удаляем пустые строки и пробелы
    
    Logger.log('Варианты ответов: ' + answerOptions.join(', ')); // Логируем варианты ответов
    
    if (answerOptions.length > 0) {
      try {
        var item = form.addMultipleChoiceItem(); // Добавляем вопрос с множественным выбором
        item.setTitle(questionText);
        item.setChoiceValues(answerOptions);
      } catch (e) {
        Logger.log('Ошибка добавления вопроса: ' + questionText + '. Сообщение об ошибке: ' + e.message);
      }
    } else {
      Logger.log('Пропущен вопрос: ' + questionText + ' из-за отсутствия вариантов ответа.');
    }
  }
}

What did we do in this script?

1. Form ID: At the very beginning of the script, we connect to the desired Google Form using its ID (FormApp.openById). This allows the script to control the form and add questions to it.

2. Getting data from a table: We use SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange().getValues()to read all the data from the active sheet of the table. This data is loaded into an array, where each row represents a question and its answer options.

3. Removing extra spaces: In order to avoid errors, we remove extra spaces using the method trim() — both for the question itself and for each answer option. This helps avoid situations where the question or answers contain invisible spaces.

4. Check for empty lines: If there is no question or the cell is empty, the script skips that line and logs a warning so we can check what went wrong.

5. Logging: We have added logging (Logger.log) to track how the script processes data. This is especially useful when something goes wrong and you need to understand at what stage the error occurred.

6. Adding questions and answers to a form: If all data is correct, the script adds the question to the Google Form using the method addMultipleChoiceItem() and sets the answer options through setChoiceValues().

Lessons and Conclusions: What I Learned from This Project

This project showed me that even the simplest task at first glance can hide unexpected difficulties. The main thing is not to give up and keep looking for a solution. Thanks to this project, I improved my skills in working with Google Apps Script and realized that automation can make life much easier if done correctly.

Also, remember that sometimes Google Forms may not update data automatically. If suddenly after running the script some questions are not displayed correctly, simply refresh the form page – this often solves the problem.

Now, when I get orders like this, I know what to do to make sure everything goes smoothly. And if you too face the task of setting up Google Forms, I hope my story and the suggested script will help you.