Как написать скрипт в гугл таблицах

Google Apps Script Quickstart

Complete the steps described in the rest of this page to create a simple Google Apps Script that makes requests to the Google Sheets API.

Prerequisites

To run this quickstart, you need the following prerequisites:

• A Google account

• Access to Google Drive

Step 1: Create the script

1. Create a new script by going to script.google.com/create.
2. Replace the contents of the script editor with the following code:

New editor

1. Click Save .
2. Click Untitled project at the top left, type Quickstart, and click Rename.

Legacy editor

1. Click File > Save, name your project “Quickstart”, and click OK.

Step 2: Turn on the Google Sheets API

Step 3: Run the sample

New editor

In the Apps Script editor, click Run.

The first time you run the sample, it will prompt you to authorize access:

1. Click Review permissions.
2. Choose an account.
3. Click Allow.

The script's execution log appears at the bottom of the window.

Legacy editor

In the Apps Script editor, click Run > logNamesAndMajors.

The first time you run the sample, it will prompt you to authorize access:

1. Click the Continue button.
2. Click the Accept button.

To view the script's output, click View > Logs.

Further reading

Troubleshooting

This section describes some common issues that you may encounter while attempting to run this quickstart and suggests possible solutions.

ReferenceError: «[API NAME]» is not defined

This error occurs when the API hasn't been toggled on in the Apps Script code editor. Revisit Step 2.b and ensure the corresponding toggle is set to on.

This app isn’t verified

If the OAuth consent screen displays the warning "This app isn't verified," your app is requesting scopes that provide access to sensitive user data. If your application uses sensitive scopes, your app must go through the verification process to remove that warning and other limitations. During the development phase, you can continue past this warning by selecting Advanced > Go to (unsafe).

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.

Скрипты Google Таблиц 101 — руководство для начинающих

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

С помощью скриптов Google Sheets вы можете автоматизировать многие вещи и даже создавать новые функции, которые, по вашему желанию, существовали.

В этой статье я расскажу об основах Google Apps Script с некоторыми простыми, но практичными примерами использования скриптов в Гугл Таблицах.

Что такое скрипт Google Apps (GAS)?

Скрипт Google Apps — это язык программирования, который позволяет вам создавать автоматизацию и функции для Google Apps (которые могут включать Google Таблицы, Google Документы, Google Формы, Диск, Карты, Календарь и т. Д.)

В этом руководстве (и на этом сайте) я сосредоточусь на использовании скрипта для Google Таблиц. Однако большую часть того, о чем я здесь расскажу, можно использовать и в других приложениях Google.

Этот язык кодирования Google Apps Script (GAS) использует Javascript и написан в серверной части этих Гугл Таблиц (есть аккуратный интерфейс, который позволяет вам писать или копировать / вставлять код в серверной части).

Поскольку Гугл Таблицы (и другие Google Apps) являются облачными (т. Е. Могут быть доступны из любого места), ваш скрипт Google Apps также является облачным. Это означает, что если вы создадите код для документа Google Sheets и сохраните его, вы сможете получить к нему доступ из любого места. Он находится не на вашем ноутбуке / системе, а на облачных серверах Google.

Что делает скрипт Google Apps полезным?

Есть много веских причин, по которым вы можете захотеть использовать скрипты Google Apps в Google Таблицах:

Позволяет автоматизировать работу

Предположим, вы регулярно загружаете данные из любого инструмента или базы данных и должны объединять и очищать их в Google Таблицах. Обычно очистка и объединение данных включает в себя ряд шагов.

Это может не иметь большого значения, если вам нужно делать это всего несколько раз, но если вам приходится делать это довольно часто, автоматизация этих шагов может сэкономить вам много времени.

И это то, что вы можете делать с помощью скрипта Google Apps.

Все, что вам нужно сделать, это потратить некоторое время на то, чтобы подготовить код один раз, и всякий раз, когда вам нужно повторить те же шаги снова, вы просто запускаете код сценария в Google Sheets и позволяете GAS делать всю тяжелую работу за вас.

Может создавать новые функции в Google Таблицах

В Гугл Таблицах уже есть сотни потрясающих функций, и в большинстве случаев этих функций должно быть достаточно.

Но в некоторых случаях вам могут понадобиться некоторые дополнительные функции, которые не могут быть достигнуты с помощью встроенной функции (или это может быть, но формула становится огромной и сложной).

В таких случаях вы можете быстро написать код GAS для создания пользовательской функции. Эти функции можно использовать так же, как обычные функции в документе Google Таблиц, и они мгновенно облегчают вашу работу.

Может взаимодействовать с другими приложениями Google

Поскольку скрипт Google Apps является распространенным языком программирования для многих приложений Google, вы также можете использовать его для взаимодействия с другими приложениями.

Например, если у вас есть 10 документов Google Таблиц на вашем Google Диске, вы можете использовать GA, чтобы объединить все это, а затем удалить все эти документы Google Sheets.

Это возможно, потому что вы можете использовать GAS для работы с несколькими Google Apps.

Другим полезным примером этого может быть использование данных в Гугл-таблицах для быстрого планирования напоминаний в вашем Календаре Google. Поскольку оба этих приложения используют GAS, это возможно.

Расширьте функциональные возможности Google Таблиц

Помимо автоматизации вещей и создания функций, вы также можете использовать GAS для улучшения функциональности Google Таблиц.

Хотя Гугл Таблицы предоставляют множество функций для всех, вы также можете написать код, который больше подходит для ваших нужд. А поскольку вы можете повторно использовать код снова и снова, это делает вас намного более продуктивным и эффективным.

Начало работы с редактором скриптов Google Таблиц

Редактор скриптов — это место, где вы можете писать скрипты в Google Таблицах, а затем запускать их. Для разных приложений Google будет отдельный редактор сценариев. Например, в случае Google Forms будет «Редактор сценариев», в котором вы можете писать и выполнять код для форм Google.

Анатомия редактора скриптов Google Таблиц

В Google Таблицах вы можете найти редактор скриптов на вкладке «Инструменты».

После того, как вы нажмете на опцию «Редактор скриптов», откроется редактор скриптов в новом окне (как показано ниже).

Вы можете изменить имя проекта, щелкнув в верхней левой части экрана с надписью «Проект без названия». Когда вы щелкаете по нему, откроется диалоговое окно, в котором вы можете ввести название проекта. Изменение имени займет несколько секунд.

В проекте сценария может быть несколько файлов сценария. Например, если у вас есть три разные вещи, которые вы хотите автоматизировать в Google Таблицах, и вы создаете для них три разных скрипта, у вас могут быть все три разных скрипта в одном файле проекта.

На левой панели проекта у вас есть файл сценария по умолчанию — Code.gs. В этом файле сценария вы можете писать код. В одном файле сценария может быть несколько сценариев, а также несколько файлов сценариев.

Если вы щелкните маленькую направленную вниз стрелку справа от имени файла сценария, отобразятся варианты переименования, удаления и создания копии файла сценария.

Примечание. В проекте всегда должен быть хотя бы один файл сценария. Если у вас есть только один, он не позволит вам удалить его.

В правой части файла сценария находится окно кода, в котором вы можете написать код.

Панель инструментов редактора скриптов

Панель инструментов редактора скриптов имеет следующие параметры:

• Кнопка «Вернуть / Отменить» : для возврата / отмены изменений, которые вы сделали в скрипте.
• Кнопка отступа: это кнопка-переключатель, и вы можете включить или отключить отступ, нажав на нее. Когда отступы включены, они автоматически делают отступы для некоторых частей вашего скрипта, чтобы сделать его более читабельным. Это может иметь место, когда вы используете циклы или операторы IF. Он будет автоматически делать отступы для наборов кодов внутри цикла, чтобы повысить удобочитаемость (если отступы включены). Этот параметр включен по умолчанию, и я рекомендую вам оставить его в таком же виде.
• Кнопка «Сохранить» : вы можете использовать эту кнопку, чтобы сохранить любые изменения в вашем скрипте. Вы также можете использовать сочетание клавиш Control + S. Обратите внимание, что, в отличие от Google Таблиц, вам необходимо сохранить свой проект, чтобы убедиться, что изменения не потеряны.
• Кнопка текущего триггера проекта : при нажатии на эту кнопку откроется панель управления триггерами, на которой перечислены все триггеры, которые у вас есть. Триггер — это все, что запускает выполнение кода. Например, если вы хотите, чтобы код запускался и вводил текущую дату и время в ячейку A1 всякий раз, когда кто-то открывает Google Таблицы, вы будете использовать для этого триггер.
• Кнопка «Выполнить» : используйте ее для запуска сценария. Если у вас несколько функций, выберите любую строку в той, которую вы хотите запустить, а затем нажмите кнопку «Выполнить».
• Кнопка отладки : отладка помогает находить ошибки в коде, а также дает некоторую полезную информацию. Когда вы нажимаете кнопку «Отладка», на панели инструментов отображаются некоторые дополнительные параметры, связанные с отладкой.
• Выберите функцию : это раскрывающийся список, в котором перечислены все ваши функции в файле сценария. Это полезно, когда у вас много функций в скрипте и вы хотите запустить конкретную. Вы можете просто выбрать имя здесь, а затем нажать кнопку запуска (или отладить его, если хотите).

Параметры меню редактора скриптов

Помимо панели инструментов, есть много других опций, доступных в Google Apps Script в Google Таблицах.

Хотя наиболее часто используемые параметры уже являются частью панели инструментов, в меню есть еще несколько параметров, которые могут вам понадобиться, когда вы начнете работать с GAS.

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

• ФАЙЛ : из меню «Файл» вы можете добавить новый проект или файл сценария. Проект будет полностью новым проектом в отдельном окне, где вы можете создать дополнительные файлы сценариев. Когда вы добавляете новый файл сценария, он просто добавляет его в тот же проект (вы увидите его на левой панели под текущими файлами сценария). Вы также можете переименовывать и удалять проекты отсюда. Еще одна полезная опция, которую вы можете найти в меню «Файл», — это возможность управлять версиями проектов. Когда вы сохраняете проект, сохраняется его версия, и вы можете вернуться и вернуться к этой версии, если хотите.
• РЕДАКТИРОВАТЬ : Edit имеет несколько полезных опций, которые могут помочь при написании или редактировании кода. Например, есть возможность найти и заменить текст в вашем коде. Есть также такие опции, как Завершение слов, Помощник по содержанию и Переключить комментарии.
• ПРОСМОТР : у этого есть параметры, которые могут быть полезны, когда вы хотите получить дополнительную информацию о скрипте, когда он выполняется, или хотите добавить журналы, чтобы помочь в отладке в будущем. Например, вы можете получить стенограмму выполнения, в которой подробно описаны все действия, выполняемые вашим скриптом.
• ВЫПОЛНИТЬ : Есть варианты для запуска различных функций или их отладки. Поскольку эти параметры также доступны на панели инструментов, их реже можно использовать из меню.
• ПУБЛИКАЦИЯ : здесь есть более продвинутые функции, такие как публикация ваших скриптов в виде веб-приложений.
• РЕСУРСЫ: это дает вам доступ к расширенным параметрам, таким как библиотеки и расширенные службы Google. Вы можете использовать эти параметры для подключения к другим ресурсам Google, таким как Google Forms или Docs.
• СПРАВКА : Здесь есть учебные пособия и ресурсы, которые могут помочь вам, когда вы начинаете / работаете со скриптами Google Apps. Одним из наиболее полезных вариантов здесь является ссылка на страницу документации, где вы можете найти множество руководств и ссылок для изучения скриптов Google Apps.

В этой статье я рассмотрел основы скрипта Google Apps и общую анатомию интерфейса.

Пишем скрипты для автоматизации работы с приложениями Google

Google Apps Script — это язык для автоматизации работы с онлайн-приложениями, появившийся в 2009 году. Его основа — классический JavaScript, обогащенный расширениями для работы с сервисами Google. После прочтения этой статьи ты овладеешь основами использования этого языка, выучишь пару приемов манипуляции с почтой и документами, а также получишь представление о необозримых возможностях Google Apps Script.

Основы использования

Начать писать Google Apps скрипты очень просто. Первым делом надо открыть редактор скриптов в любом приложении, взаимодействие с которым будем автоматизировать. В Google Docs он находится в меню «Инструменты -> Редактор скриптов». Далее надо выбрать проект, внутри которого будет располагаться скрипт (см. рис. 1). В открывшемся окне пишем код:

Рис. 1. Стартовое окно редактора скриптов
Другие статьи в выпуске:

Хакер #187. Обходим Blizzard Warden

Декларированную функцию можно запускать из «Инструменты -> Управление скриптами» или сделать для нее отдельную кнопку. Для этого надо прописать еще одну функцию:

Теперь в нашем меню есть пункт под названием ExampleFunc, при клике на который открывается однострочное подменю «Моя единственная функция».

Декларированные функции можно использовать в формулах, которые вводятся внутрь ячеек электронных таблиц (см. рис. 3). Теперь перейдем к более практически полезным примерам.

Рис. 2. Редактор кода, заполненный автогенерируемым сырьевым материалом для работы с электронной таблицей Рис. 3. Вызов пользовательской функции с аргументом

