Google apps script подключить файл
Google Apps Script lets you do new and cool things with Google Sheets. You can use Apps Script to add custom menus, dialogs, and sidebars to Google Sheets. It also lets you write custom functions for Sheets, as well as integrate Sheets with other Google services like Calendar, Drive, and Gmail.
Most scripts designed for Google Sheets manipulate arrays to interact with the cells, rows, and columns in a spreadsheet. If you're not familiar with arrays in JavaScript, Codecademy offers a great training module for arrays. (Note that this course wasn't developed by and isn't associated with Google.)
For a quick introduction to using Apps Script with Google Sheets, see the 5-minute quickstart guide for Macros, Menus, and Custom Functions.
Drive REST API
The Google Drive REST API allows developers to access files in Google Drive programmatically. This API uses GET to download files and PUT/POST to upload files. Please refer to the Google Drive REST API Overview page for detailed documentation and quickstarts.
This guide will focus on listing and moving files with the Files resource using these calls:
Index.html
Once that basic framework is in place, all you have to do is save a version of your script, then deploy your script as a web app.
After the script is deployed as a web app, you can also choose to embed it in a Google Site.
Reading data
Suppose you have a list of product names and product numbers that you store in a spreadsheet, as shown in the image below.
The example below shows how to retrieve and log the product names and product numbers.
View logs
ШАГ 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» вводим скопированный ранее номер проекта. Теперь этот скрипт ассоциирован с созданным проектом.
Import projects to Drive
Create new files within a project
To create a new file within a project, send a PUT request for a file without the id property. If you include a file with an unknown identifier, the system will throw an error message.
Delete files within a project
To delete a file from a project, send a PUT request that does not include that file (but that does include all other files in the project). Any file that is not sent back during the import process will be deleted from the server.
Rename files within a project
To rename a file within a project, send a PUT request with the existing id but a new name . Note that the server ignores any attempts to change to type .
Create new project
To create a new project, send a POST request to the file insert API. Much like the update call, you can use a client library to include metadata such as the project name and description.
Here is a sample transaction of the media upload. This will create a project called "Untitled" in your Drive. The convert parameter in the URL is required. As with the update call, the Content-Type header is required.
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
The HTML service lets you serve web pages that can interact with server-side Apps Script functions. It is particularly useful for building web apps or adding custom user interfaces in Google Docs, Sheets, and Forms. You can even use it to generate the body of an email.
Index.html
Note that the first time you want to display this user interface, you must either run the onOpen() function manually in the script editor or reload the window for the Docs, Sheets, or Forms editor (which will close the script editor). After that, the custom menu should appear within a few seconds every time you open the file. Choose Dialog > Open to see the interface.
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
В открывшемся редакторе напишем любую функцию, которая вам нравится. Я напишу функцию поиска значения в таблице по ключу.
Передаю собственно ключ, лист, на котором надо искать, и номер колонки.
Лично мне в функции все понятно, но будем предусмотрительными и задокументируем ее.
Для этого перед функцией прописываем комментарий:
В первой строке говорим, что функция делает. Далее описываем параметры: тип параметра заключаем в фигурные скобки, за скобками - имя параметра, далее - описание.
В последней строке добавим описание возвращаемого результата, следуя той же логике, что и при определении параметров.
В результате получаем что-то вроде этого:
Сохраняем код и деплоим.
Открывается окно New deployment. Здесь кликаем на шестеренку и выбираем Library
Добавляем описание и кликаем Deploy
После деплоя получаем ссылку и копируем ее.
Из полученного URL нам нужен только id (выделено в ссылке)
Code.gs
ШАГ 4. Python REST API
Пришло время автоматизировать работу скрипта с помощью REST API. В качестве языка был использован Python.
Подключение библиотеки
Открываем гугл таблицу и скрипт, из которого хотим вызывать функции из созданной библиотеки. Во вкладке Libraries кликаем на "+"
Вставляем ТОЛЬКО id из URL в строку и кликаем Look up.
В последней строке указываем идентификатор нашей библиотеки, по которому мы будем к ней обращаться в коде.
Возвращаемся к скрипту. Слева во вкладке Libraries видим идентификатор подключенной библиотеки. Когда обращаемся к библиотеке в коде, сразу видим список доступных в этой библиотеке функций (у нас она пока одна)
При выборе функции сразу видим ее описание.
Таким образом, мы создали библиотеку с задокументированной функцией. Аналогично в эту же библиотеку можно добавить другие функции и описание к ним.
Надеюсь, для кого-то пост может быть полезным :-) Спасибо за внимание, жду фидбека.
У нас было две гугл-формы, 75 вопросов в каждой, 5 бизнес-пользователей, которые активно правили эти формы, а еще гугл-скрипт, экспортирующий форму в JSON. Не то что бы его было сложно каждый раз запускать руками, но раз начал автоматизировать свою работу, то иди в этом увлечении до конца.
В официальной документации сам черт ногу сломит, поэтому под катом мы подробно рассмотрим удаленную загрузку и запуск Google Apps Script через REST API, используя Python.
Data validation
Apps Script lets you access existing data-validation rules in Google Sheets or create new rules. For instance, the following sample shows how to set a data-validation rule that allows only numbers between 1 and 100 on a cell.
Triggers
Scripts that are bound to a Google Sheets file can use simple triggers like the functions onOpen() and onEdit() to respond automatically when a user who has edit access to the spreadsheet opens or edits the spreadsheet.
Like simple triggers, installable triggers let Google Sheets run a function automatically when a certain event occurs. Installable triggers, however, offer more flexibility than simple triggers and support the following events: open, edit, change, form submit, and time-driven (clock).
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Since Apps Script projects reside on Google Drive, developers can import and export Apps Script source code using the Google Drive REST API (not to be confused with the Drive Service in Apps Script).
For example, a developer can author new Apps Script code on her local machine with her favorite code editor and use a version control system like Git to collaborate with other developers. When a version is finalized, she can upload (import) the files to Google Drive using the REST API, where they can be used like any other Apps Script project.
Code changes can be made on the local versions and synced to the Apps Script project using the Google Drive REST API. Existing projects can be downloaded (exported) from Google Drive to a local machine.
Export projects from Drive
Once you get a File resource back from the API, the exportLinks property will contain a URL to fetch to get the contents of the project as JSON data. Here is a sample of what this URL might look like:
Make a request to this URL to retrieve the contents of the project itself. Ensure that you include an Authorization header with the same OAuth Bearer token
Here is a sample request and response:
The example above includes code for a simple web app from the HTML Service guide. Note that you get back an array of Files , each with the following 4 properties:
id | Internal identifier of a file within a project, needed to reference this file during updates. |
name | The name of the file without extensions, as displayed in the Script Editor. |
type | The two types of files are server_js and html. |
source | The encoded source code contained in the file. |
Legacy editor
To view the data that has been logged, choose View > Logs from the script editor.
Charts
Apps Script lets you embed charts in a spreadsheet that represent the data in a specific range. The following example generates an embedded bar chart, assuming you have chartable data in cells A1:B15 :
To learn more about embedding a chart into your spreadsheet, see EmbeddedChart and specific chart builders, such as EmbeddedPieChartBuilder .
Formatting
The Range class has methods like setBackground(color) to access and modify the format of a cell or range of cells. The following example shows how you can set the font style of a range:
Запуск скрипта
Запрос на запуск скрипта содержит scriptID и body со следующей структурой:
- function: имя функции, которую мы хотим запускать
- parameters: (optional) набор параметров примитивного типа (string, array . ), передаваемых в функцию
- sessionState: (optional) требуется только для Android приложений
- devMode: (optional) True в случае если юзер является владельцем скрипта и тогда будет запущена самая свежая версия, нежели та, что задеплоена с помощью Apps Script API. (по дефолту — False)
Внимание. Когда мы тестировали скрипт, то функция main ничего не принимала, поэтому изменим первые строки кода в .gs файле следующим образом:
Так как наше приложение не под Android и мы являемся владельцами скрипта, то в итоге body будет выглядеть так:
Запустим скрипт и запишем результат выполнения в переменную resp:
Сохраним resp в файл с удобным JSON форматированием:
Для избежания такого поведения, рекомендую в начале программы установить лимит на открытое время сокета, заведомо достаточное для того чтобы он дождался окончания выполнения скрипта. В моем случае 120 секунд достаточно:
Вуаля! Удобный пайплайн для удаленного обновления и запуска гугл скриптов готов. Полный код, приспособленный к запуску из терминала приведен в моем гитхабе.
Также, приведу код основных функций ниже
Для запуска необходимо поместить JSON файл с ключами доступа к гуглу в папку «credentials», а конфигурационный JSON в одной директории со скриптами.
Legacy editor
To add an HTML file to your Apps Script project, open the Script Editor and choose File > New > HTML File.
Within the HTML file, you can write most standard HTML, CSS, and client-side JavaScript. The page will be served as HTML5, although some advanced features of HTML5 are not available, as explained in Restrictions.
Your file can also include template scriptlets that are processed on the server before the page is sent to the user — similar to PHP — as explained in the section on templated HTML.
Features and Limitations
If you want to use the Google Drive REST API to import or export projects, be aware of the following:
- Server-side script files are expected to end in ".gs". You may wish to develop locally using .js files, but make sure to rename to include have a .gs extension prior to importing to Google Drive.
- Client-side script files need to end with ".html". This includes client-side .html, .js or .css files. Again, you can locally develop using other extensions, but it is important to have the .html extension prior to importing to Google Drive.
- When you import project files to Google Drive, all existing data in those files will be overwritten. You cannot append or insert partial text; the whole file must be updated.
- Server-side script files must contain valid JavaScript. If there are errors in your server .js files, the Google Drive REST API update call will fail with a 5xx error. You can prevent this by linting your code prior to importing.
- Empty files cannot be imported. The Google Drive REST API update call will fail with a 5xx error if you try to upload an empty file.
- Only standalone scripts can be imported or exported.Container-bound scripts cannot be accessed through the Google Drive REST API.
- Only source code can be imported or exported. Resources such as project properties or logs are also not exposed via the Google Drive REST API. Actions such as script versioning, publishing or executing the script are not possible via the Google Drive REST API.
- You are not limited to a single server Code.gs file. You can spread server code across multiple files for ease of development. All of the server files are loaded into the same global namespace, so use JavaScript classes when you want to provide safe encapsulation.
Логин в Apps Script API
Теперь необходимо дать разрешение на использование API (в нашем случае Apps Script API) в рамках данного проекта. Для этого переходим в «API и сервисы» -> «Библиотека» -> набираем в поиске «Apps Script API» и нажимаем «Включить».
Приложения, взаимодействующие с гуглом, имеют скоп разрешений, которые пользователь должен дать при его запуске. Этот скоп зависит от функций, используемых конкретным скриптом и узнать его можно, пройдя на странице 1 в окне редактирования скрипта в «File» -> «Project Properties» -> «Scopes». Указанные разрешения следует сохранить для дальнейшего использования в коде.
В таком случае функция логина будет выглядеть следующим образом:
Данный блок кода — стандартная процедура для начала работы с Google App Script.
Мы используем аутентификационный токен и, осуществляя login, либо создаем новый токен, либо используем уже существующий.
Для удобства был создан конфигурационный JSON файл, имеющий следующий вид:
Важно: токен создается для аутентификации с конкретным скопом разрешений. Иными словами, при изменении скопа разрешений, следует удалить токен и при логине создать новый.
Writing data
To store data, such as a new product name and number to the spreadsheet, add the following code to the end of the script.
The above code appends a new row at the bottom of the spreadsheet, with the values specified. If you run this function, you'll see a new row added to the spreadsheet.
Удаленное обновление кода скрипта
Теперь научимся удаленно обновлять код скрипта, затем запускать этот код и получать результат. На самом деле, помимо того кода, что мы запускаем в гугловском редакторе, есть еще манифест файл, в котором указаны права запуска, настройки деплоймента и т.д. Подробнее ознакомиться с его структурой можно по ссылке.
Чтобы посмотреть на дефолтный манифест файл, созданный гуглом к вашему скрипту, пройдите в редакторе скрипта в «View» -> «Show manifest file». Манифест появится в списке файлов, относящихся к данному скрипту.
Речь о манифесте зашла неспроста: для удаленного обновления скрипта требуется загрузка кода обоих файлов (*.gs) и манифеста (appscript.json).
Для начала читаем код .gs файла, который мы хотим задеплоить:
Теперь скопируем автоматически сгенерированный манифест и немного модифицируем его под наши цели. Документация достаточно исчерпывающе описывает структуру манифест файла, поэтому останавливаться на этом моменте я не буду. Для работы скрипта необходимо добавить в дефолтный манифест секцию «executionApi», которая требуется для удаленного запуска скрипта через API. В данной секции указываем круг лиц, имеющих возможность запускать его. Я разрешил запуск для всех, кто прошел авторизацию, что соответствует идентификатору «ANYONE»:
Тело запроса на обновление должно содержать в себе массив файлов со следующей структурой:
- name: имя файла, который будет создан на сервере, без расширения
- type: тип файла (JSON для манифеста, SERVER_JS для .gs)
- source: код файла
Наконец, сам запрос на обновление должен содержать в себе тело (request, описанный выше) и ID скрипта. Последнее можно получить, пройдя в редакторе скрипта в «File» -> «Project Properties» и скопировав «Script ID»:
Однако, на данный момент запуск кода приведет к ошибке:
Как видим, не хватает разрешений в аутентификационном скопе, который мы указали ранее. Обратимся к официальной документации по API, а именно к методу updateContent, который мы использовали для удаленного обновления скрипта. Документация гласит, что использование этого метода требует включения разрешения доступа к script.projects:
Добавим его в нашу конфигурационный файл в секцию SCOPES. Как я уже писал выше, при изменении скопа, необходимо удалять автоматически создающийся токен.
Отлично! На данный момент мы научились удаленно обновлять гугл скрипт. Осталось его запустить и получить результат выполнения.
Serve HTML as a Google Docs, Sheets, Slides, or Forms user interface
The HTML service can display a dialog or sidebar in Google Docs, Sheets, Slides, or Forms if your script is container-bound to the file. (In Google Forms, custom user interfaces are only visible to an editor who opens the form to modify it, not to a user who opens the form to respond.)
Unlike a web app, a script that creates a user interface for a document, spreadsheet, or form does not need a doGet() function specifically, and you do not need to save a version of your script or deploy it. Instead, the function that opens the user interface must pass your HTML file as an HtmlOutput object to the showModalDialog()) or showSidebar() methods of the Ui object for the active document, form, or spreadsheet.
These examples include a few extra features for convenience: the onOpen() function creates a custom menu that makes it easy to open the interface, and the button in the HTML file invokes a special close() method of the google.script.host API to close the interface.
ШАГ 2. Тестирование скрипта
В данный момент работоспособность скрипта можно проверить следующим образом:
Внимание: Для удобства понимания происходящего, ниже я буду ссылаться только на две страницы, поэтому рекомендуется открыть их в соседних вкладках:
-
скриптов / редактирования скрипта — «страница 1» Google Cloud Platform — «страница 2»
Code.gs
Введение
У нас в «Доктор рядом» разрабатывается платформа для чат-ботов, в которой для описания сценариев используются гугл-формы. Соответственно, хочется из форм по нажатию кнопки получать JSON, содержащий ноды (пункты формы) и метаданные к ним (переходы между нодами, типы нод, их название). Казалось бы, желание простое, но гугл не поддерживает данный функционал и приходится собирать этот «экспортер» своими руками. Рассмотрим по шагам процесс его создания.
Add-ons for Google Sheets
Add-ons are specially packaged Apps Script projects that run inside Google Sheets and can be installed from the Google Sheets add-on store. If you've developed a script for Google Sheets and want to share it with the world, Apps Script lets you publish your script as an add-on so other users can install it from the add-on store.
Get started
Apps Script includes special APIs to let you programmatically create, read, and edit Google Sheets. Apps Script can interact with Google Sheets in two broad ways: any script can create or modify a spreadsheet if the script's user has appropriate permissions for the spreadsheet, and a script can also be bound to a spreadsheet, which gives the script special abilities to alter the user interface or respond when the spreadsheet is opened. To create a bound script, select Extensions > Apps Script from within Google Sheets.
The Spreadsheet service treats Google Sheets as a grid, operating with two-dimensional arrays. To retrieve the data from the spreadsheet, you must get access to the spreadsheet where the data is stored, get the range in the spreadsheet that holds the data, and then get the values of the cells. Apps Script facilitates access to the data by reading structured data in the spreadsheet and creating JavaScript objects for them.
Custom functions in Google Sheets
A custom function is similar to a built-in spreadsheet function like =SUM(A1:A5) except that you define the functions’s behavior with Apps Script. For example, you could create a custom function, in2mm() , that converts a value from inches to millimeters, then use the formula in your spreadsheet by typing =in2mm(A1) or =in2mm(10) into a cell.
To learn more about custom functions, try the Menus and Custom Functions 5-minute quickstart, or take a look at the more in-depth guide to custom functions.
ШАГ 1. Google Apps Script
Google предусмотрел возможность взаимодействия со своими сервисами (Sheets, Docs, Forms) через Google Apps Script — скрипты, написанные на языке google script (.gs). Данная статья не предусматривает разбора языка google script, поэтому приведу пример готового скрипта, создающего JSON из имеющейся гугл-формы. За основу был взят с гитхаба код пользователя Steven Schmatz, за что выражаю ему свою благодарность.
Что происходит в коде:
- функция getFormMetadata — возвращает JSON с метаданными формы
- функция itemToObject — преобразует объект form.item в JSON с требуемыми полями
- функция sendEmail — отправляет текстом JSON файл на указанную почту
- функция main — возвращает итоговый JSON
- переменная form_url в функции main — адрес нашей гугл-формы
Create HTML files
New editor
To view the data that has been logged, at the top of the script editor, click Execution log.
Connecting to Google Forms
Apps Script allows you to connect Google Forms with Google Sheets through Forms and Spreadsheet services. This feature can automatically create a Google Form based on data in a spreadsheet. Apps Script also enables you to use triggers, such as onFormSubmit to perform a specific action after a user responds to the form. To learn more about connecting Google Sheets to Google Forms, try the Managing Responses for Google Forms 5-minute quickstart.
List existing projects
To list all Apps Script projects in your Drive, use the Files resource to query for files with the MIME type of application/vnd.google-apps.script . To filter the response to only include files you own, include the search parameter 'me' in owners .
Here is a sample request and response that shows an array of Apps Script projects returned through a JSON response.
If you know the file ID of an Apps Script project, you can directly fetch it with the following API call:
Macros
Macros are another way of executing Apps Script code from the Google Sheets UI. Unlike custom functions, you activate them with a keyboard shortcut or through the Google Sheets menu. For more information, see Google Sheets Macros.
Authorization
All requests to the Google Drive REST API must be authorized by an authenticated user through the OAuth 2.0 protocol. For more details, please refer to the Google Drive REST API authorizing documentation.
Custom menus and user interfaces
You can customize Google Sheets by adding custom menus, dialog boxes, and sidebars. To learn the basics of creating menus, see the guide to menus. To learn about customizing the content of a dialog box, see the guide to HTML service.
You can also attach a script function to an image or drawing within a spreadsheet; the function will execute when a user clicks on the image or drawing. To learn more, see Images and Drawings in Google Sheets.
If you're planning to publish your custom interface as part of an add-on, follow the style guide for consistency with the style and layout of the Google Sheets editor.
Serve HTML as a web app
To create a web app with the HTML service, your code must include a doGet() function that tells the script how to serve the page. The function must return an HtmlOutput object, as shown in this example.
New editor
To add an HTML file to your Apps Script project, follow these steps:
- Open the Apps Script editor.
- At the left, click Add a file add >HTML.
Читайте также: