У нас было две гугл-формы, 75 вопросов в каждой, 5 бизнес-пользователей, которые активно правили эти формы, а еще гугл-скрипт, экспортирующий форму в JSON. Не то что бы его было сложно каждый раз запускать руками, но раз начал автоматизировать свою работу, то иди в этом увлечении до конца.
В официальной документации сам черт ногу сломит, поэтому под катом мы подробно рассмотрим удаленную загрузку и запуск Google Apps Script через REST API, используя Python.
// 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();});
}
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
{
"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"
}
with open('export-google-form.gs', 'r') as f:
sample_code = f.read()
MANIFEST = '''
{
"timeZone": "America/New_York",
"exceptionLogging": "STACKDRIVER",
"executionApi": {
"access": "ANYONE"
}
}
'''.strip()
request = {
'files': [{
'name': 'hello',
'type': 'SERVER_JS',
'source': sample_code
}, {
'name': 'appsscript',
'type': 'JSON',
'source': MANIFEST
}
]
}
script_id = 'qwertyuiopQWERTYUIOPasdfghjkl123456789zxcvbnmASDFGHJKL54'
service.projects().updateContent(
body=request,
scriptId=script_id
).execute()
"error": {
"code": 403,
"message": "Request had insufficient authentication scopes.",
"status": "PERMISSION_DENIED"
}
https://www.googleapis.com/auth/script.projects
function main(form_url) {
var form = FormApp.openByUrl(form_url);
.......
body = {
"function": "main",
"devMode": True,
"parameters": form_url
}
resp = service.scripts().run(scriptId=script_id, body=body).execute()
import json
with open('habr_auto.json', 'w', encoding='utf-8') as f:
json.dump(resp['response']['result'], f, ensure_ascii=False, indent=4)
socket.timeout: The read operation timed out
import socket
socket.setdefaulttimeout(120)
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
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()
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()
python update_script.py <config_file_name> <script_id> <script_file_name>
python export_form.py <config_file_name> <result_file_name> <script_id> <google_form_url>
К сожалению, не доступен сервер mySQL