WARNING

Перед запуском скрипта не забудь сделать резервную копию важной информации. Действия, выполненные GS, нельзя отменить нажатием <Ctrl + Z>.

Работа с Google Docs

Когда встает задача автоматизации работы с офисными документами, первым делом на ум приходит VBA, одно упоминание которого оказывает на многих тотальное антиэкстатическое воздействие, вызывая болезненные воспоминания из школьного и университетского прошлого. Google Script однозначно удобнее и доступнее для понимания. Особенно для веб-разработчиков, ведь это же родной, привычный и любимый JS! Разберем пару примеров скриптов для Google Docs. Приведенный код заполняет левую верхнюю ячейку первого листа активной таблицы:

А этот код создает копию текстового документа и кладет его в определенное место:

А вот так можно провести замену строк в текстовом документе:

Следующий пример кода подсвечивает определенные слова в тексте:

Рис. 4 Вот здесь можно узнать ID документа

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

Работа с почтой

Письмо отправляется одной короткой строкой: MailApp.sendEmail(«irairache@gmail.com», «тема письма», «текст письма») Если добавить к ней еще немного кода, то можно организовать рассылку по списку адресов из электронной таблицы (исходник ищи в приложении):

Помимо рассылок, с помощью GS можно производить автоматизированную обработку содержимого почтового ящика. Пример — удаление всех писем от адресата, на которого ты обиделся:

WARNING

Имей в виду, что Gmail не только защищает от входящего спама, но и ограничивает рассылку исходящего. Больше 500 писем за сутки с помощью Google Apps Script не выйдет.

Работа с Google Translate

С помощью Google Apps Script можно переводить текстовые строки с одного языка на другой. Пример:

Коды для языков можно посмотреть в адресной строке сервиса Google Translate.

Если выделить код и нажать <Shift + Tab>, то магическим образом расставятся все отступы для условий, циклов, объявленных функций и всех других мест, где им положено быть.

Работа с Google Drive

Google Apps Script может работать с файлами пользователя, размещенными на Google Drive. Этот скрипт выводит в консоль имена всех файлов пользователя:

К файлам можно применять несколько десятков различных методов. Вот некоторые из них:

• addEditor(«email пользователя») — наделяет пользователя правами на редактирование файла;
• getOwner() — узнать владельца файла;
• makeCopy(«имя», «путь») — создать копию файла;
• getLastUpdated() — возвращает пользователя, который внес последнее изменение.

Работа с Google Contacts

Адресная книга также может быть подвергнута автоматизированной обработке. Приведенный ниже код копирует все контакты из группы «Редакция» в лист Google Spread Sheet:

Работа с Google Tasks

С помощью Google Apps Scripts можно работать с сервисом Google Task — создавать новые задачи и парсить уже имеющиеся.

Этот код создает новое дело в списке:

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

Задачи можно перемещать из одного списка в другой с помощью метода move, дополнять с помощью метода update и удалять с помощью метода delete.

Всего есть несколько десятков методов для работы с задачами. Полный их список доступен вGoogle Apps Script References для Google Tasks.

Работа с календарем

Создавать события в календаре тоже можно автоматически (и так же, как в случае с рассылкой, формировать информацию о них из строк таблицы). Код для создания события:

Формы обмена скриптами

Есть два основных способа поделиться своим скриптом с другим человеком (без учета непосредственного обмена исходным кодом) — ссылка и гаджет. В первом случае все просто: пользователь получает ссылку на программу, переходит по ней, и скрипт немедленно начинает выполняться (при условии, что человек авторизирован в своем Google-аккаунте).

Гаджет — это приложение-контейнер, которое размещается на веб-странице и исполняет определенные функции. Примеры: мини-блок в углу страницы с прогнозом погоды или календарем. Чтобы поместить Google Script внутрь гаджета, необходимо в меню редактора скриптов выбрать пункт «Publish -> Deploy as web app».

А если нажать <Ctrl + Space>, то включится режим автозавершения, то есть редактор будет дописывать код за тебя.

События

Можно настроить скрипт так, чтобы он выполнялся после определенного события. К примеру, после открытия/редактирования электронной таблицы или отправки данных формы. Подробности о работе с Events

Работа с базами данных

Для этого существует сервис для работы с базами данных Google Cloud SQL. По сути — классический MySQL в облаке. Может взаимодействовать с Google Apps Script по стандарту Java Database Connectivity. Вот пример кода, который производит чтение записей из таблицы:

