Экспорт Google Forms + загрузка Google Script через REST API (Python) +8




У нас было две гугл-формы, 75 вопросов в каждой, 5 бизнес-пользователей, которые активно правили эти формы, а еще гугл-скрипт, экспортирующий форму в JSON. Не то что бы его было сложно каждый раз запускать руками, но раз начал автоматизировать свою работу, то иди в этом увлечении до конца.

В официальной документации сам черт ногу сломит, поэтому под катом мы подробно рассмотрим удаленную загрузку и запуск Google Apps Script через REST API, используя Python.

Введение


У нас в «Доктор рядом» разрабатывается платформа для чат-ботов, в которой для описания сценариев используются гугл-формы. Соответственно, хочется из форм по нажатию кнопки получать JSON, содержащий ноды (пункты формы) и метаданные к ним (переходы между нодами, типы нод, их название). Казалось бы, желание простое, но гугл не поддерживает данный функционал и приходится собирать этот «экспортер» своими руками. Рассмотрим по шагам процесс его создания.

ШАГ 1. Google Apps Script


Google предусмотрел возможность взаимодействия со своими сервисами (Sheets, Docs, Forms) через Google Apps Script — скрипты, написанные на языке google script (.gs). Данная статья не предусматривает разбора языка google script, поэтому приведу пример готового скрипта, создающего JSON из имеющейся гугл-формы. За основу был взят с гитхаба код пользователя Steven Schmatz, за что выражаю ему свою благодарность.

Код скрипта
// Steven Schmatz
// Humanitas Labs
// 13 October, 2016.

// Roman Shekhovtsov
// dr-telemed.ru
// Autumn 2019

// Nikita Orekhov
// dr-telemed.ru
// Autumn 2019

/**
 * Converts the given form URL into a JSON object.
 */
function main() {

  form_url = "<YOUR_FORM_URL>"
  var form = FormApp.openByUrl(form_url);
  var items = form.getItems();

  var result = {
    "metadata": getFormMetadata(form),
    "items": items.map(itemToObject),
    "count": items.length
  };

//  sendEmail("<YOUR_EMAIL>", result)

  return result;
}

/** If we want to receive data by email
 * Sends JSON as text to recipient email
 * @param recipient: String
 * @param result: JSON
 */
function sendEmail(recipient, json_file){
  var subject = "google form json import"
  var body = JSON.stringify(json_file);
  Logger.log(body);
  MailApp.sendEmail(recipient, subject, body);
}

/**
 * Returns the form metadata object for the given Form object.
 * @param form: Form
 * @returns (Object) object of form metadata.
 */
function getFormMetadata(form) {
  return {
    "title": form.getTitle(),
    "id": form.getId(),
    "description": form.getDescription(),
    "publishedUrl": form.getPublishedUrl(),
    "editorEmails": form.getEditors().map(function(user) { return user.getEmail() }),
    "count": form.getItems().length,
    "confirmationMessage": form.getConfirmationMessage(),
    "customClosedFormMessage": form.getCustomClosedFormMessage()
  };
}

/**
 * Returns an Object for a given Item.
 * @param item: Item
 * @returns (Object) object for the given item.
 */
function itemToObject(item) {
  var data = {};

  data.type = item.getType().toString();

  // Downcast items to access type-specific properties

  var itemTypeConstructorName = snakeCaseToCamelCase("AS_" + item.getType().toString() + "_ITEM");
  var typedItem = item[itemTypeConstructorName]();

  // Keys with a prefix of "get" have "get" stripped

  var getKeysRaw = Object.keys(typedItem).filter(function(s) {return s.indexOf("get") == 0});

  getKeysRaw.map(function(getKey) {
    var propName = getKey[3].toLowerCase() + getKey.substr(4);

    // Image data, choices, and type come in the form of objects / enums
    if (["image", "choices", "type", "alignment"].indexOf(propName) != -1) {return};

    // Skip feedback-related keys
    if ("getFeedbackForIncorrect".equals(getKey) || "getFeedbackForCorrect".equals(getKey)
      || "getGeneralFeedback".equals(getKey)) {return};

    var propValue = typedItem[getKey]();

    data[propName] = propValue;
  });

  // Bool keys are included as-is

  var boolKeys = Object.keys(typedItem).filter(function(s) {
    return (s.indexOf("is") == 0) || (s.indexOf("has") == 0) || (s.indexOf("includes") == 0);
  });

  boolKeys.map(function(boolKey) {
    var propName = boolKey;
    var propValue = typedItem[boolKey]();
    data[propName] = propValue;
  });

  // Handle image data and list choices

  switch (item.getType()) {
    case FormApp.ItemType.LIST:
    case FormApp.ItemType.CHECKBOX:
      data.choices = typedItem.getChoices().map(function(choice) {
        return choice.getValue()
      });
    case FormApp.ItemType.MULTIPLE_CHOICE:
      data.choices = typedItem.getChoices().map(function(choice) {
        gotoPage = choice.getGotoPage()
        if (gotoPage == null)
            return choice.getValue()
        else
            return {
                "value": choice.getValue(),
                "gotoPage":choice.getGotoPage().getId()
        };
      });
      break;

    case FormApp.ItemType.IMAGE:
      data.alignment = typedItem.getAlignment().toString();

      if (item.getType() == FormApp.ItemType.VIDEO) {
        return;
      }

      var imageBlob = typedItem.getImage();

      data.imageBlob = {
        "dataAsString": "", //imageBlob.getDataAsString(), - BLOB too big
        "name": imageBlob.getName(),
        "isGoogleType": imageBlob.isGoogleType()
      };

      break;

    case FormApp.ItemType.PAGE_BREAK:
      data.pageNavigationType = typedItem.getPageNavigationType().toString();
      break;

    default:
      break;
  }

  // Have to do this because for some reason Google Scripts API doesn't have a
  // native VIDEO type
  if (item.getType().toString() === "VIDEO") {
    data.alignment = typedItem.getAlignment().toString();
  }

  return data;
}

/**
 * Converts a SNAKE_CASE string to a camelCase string.
 * @param s: string in snake_case
 * @returns (string) the camelCase version of that string
 */
function snakeCaseToCamelCase(s) {
  return s.toLowerCase().replace(/(\_\w)/g, function(m) {return m[1].toUpperCase();});
}


Что происходит в коде:

  • функция getFormMetadata — возвращает JSON с метаданными формы
  • функция itemToObject — преобразует объект form.item в JSON с требуемыми полями
  • функция sendEmail — отправляет текстом JSON файл на указанную почту
  • функция main — возвращает итоговый JSON
  • переменная form_url в функции main — адрес нашей гугл-формы

ШАГ 2. Тестирование скрипта


В данный момент работоспособность скрипта можно проверить следующим образом:

  1. создаем свой App Script проект
  2. копируем в него код
  3. вместо <YOUR_FORM_URL> подставляем адрес своей формы вида docs.google.com/forms/d/FORM_IDENTIFICATOR/edit
  4. раскомментируем вызов функции sendEmail в функции main
  5. вместо <YOUR_EMAIL> подставляем адрес своей электронной почты, на которую хотим получить JSON
  6. сохраняем проект
  7. запускаем функцию main
  8. если это первый запуск скрипта, то система сообщит о необходимости дать скрипту разрешение отправлять email с вашего адреса. Не стоит пугаться. Это стандартная процедура, необходимая для тестирования скрипта. Проходим «Review permissions» -> выбираем свой аккаунт -> «Advanced» -> «Go to PROJECT_NAME project (unsafe)» -> «Allow»
  9. ждем отработки скрипта
  10. смотрим в почтовый ящик и видим JSON файл в текстовом виде

Все бы ничего, но дальнейшее использование полученных данных предполагает ручное копирование из почты, обработку этого текста (в питоне, например) и сохранение итогового файла. Звучит не слишком production-ready. Автоматизируем запуск этого скрипта и получение его результата через Google Apps Script API, однако прежде настроим наш гугл-проект соответствующим образом.

Внимание: Для удобства понимания происходящего, ниже я буду ссылаться только на две страницы, поэтому рекомендуется открыть их в соседних вкладках:

  1. Страница скриптов / редактирования скрипта — «страница 1»
  2. Страница Google Cloud Platform — «страница 2»

ШАГ 3. Настройка Google Cloud Platform


Заходим в Google Cloud Platform (страница 2), создаем новый проект. Необходимо создать новый проект, ибо по умолчанию статус проекта — Default, а для наших целей требуется Standart. Подробнее можно почитать тут (пункт 3).

Возвращаемся на страницу 2, переходим во вкладку «API и сервисы», затем «Окно запроса доступа OAuth». Ставим User Type «Внешний».

В появившемся окне заполняем «Название приложения».

Открываем главную страницу в Google Cloud Platform. Из блока «Информация о проекте» копируем номер проекта.

Переходим на страницу 1. Открываем ранее созданный скрипт. В открывшемся окне редактирования скрипта идем в «Resources» -> «Cloud Platform project». В поле «Change project» вводим скопированный ранее номер проекта. Теперь этот скрипт ассоциирован с созданным проектом.

ШАГ 4. Python REST API


Пришло время автоматизировать работу скрипта с помощью REST API. В качестве языка был использован Python.

Логин в Apps Script API


Код должен иметь доступ к проекту, поэтому первая и очень важная процедура — логин в Apps Script API. Открываем страницу 2 -> «API и сервисы» -> «Учетные данные» -> «Создать учетные данные» -> «Идентификатор клиента OAuth» -> «Другие типы». Называем наш идентификатор, переходим в него. Находясь во вкладке «Учетные данные», выбираем «Скачать файл JSON». При этом будет загружен файл ключей для доступа из кода к проекту в гугле. Помещаем данный файл в папку «credentials».

Теперь необходимо дать разрешение на использование API (в нашем случае Apps Script API) в рамках данного проекта. Для этого переходим в «API и сервисы» -> «Библиотека» -> набираем в поиске «Apps Script API» и нажимаем «Включить».

Приложения, взаимодействующие с гуглом, имеют скоп разрешений, которые пользователь должен дать при его запуске. Этот скоп зависит от функций, используемых конкретным скриптом и узнать его можно, пройдя на странице 1 в окне редактирования скрипта в «File» -> «Project Properties» -> «Scopes». Указанные разрешения следует сохранить для дальнейшего использования в коде.

В таком случае функция логина будет выглядеть следующим образом:

import pickle
import os.path

from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

def login(config):
    try:
        creds = None
        # The file token.pickle stores the user's access and refresh tokens, and is
        # created automatically when the authorization flow completes for the first
        # time.
        token_file = config['credentials_path'] + config['token_file']
        credentials_file = config['credentials_path'] + config['credentials_file']
        if os.path.exists(token_file):
            with open(token_file, 'rb') as token:
                creds = pickle.load(token)
        # If there are no (valid) credentials available, let the user log in.
        if not creds or not creds.valid:
            if creds and creds.expired and creds.refresh_token:
                creds.refresh(Request())
            else:
                flow = InstalledAppFlow.from_client_secrets_file(credentials_file, config['SCOPES'])
                creds = flow.run_local_server(port=0)
            # Save the credentials for the next run
            with open(token_file, 'wb') as token:
                pickle.dump(creds, token)

        service = build('script', 'v1', credentials=creds)
        pprint('Login successful')
        return service

    except Exception as e:
        pprint(f'Login failure: {e}')
        return None

Данный блок кода — стандартная процедура для начала работы с Google App Script.
Мы используем аутентификационный токен и, осуществляя login, либо создаем новый токен, либо используем уже существующий.

Для удобства был создан конфигурационный JSON файл, имеющий следующий вид:

{
    "SCOPES": ["https://www.googleapis.com/auth/forms",
        "https://www.googleapis.com/auth/script.send_mail"],
    "credentials_path": "credentials/",
    "credentials_file": "google_test_project.json",
    "token_file": "token.pickle"
}

Важно: токен создается для аутентификации с конкретным скопом разрешений. Иными словами, при изменении скопа разрешений, следует удалить токен и при логине создать новый.

Удаленное обновление кода скрипта


Теперь научимся удаленно обновлять код скрипта, затем запускать этот код и получать результат. На самом деле, помимо того кода, что мы запускаем в гугловском редакторе, есть еще манифест файл, в котором указаны права запуска, настройки деплоймента и т.д. Подробнее ознакомиться с его структурой можно по ссылке.
Чтобы посмотреть на дефолтный манифест файл, созданный гуглом к вашему скрипту, пройдите в редакторе скрипта в «View» -> «Show manifest file». Манифест появится в списке файлов, относящихся к данному скрипту.

Речь о манифесте зашла неспроста: для удаленного обновления скрипта требуется загрузка кода обоих файлов (*.gs) и манифеста (appscript.json).

Для начала читаем код .gs файла, который мы хотим задеплоить:

    with open('export-google-form.gs', 'r') as f:
        sample_code = f.read()

Теперь скопируем автоматически сгенерированный манифест и немного модифицируем его под наши цели. Документация достаточно исчерпывающе описывает структуру манифест файла, поэтому останавливаться на этом моменте я не буду. Для работы скрипта необходимо добавить в дефолтный манифест секцию «executionApi», которая требуется для удаленного запуска скрипта через API. В данной секции указываем круг лиц, имеющих возможность запускать его. Я разрешил запуск для всех, кто прошел авторизацию, что соответствует идентификатору «ANYONE»:

MANIFEST = '''
{
    "timeZone": "America/New_York",
    "exceptionLogging": "STACKDRIVER",
    "executionApi": {
        "access": "ANYONE"
    }
}
'''.strip()

Тело запроса на обновление должно содержать в себе массив файлов со следующей структурой:

  • name: имя файла, который будет создан на сервере, без расширения
  • type: тип файла (JSON для манифеста, SERVER_JS для .gs)
  • source: код файла

request = {
        'files': [{
            'name': 'hello',
            'type': 'SERVER_JS',
            'source': sample_code
        }, {
            'name': 'appsscript',
            'type': 'JSON',
            'source': MANIFEST
        }
        ]
}

Наконец, сам запрос на обновление должен содержать в себе тело (request, описанный выше) и ID скрипта. Последнее можно получить, пройдя в редакторе скрипта в «File» -> «Project Properties» и скопировав «Script ID»:

script_id = 'qwertyuiopQWERTYUIOPasdfghjkl123456789zxcvbnmASDFGHJKL54'

У объекта service, полученного в результате логина, получаем поле projects() и вызываем метод updateContent(), после чего у полученного объекта HttpRequest вызываем метод execute():

service.projects().updateContent(
        body=request,
        scriptId=script_id
).execute()

Однако, на данный момент запуск кода приведет к ошибке:

"error": {
    "code": 403,
    "message": "Request had insufficient authentication scopes.",
    "status": "PERMISSION_DENIED"
}

Как видим, не хватает разрешений в аутентификационном скопе, который мы указали ранее. Обратимся к официальной документации по API, а именно к методу updateContent, который мы использовали для удаленного обновления скрипта. Документация гласит, что использование этого метода требует включения разрешения доступа к script.projects:

https://www.googleapis.com/auth/script.projects

Добавим его в нашу конфигурационный файл в секцию SCOPES. Как я уже писал выше, при изменении скопа, необходимо удалять автоматически создающийся токен.

Отлично! На данный момент мы научились удаленно обновлять гугл скрипт. Осталось его запустить и получить результат выполнения.

Запуск скрипта


Запрос на запуск скрипта содержит scriptID и body со следующей структурой:

  • function: имя функции, которую мы хотим запускать
  • parameters: (optional) набор параметров примитивного типа (string, array ...), передаваемых в функцию
  • sessionState: (optional) требуется только для Android приложений
  • devMode: (optional) True в случае если юзер является владельцем скрипта и тогда будет запущена самая свежая версия, нежели та, что задеплоена с помощью Apps Script API. (по дефолту — False)

Для того чтобы не зашивать URL гугл-формы в скрипте, будем передавать form_url в функцию main как аргумент.

Внимание. Когда мы тестировали скрипт, то функция main ничего не принимала, поэтому изменим первые строки кода в .gs файле следующим образом:

function main(form_url) {

  var form = FormApp.openByUrl(form_url);
.......

Так как наше приложение не под Android и мы являемся владельцами скрипта, то в итоге body будет выглядеть так:

body = {
        "function": "main",
        "devMode": True,
        "parameters": form_url
    }

Запустим скрипт и запишем результат выполнения в переменную resp:

resp = service.scripts().run(scriptId=script_id, body=body).execute()

Сохраним resp в файл с удобным JSON форматированием:

import json

with open('habr_auto.json', 'w', encoding='utf-8') as f:
    json.dump(resp['response']['result'], f, ensure_ascii=False, indent=4)

Внимание. В силу того, что script.run() запрос ждет результат через сокет, то при превышении таймаута временем выполнения, произойдет ошибка следующего типа:

socket.timeout: The read operation timed out

Для избежания такого поведения, рекомендую в начале программы установить лимит на открытое время сокета, заведомо достаточное для того чтобы он дождался окончания выполнения скрипта. В моем случае 120 секунд достаточно:

import socket

socket.setdefaulttimeout(120)

Вуаля! Удобный пайплайн для удаленного обновления и запуска гугл скриптов готов. Полный код, приспособленный к запуску из терминала приведен в моем гитхабе.

Также, приведу код основных функций ниже

login.py
from pprint import pprint
import pickle
import os.path

from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request


def login(config):
    try:
        creds = None
        # The file token.pickle stores the user's access and refresh tokens, and is
        # created automatically when the authorization flow completes for the first
        # time.
        token_file = config['credentials_path'] + config['token_file']
        credentials_file = config['credentials_path'] + config['credentials_file']
        if os.path.exists(token_file):
            with open(token_file, 'rb') as token:
                creds = pickle.load(token)
        # If there are no (valid) credentials available, let the user log in.
        if not creds or not creds.valid:
            if creds and creds.expired and creds.refresh_token:
                creds.refresh(Request())
            else:
                flow = InstalledAppFlow.from_client_secrets_file(credentials_file, config['SCOPES'])
                creds = flow.run_local_server(port=0)
            # Save the credentials for the next run
            with open(token_file, 'wb') as token:
                pickle.dump(creds, token)

        service = build('script', 'v1', credentials=creds)
        pprint('Login successful')
        return service

    except Exception as e:
        pprint(f'Login failure: {e}')
        return None


update_script.py
from pprint import pprint
import json
import sys

from googleapiclient import errors

from google_habr_login import login

MANIFEST = '''
{
    "timeZone": "America/New_York",
    "exceptionLogging": "STACKDRIVER",
    "executionApi": {
        "access": "ANYONE"
    }
}
'''.strip()


def update_project(service, script_id, script_file_name):
    # Read from file code we want to deploy
    with open(script_file_name, 'r') as f:
        sample_code = f.read()

    # Upload two files to the project
    request = {
        'files': [{
            'name': 'hello',
            'type': 'SERVER_JS',
            'source': sample_code
        }, {
            'name': 'appsscript',
            'type': 'JSON',
            'source': MANIFEST
        }
        ]
    }

    # Update files in the project
    service.projects().updateContent(
        body=request,
        scriptId=script_id
    ).execute()

    pprint('Project was successfully updated')


def main():

    try:

        args = sys.argv
        if len(args) != 4:
            raise TypeError('Wrong number of arguments. Three argument required: <config_file_name>, <script_id> and ' 
                            '<script_file_name>')
        config_file_name = args[1]
        script_id = args[2]
        script_file_name = args[3]

        with open(config_file_name, "r") as f:
            config = json.load(f)

        service = login(config)

        update_project(service, script_id, script_file_name)

    except (errors.HttpError, ) as error:
        # The API encountered a problem.
        pprint(error.content.decode('utf-8'))


if __name__ == '__main__':
    main()



export_form.py
from pprint import pprint
import socket
import json
import sys

from googleapiclient import errors

from google_habr_login import login


socket.setdefaulttimeout(120)


# Get JSON, which is returned by script
def get_json(service, file_name, script_id, form_url):
    pprint('Exporting form...')
    body = {
        "function": "main",
        "devMode": True,
        "parameters": form_url
    }
    # Get JSON from script
    resp = service.scripts().run(scriptId=script_id, body=body).execute()

    # Write out JSON to file
    with open(file_name, 'w', encoding='utf-8') as f:
        json.dump(resp['response']['result'], f, ensure_ascii=False, indent=4)

    pprint('Form was successfully exported')


def main():

    try:

        args = sys.argv
        if len(args) != 5:
            raise TypeError('Wrong number of arguments. Four arguments required:  <config_file_name>, '
                            '<result_file_name>, <script_id> and <google_form_url>')
        config_file_name = args[1]
        file_name = args[2]
        script_id = args[3]
        form_url = args[4]

        with open(config_file_name, "r") as f:
            config = json.load(f)

        service = login(config)

        get_json(service, file_name, script_id, form_url)

    except (errors.HttpError, ) as error:
        # The API encountered a problem.
        pprint(error.content.decode('utf-8'))


if __name__ == '__main__':
    main()



Для запуска необходимо поместить JSON файл с ключами доступа к гуглу в папку «credentials», а конфигурационный JSON в одной директории со скриптами.

Затем, если мы хотим удаленно обновить скрипт, то в терминале вызвать:

python update_script.py <config_file_name> <script_id> <script_file_name>

В данном случае:

  • config_file_name — имя конфигурационного JSON файла
  • script_id — ID скрипта
  • script_file_name — имя .gs файла, который будет залит в гугл

Для запуска скрипта надо вызвать:

python export_form.py <config_file_name> <result_file_name> <script_id> <google_form_url>

В данном случае:

  • config_file_name — имя конфигурационного JSON файла
  • result_file_name — имя JSON файла, в который будет выгружена форма
  • script_id — ID скрипта
  • google_form_url — URL гугл-формы

Спасибо за внимание, жду ваши предложения и комментарии :)




К сожалению, не доступен сервер mySQL