google-sheets-to-google-forms.md

How to transform Google Sheets to Google Forms

Code samples

Google Sheets

A piece of code shown below demonstrates authorization to Google Account and Google Sheet reading using NodeJS:

  client = await authenticate({       // функция authenticate() есть в официальной библиотеке Cloud APIs
    scopes: SCOPES,
    keyfilePath: CREDENTIALS_PATH,
  });
  if (client.credentials) {
    await saveCredentials(client);    // функция, создающая файл "token.json" в проекте (её можно найти в документации Google "Google Sheets for Developers: Node.js quickstart")
  }
  
  const sheets = google.sheets({version: 'v4', client});
  const docID = process.argv[2];      // аргумент командной строки, обозначающий id обрабатываемой Google-таблицы

  const res = (await sheets.spreadsheets.values.get({   // непосредственно данные в виде нескольких js-массивов
    spreadsheetId: docID,
    range: 'A:C',
    majorDimension: 'COLUMNS'
  })).data;

Google Forms

And the following code illustrates creating and modifying Google Form:

const forms = googleForms.forms({          // авторизоваться в гугл-формы
  version: 'v1',
  auth: authClient,
});
const newForm = {                          // запрос (из REST API) для создания новой формы
  info: {
    title: 'Средства связи в системах управления автономными роботами(общее)',
  }
};
const form = await forms.forms.create({    // создание новой формы
  requestBody: newForm,
});

After that in your catalogue you would see new form with customized header:

Than you can edit your form, for example:

  const update = {                          // запрос на изменение настройки "Тест" (isQuiz) формы
    requests: [
      {
        updateSettings: {
          settings: {
            quizSettings: {
              isQuiz: true
            }
          },
          updateMask: 'quizSettings.isQuiz',
        },
      },
    ],
  };
  
  await forms.forms.batchUpdate({          // изменение настройки "Тест" (isQuiz) формы
    formId: form.data.formId,              // получить id созданной на предыдущем шаге формы
    requestBody: update,                   // запрос, отправляемый Google-серверу
  });

When Google Server run that code, "Test" setting of the form will turn on. And your new form will become a quiz or survey, depends on your idea.

batchUpdate(formId, request) Google function can also add a question to your survey form:

  update = {
    requests: [
      {
        createItem: {
          item: {
            "itemId": '0',           // id элемента формы
            "title": 'Question 1. What would you do if I visited you?',
            "questionItem": {
              "question": {
                "questionId": '01',  // id вопроса
                "required": true,    // обязательный
                "grading": {
                  "pointValue": 5,   // кол-во баллов за верный ответ
                  "correctAnswers": {
                    "answers": [
                      {
                        "value": 'Hug you!'
                      }
                    ]
                  }
                },
                "textQuestion": {
                  "paragraph": false
                }
              }
            }
          },
          location: {
            index: 0,
          },
        },
      },
    ],
  };
  
  await forms.forms.batchUpdate({
    formId: form.data.formId,
    requestBody: update,
  });

As a result, you would see such appearance of the form:

Install and run

To run NodeJS script you need installed on your computer node framework (download link: https://nodejs.org/en/download/) and npm. Npm will be able to add to your project folder all Google libraries that you need: googleapis and @google-cloud/local-auth:

Windows console:
project-root-folder> npm install googleapis
project-root-folder> npm install --save @google-cloud/local-auth

If you have it then you have to create a Google Account (or just log in if you already have it) and a Google Cloud project which let you to connect node app and Google Docs and Forms.

The next step, node app has to authorize to your Account and start working with your Google files (sheets and forms).

To run app just enter following command:

Windows console:
project-root-folder> node <script-name>.js

Conclusion

It is wonderful thing to work with Google Cloud Documents via user defined scripts and programs. It can help to automate a lot of routine things. It is possible to use other languages, such as: Go, Java and Python. It's up to you, dear reader! :)

External links

Useful and interesting resources: - https://developers.google.com/forms/api/reference/rest - https://developers.google.com/sheets/api/guides/concepts - https://learn.javascript.ru/promise - https://github.com/LifeSweetener/GoogleSheets-to-GoogleForms-app (my GitHub Repository with full ready node script)

THANKS

Thanks to the initiators of this idea 👍 and to GitHub Gist author tanaikech ⭐: https://gist.github.com/tanaikech/3e131b55c7947ced19a3dd99410367eb