Стоимость использования сервиса — 88 долларов в год за 10 Гб свободного места. С другими базами данных Google Apps Script, к сожалению, работать не может. Если ты запланировал написать скрипт, который должен взаимодействовать с данными, не стоит сразу расстраиваться или истощать свой бюджет пожертвованиями на закупку квадроциклов для жителей Кремниевой долины. Есть два способа выкрутиться из этой ситуации:

• первый — хранить данные в виде таблиц на Google Drive;
• второй — разместить базу на стороннем сервере, на нем же разместить <и т. д.>скрипт, который будет выполнять к ней запрос и возвращать ответ в формате JSON или XML, а его, в свою очередь, подвергать парсингу внутри GS. Подробности о Google Cloud SQL

Приложения, с которыми может взаимодействовать Google Apps Script

Я думаю, что далеко не каждый из наших читателей успел опробовать все онлайн-сервисы Google. В целях расширения кругозора и стимуляции творческого воображения приведем краткий обзор возможностей приложений, работу которых можно автоматизировать с помощью Google Apps Script.

• Google Mail — почтовый клиент. Наиболее интересна в нем возможность отправлять письма людям, адреса которых неизвестны. Для этого надо включить настройку «Отправлять письма пользователям Google+» и ввести имя и фамилию получателя в поле «Кому».
• Google Calendar — органайзер. Самое удобное в нем — возможность отправки SMS-уведомлений о событиях на номера российских операторов.
• Google Contacts — приложение для хранения контактов. Самый цимес его в том, что если ты случайно синхронизируешь свой список контактов с новым смартфоном и все имена сотрутся, то сможешь попросить у Google резервную копию предыдущей версии, которая навечно сохранена в его архивах.
• Google Drive — облачное хранилище данных. 15 Гб, на которых также размещаются данные всех других приложений, доступны бесплатно.
• Google Maps — онлайн-карты. Жителям СНГ повезло, у них есть альтернативный инструмент для построения маршрутов и просмотра панорам улиц — Яндекс.Карты. Для жителей большинства других территорий альтернатив нет. Google Maps — единственная всемирная картографическая система, позволяющая искать населенные пункты, вводя названия на языке государства, в котором они находятся. Допустим, не Kotlas, а Котлас, не Vagharshapat, а Վաղարշապատ.
• Google Docs — онлайн-редактор офисных документов. Во время написания статьи этот сервис совершил мега-прорыв — появилась возможность редактировать документы, созданные в Microsoft Office. Это произошло после интеграции сервиса с функционалом приложения Quickoffice. Ради интереса попробовала отредактировать в Google Docs пояснительную записку к диплому (как пример документа с простейшим форматированием). Преобразование docx в гугл-формат пришлось ждать около минуты, и внешний вид текста явно отличался от оригинала.
• Google Forms позволяет создавать формы для сбора различных данных (онлайн-опрос, страницу регистрации на событие, обратную связь для сайта и прочее), которые можно привязать к таблицам в различных форматах (HTML, CVS, TXT, PDF, RSS, XLS, ODF). Собранные данные хранятся на Google Drive.
• Google Sites — бесплатный хостинг (100 Мб) с предельно ограниченным функционалом и собственной wiki-разметкой. Полнофункциональный HTML, а также CSS и JS недоступны.

По старой доброй традиции, которой уже почти три месяца, мы выложили несколько исходничков на GitHub:

• autodeletemail.gs — удаляет письма, с момента получения которых прошло n-ное количество дней;
• snoozeemails.gs — скрипт для повторной отправки самому себе прочитанных писем через определенный промежуток времени;
• sendsmsaboutemails.gs — настраивает отправку SMS в случае получения писем, соответствующих определенным критериям. Перед его использованием надо указать свой номер телефона Google Calendar;
• savemailtopdfindrive.gs — сохраняет содержимое письма в файлах на Google Drive;
• fromcalendartospreadsheet.gs — записывает информацию из календаря в электронную таблицу;
• sendmailsfromspreadsheet.gs — рассылает письма по списку адресов из электронной таблицы;
• createdocsfromspread.gs — генерирует текстовые документы из данных электронной таблицы.

Advanced Google Services

У Google есть множество API для разработчиков, которые можно внедрять в программы, написанные на Google Apps Script. Для этого надо подключить в редакторе скриптов эту возможность (в меню Resources, далее Advanced Google services). После этого можно будет задействовать возможности следующих сервисов: