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

Как написать свой первый Google Apps script

Платформа Google Apps Script предназначена для разработки веб-приложений, и позволяет интегрировать в них все сервисы Google Cloud.

Обновлено: 2023-06-11 19:26:45 Наталья Кайда автор материала

Что можно сделать с помощью приложения Google Script?

Вот лишь несколько примеров того, что умеет Google Script:

• Создавать пользовательские функции в Google Таблицах.
• Интегрировать Google-таблицы или Google Docs с почтовой службой Gmail.
• Создавать веб-приложения, которые можно развернуть с помощью Google Sites.
• Добавлять пользовательское меню в Google Docs.
• Создавать панели мониторинга трафика в Google Таблицах с использованием данных, получаемых от Google Analytics;
• Отправлять электронную почту из Google Таблиц или любой другой службы Google.

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

Написание первого Google Apps script

Ваш первый скрипт будет отправлять электронное письмо из аккаунта Google с сообщением, встроенным в Google Script. Для его написания:

1. Перейдите в браузере по адресу google.com .
2. Войдите в свой аккаунт Google.
3. Нажмите кнопку « Создать скрипт».
4. В поле « Проект без названия» вводим « Мой первый скрипт» .

Удаляем код, который отображается в редакторе, и вставляем в него следующий:

function SendAnEmail() < // Задает email получателя var email = 'xxxxx@yahoo.com' // Создать тему email. var subject = 'This is my first script!'; // Создать текст email. var body = 'Hello, world!'; // Отправить email GmailApp.sendEmail(email, subject, body); >

Нажимаем « Сохранить» . Затем кликаем по иконке « Выполнить», чтобы запустить код. Затем предоставьте приложению разрешение на выполнение скрипта и отправку писем.

Также может появиться предупреждение о том, что приложение не проверено. Просто кликните по ссылке « Дополнительные настройки » и перейдите на страницу « Мой первый скрипт (небезопасно)» . Вот так будет выглядеть входящее письмо:

Данный скрипт использовал почтовую службу Gmail для отправки электронного письма из вашего аккаунта с помощью Google Apps Script.

Это простой пример того, как Google Apps Script может подключиться к любой из облачных служб Google.

Добавление других функций к службам Google

К Google Script можно получить доступ из многих других служб. Это позволяет расширить возможности данных сервисов. Например, в Google-таблицах можно получить доступ к редактору скриптов Google, перейдя в меню « Инструменты» — « Редактор скриптов» .

Также можно использовать Google Apps Script для создания надстройки Gmail, чтобы повысить удобство работы с электронной почтой: изменить окно сообщений, написать скрипт, который будет архивировать старые письма. Для этого необходимо активировать надстройки разработчика в вашем аккаунте Gmail. Переходим в « Настройки» , « Дополнения» и выбираем пункт « Разрешить устанавливать дополнения для разработчиков в этом аккаунте». Далее во всплывающем окне нужно кликнуть по кнопке « Включить».

Создание дополнений для Gmail выходит за рамки этой статьи. Но если вы хотите заняться этим, можно изучить «Руководство разработчика Google по созданию дополнений Gmail».

Получение доступа к Google Script API

Из редактора Google Script можно получить доступ к каждой из служб Google. В приведенном выше примере приложения «Hello World» в качестве глобального объекта мы использовали приложение GmailApp.

Для получения доступа ко всем методам и вызовам, которые можно совершать из Google Script, необходимо включить расширенные службы Google. В редакторе скриптов перейдите в меню « Ресурсы» — « Дополнительные функции Google» .

Не забудьте перейти по ссылке « Панель инструментов платформы API Google Cloud» и активировать службы на этой панели инструментов.

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

Внизу страницы, на которой вы включили API Library, есть ссылка на справочную документацию . В ней приводятся примеры и синтаксис интеграции API в приложение Google Scripts.

Просмотр библиотеки API Library показывает, какое количество сервисов вы можете интегрировать в свои скрипты.

Что дальше?

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

Google Apps Script: переносим расписание из таблицы в календарь

Это можно сделать, перейдя на страницу своего Google Drive.

Рис. 1. Правый клик мышью по рабочему пространству открывает контекстное меню, где нужно выбрать «Google Таблицы» — «Создать пустую таблицу»

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

• Название мероприятия
• Дата мероприятия
• Время начала
• Время конца или длительность (впрочем, это необязательно — в итоге вы сможете установить какое-нибудь общее дефолтное значение длительности мероприятия, например 1 час, или вовсе сделать мероприятие, длящимся целый день)

Вся остальная информация, в моем случае — преподаватель, тип занятия (лекция, семинар и т.д.), может быть отправлена в описание мероприятия.

Рис. 2. Таблица с расписанием

2. Создаем скрипт

2.1. Переходим в редактор скриптов

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

Рис. 3. Скрипт создается, через меню «Инструменты», в котором нужно выбрать пункт «Редактор скриптов»

Скрипты разрабатываются на языке JavaScript. Только что созданный скрипт содержит одну пустую функцию myFunction. Писать код можно в ней, но я предпочту дать ей более осмысленное имя SetCalendar.

Рис. 4. Созданный скрипт, дадим функции осмысленное имя — SetCalendar

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

2.2. Разбираемся с отладкой

Часто для отладки скриптов на JavaScript мы выводим различные значения в консоль:

console.log("Hello, world!")

В Google Apps Script логировать значения следует немного иначе. Вместо объекта console следует использовать глобальный объект Logger:

function SetCalendar()

После запуска скрипта, все выведенные в лог значения можно посмотреть из меню «Вид», выбрав пункт «Журналы» или же по сочетанию клавиш Ctrl+Enter:

Рис. 5. Модальное окно с логами последнего запуска скрипта

2.3. Извлекаем информацию о мероприятиях из таблицы

Сразу небольшой кусок кода

function SetCalendar() < //Индексы первой строки и первого столбца в таблице с данными const rowStart = 1; const colStart = 1; //Количество строк и столбцов в расписании const colsCount = 5; const rowsCount = 67; //Получаем объект страницы var sheet = SpreadsheetApp.getActiveSheet(); //Извлекаем данные таблицы в указанных диапазонах var range = sheet.getRange(rowStart, colStart, rowsCount, colsCount) var data = range.getDisplayValues(); >

Рассмотрим строку 11.

Глобальный объект SpreadsheetApp — глобальный объект, содержащий методы, представляющие собой интерфейс для взаимодействия скрипта с приложением Google Sheets.

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

Метод возвращает объект sheet.

Далее, нужно получить объект range — он представляет собой диапазон ячеек таблицы, из которых мы будем извлекать данные. Это делается путем вызова метода getRange. В качестве параметров передаются:

• Индекс первой строки диапазона
• Индекс первого столбца диапазона
• Количество извлекаемых строк
• Количество извлекаемых столбцов

Обратите внимание! Индексация строк и столбцов начинается с 1.

Строка 15: мы получаем из выбранного выше диапазона ячеек непосредственно данные путем вызова метода getDisplayValues.

Обратите внимание! Метод getDisplayValues() отдает данные в том виде, в каком он отображается в ячейках таблицы. Например, если ячейка содержит дату, то указанный метод вернет не какое-то внутреннее представление записанной в ячейку даты, а ее представление, которое видит пользователь в ячейке.

2.4. Извлекаем данные из ячеек

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

//Константы-имена для индексов столбцов const dateCol = 0; const timeCol = 1; const typeCol = 2; const nameCol = 3; const teacherCol = 4;

Вы можете посмотреть какие столбцы стоят в таблице под данными номерами на рисунке 2.

Объект data представляет набор ячеек с данными, которые были получены. Теперь нужно получить данные из отдельных ячеек.

 for (var i in data) < let row = data[i]; let classDate = row[dateCol]; let classPeriod = row[timeCol]; let classType = row[typeCol]; let className = row[nameCol]; let classTeacher = row[teacherCol]; Logger.log("[DATE] " + classDate); Logger.log("[PERIOD] " + classPeriod); Logger.log("[TYPE] " + classType); Logger.log("[NAME] " + className); Logger.log("[TEACHER] " + classTeacher); Logger.log("=========================================== 25-rabota-s-obedinennymi-yacheykami">2.5. Работа с объединенными ячейками 
В результате выполнения предыдущих шагов, мы получаем следующий скрипт
 
function SetCalendar() < //Индексы первой строки и первого столбца в таблице с данными const rowStart = 1; const colStart = 1; //Количество строк и столбцов в расписании const colsCount = 5; const rowsCount = 8; //Получаем объект страницы var sheet = SpreadsheetApp.getActiveSheet(); //Извлекаем данные таблицы в указанных диапазонах var range = sheet.getRange(rowStart, colStart, rowsCount, colsCount) var data = range.getDisplayValues(); //Константы-имена для индексов столбцов const dateCol = 0; const timeCol = 1; const typeCol = 2; const nameCol = 3; const teacherCol = 4; for (var i in data) < let row = data[i]; let classDate = row[dateCol]; let classPeriod = row[timeCol]; let classType = row[typeCol]; let className = row[nameCol]; let classTeacher = row[teacherCol]; Logger.log("[DATE] " + classDate); Logger.log("[PERIOD] " + classPeriod); Logger.log("[TYPE] " + classType); Logger.log("[NAME] " + className); Logger.log("[TEACHER] " + classTeacher); Logger.log("=========================================== https://habrastorage.org/r/w1560/webt/at/ja/p0/atjap0smhmy37otnfzgzpx30_pw.png" data-src="https://habrastorage.org/webt/at/ja/p0/atjap0smhmy37otnfzgzpx30_pw.png"/> 
Рис. 6. Не все даты были правильно извлечены из таблицы
 
Из лога видно, что из первой строки данные были извлечены, а в следующих строках, которые описывают мероприятия того же дня, поле с датой оказалось пустым. Почему так происходит?
 
Еще раз взглянем на таблицу:
 
 
Рис. 7. Даты вписаны в объединенные ячейки
 
При создании таблицы, дата вписывалась только в первую строчку расписания на конкретную дату. После чего, все ячейки из столбца, относящиеся к одной дате были объединены.





 
Когда мы двигаемся по строкам таблицы, мы получаем дату не из всех ячеек, которые были объединены в одну, а только из первой.
 
Немного изменим алгоритм, чтобы корректно извлекать дату из объединенных ячеек:
 
 let savedDate = ""; for (var i in data) < let row = data[i]; let classDate = row[dateCol]; //. if (classDate.trim() == "") < classDate = savedDate; >else < savedDate = classDate; >Logger.log("[DATE] " + classDate); //. Logger.log("=========================================== 26-parsim-datu-i-vremya-iz-strok">2.6. Парсим дату и время из строк 
Как было отмечено выше — если мы используем метод getDisplayValues(), то значения из таблицы мы получаем в виде строк и ровно так, как они отображаются в ячейках, никаких внутренних представлений данных мы не получаем.
 
Как будет показано далее, чтобы создать событие в Google Calendar, нужно предварительно создать два объекта класса Date: дату-время начала события, дату-время окончания события.
 
В рассматриваемой таблице (см. рисунок 2), мы получаем дату занятия в формате dd.mm.yyyy, и временной промежуток, когда занятие длится, в формате hh:mm-hh.mm.
 
Из этих двух строк, нам нужно получить два объекта Date. Такая задача не касается Google Apps Script, с этим по идее должен справиться хоть сколько-нибудь опытный программист. Поэтому я не буду подробно останавливаться на алгоритме, а лишь приведу код двух JS-функций, которые парсят дату время и возвращают дату-время начала и дату-время окончания занятия.
 
Поехали:
 
function extractTime(timeStr, dateStr) < let sepIdx = timeStr.indexOf(":"); let hoursStr = timeStr.substring(0, sepIdx); let minsStr = timeStr.substring(sepIdx + 1); sepIdx = dateStr.indexOf("."); let dayStr = dateStr.substring(0, sepIdx); let monthStr = dateStr.substring(sepIdx + 1, sepIdx + 3); sepIdx = dateStr.indexOf(".", sepIdx + 1); let yearStr = dateStr.substring(sepIdx + 1); let t = new Date(); t.setHours(parseInt(hoursStr), parseInt(minsStr)); t.setYear(parseInt(yearStr)); t.setMonth(parseInt(monthStr) - 1, parseInt(dayStr)); return t; >function extractPeriod(periodStr, dateStr) < let sepIdx = periodStr.indexOf("-"); let fromStr = periodStr.substring(0, sepIdx); let toStr = periodStr.substring(sepIdx + 1); fromStr = fromStr.trim(); toStr = toStr.trim(); return < from: extractTime(fromStr, dateStr), to: extractTime(toStr, dateStr) >>
Чтобы получить дату-время начала и окончания занятия, нужно вызывать функцию extractPeriod и передать две строки — дата и временной период, в который длится занятие.
2.7. Создание события в Google Calendar
Сразу небольшой листинг, пояснения будут дальше
let classTimeInfo = extractPeriod(classPeriod, classDate); let classStartTime = classTimeInfo.from; let classEndTime = classTimeInfo.to; let info = "Преподаватель: " + classTeacher + "\nТип занятия: " + classType; var event = (CalendarApp.getCalendarsByName("Учеба"))[0].createEvent ( className, classStartTime, classEndTime, < description: info >); Utilities.sleep(50);
Строки 1-6: получаем дату-время начала и окончания занятия, а также склеиваем в одну строку всю дополнительную информацию, которая будет записана в описание мероприятия в календаре.






Далее, к интерфейсу сервиса Google Calendar мы обращаемся через методы глобального объекта CalendarApp.
Посредством метода getCalendarsByName мы получаем массив календарей с указанным именем.
Получить объект-календарь можно также посредством метода getDefaultCalendar, тогда событие будет создано в основном календаре. Однако, я советую зайти в веб-версию Google Calendar и создать новый календарь, в который вы будете экспортировать мероприятия. Во-первых, вы получаете возможность скрывать события этого календаря, выделять события отдельным цветом.
Но, что более важно, в случае, если вы при написании скрипта где-то ошиблись, и экспортировали события как-то неправильно, то вместо того, чтобы удалять все многочисленные неправильные события вручную, вы можете просто удалить календарь, после чего создать новый, пустой, и попробовать запустить новый, исправленный скрипт.
В данном случае я создал календарь "Учеба", и собираюсь создавать события в нем. Поэтому в коде вызывается метод getCalendarsByName и в качестве параметра передается срока "Учеба".
Метод возвращает массив календарей с указанным названием, поэтому нужно взять из этого массива один элемент, в нашем случае нулевой:
(CalendarApp.getCalendarsByName("Учеба"))[0]
У объекта-календаря необходимо вызывать метод createEvent. В качестве параметров нужно передать следующее:

Название мероприятия (в данном случае название дисциплины)
Объект Data — дата-время начала мероприятия
Объект Data — дата-время окончания мероприятия
Необязательный параметр — объект с дополнительными опциями (в данном случае я заполняю одно поле — description — это описание мероприятия)

Последняя строка в скрипте — задержка на 50 миллисекунд. Во время разработки скрипта и отладки я обнаружил, что иногда при выполнении кода выбрасывается исключение, суть которого в том, что Google Calendar не нравятся частые обращения к сервису, и в описании исключения рекомендуется вставить задержку между вызовами методов API календаря.
3. Итоговый скрипт
Ниже приведен полный скрипт, который получился
Обратите внимание! Данный скрипт заточен под структуру таблицы, которая приведена на рисунке 2. Если вы хотите использовать этот скрипт для себя как есть, убедитесь, что ваша таблица с расписанием имеет такую же структуру, или же измените скрипт под свою таблицу.
function extractTime(timeStr, dateStr) < let sepIdx = timeStr.indexOf(":"); let hoursStr = timeStr.substring(0, sepIdx); let minsStr = timeStr.substring(sepIdx + 1); sepIdx = dateStr.indexOf("."); let dayStr = dateStr.substring(0, sepIdx); let monthStr = dateStr.substring(sepIdx + 1, sepIdx + 3); sepIdx = dateStr.indexOf(".", sepIdx + 1); let yearStr = dateStr.substring(sepIdx + 1); let t = new Date(); t.setHours(parseInt(hoursStr), parseInt(minsStr)); t.setYear(parseInt(yearStr)); t.setMonth(parseInt(monthStr) - 1, parseInt(dayStr)); return t; >function extractPeriod(periodStr, dateStr) < let sepIdx = periodStr.indexOf("-"); let fromStr = periodStr.substring(0, sepIdx); let toStr = periodStr.substring(sepIdx + 1); fromStr = fromStr.trim(); toStr = toStr.trim(); return < from: extractTime(fromStr, dateStr), to: extractTime(toStr, dateStr) >> function SetCalendar() < //Индексы первой строки и первого столбца в таблице с данными const rowStart = 1; const colStart = 1; //Количество строк и столбцов в расписании const colsCount = 5; const rowsCount = 8; //Получаем объект страницы var sheet = SpreadsheetApp.getActiveSheet(); //Извлекаем данные таблицы в указанных диапазонах var range = sheet.getRange(rowStart, colStart, rowsCount, colsCount) var data = range.getDisplayValues(); //Константы-имена для индексов столбцов const dateCol = 0; const timeCol = 1; const typeCol = 2; const nameCol = 3; const teacherCol = 4; let savedDate = ""; for (var i in data) < let row = data[i]; let classDate = row[dateCol]; let classPeriod = row[timeCol]; let classType = row[typeCol]; let className = row[nameCol]; let classTeacher = row[teacherCol]; if (classDate.trim() == "") < classDate = savedDate; >else < savedDate = classDate; >let classTimeInfo = extractPeriod(classPeriod, classDate); let classStartTime = classTimeInfo.from; let classEndTime = classTimeInfo.to; let info = "Преподаватель: " + classTeacher + "\nТип занятия: " + classType; var event = (CalendarApp.getCalendarsByName("Учеба"))[0].createEvent ( className, classStartTime, classEndTime, < description: info >); Utilities.sleep(50); > >
4. Немножко скриншотов







Что можно улучшить

Некоторые значения в коде я просто захардкодил — например, индексы начала таблицы, размеры таблицы, количество строк (количество мероприятий). Теоретически, можно покопаться в API Google Sheets для Google Apps Script и придумать, как выцеплять нужные координаты ячеек автоматически;
Я никогда плотно не изучал JavaScript, могут быть различные стилистические и прочие недостатки в коде, возможно тот же парсинг даты-времени можно сделать красивее и элегантнее.

Открыт к конструктивной критике, буду рад, если укажете на недостатки, посоветуете что можно улучшить, а что изменить в статье.
Что читать дальше

Apps Script Overview
Apps Script: Calendar
Apps Script: Spreadsheet


javascript
google apps

How to: Google App Script
Сегодняшний пост прольет свет на то, как можно еще более эффективно использовать один из очень удобных инструментов для базовой аналитики — Google Sheets. 
Зачастую, аналитикам нужно часто и много работать с таблицами и создавать выборки по различным условиям. В работе с таблицами проблема часто заключается в том, что приходится обрабатывать и систематизировать большие объемы данных, хранящихся на разных листах. К примеру, вы ведете учет клиентов на одном листе таблицы, выгружая данные из сторонних сервисов, и вам хотелось бы, при обновлении ячеек менять данные на другом листе, используя возможности скриптов Google Sheets. Давайте посмотрим как легко и просто решить эту задачу.

Редактор скриптов
Если у вас есть Google аккаунт и таблицы с данными, загруженные в Google Sheets, то можно создавать скрипт для этой таблицы. Выберите таблицу, в которой нужно автоматизировать перенос информации с одного листа на другой, откройте её и выберете в меню «Инструменты» пункт «Редактор скриптов». Браузер переадресует вас на страницу Apps Script, где вы можете создавать и редактировать скрипты для таблицы.

Автоматизация переноса строк на другой лист
Итак, наш скрипт должен автоматически выполнять задачу переноса строки на другой лист. Для этого, мы создаем еще одну колонку в таблице, в которой можно будет поставить галочку для переноса строки и убрать её для отмены этого действия. Давайте разберемся, как именно это делается. 
При написании скрипта можно использовать функцию-триггер, которая срабатывает при выполнении определенного условия. Подробнее о функциях-триггерах вы можете прочитать в документации. В нашем скрипте мы используем функцию OnEdit(e), которая запускается при редактировании ячейки таблицы (включение и выключение галочки). Эту функцию мы вызовем в конце скрипта, а пока что, пропишем все, что в функции должно происходить.
function myScript(e) < // Задаем следующее условие для функции: нужно реагировать только на нажатие галочки в восьмой колонке на листе "Лиды-воронка". if (e.source.getActiveSheet().getName() == "Лиды- воронка" && e.range.getColumn() == 8) < // Сохраняем объекты исходного листа и листа назначения destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test'); sourceSheet = e.source.getActiveSheet(); // Очищаем лист назначения. При очистке, начинаем со второй строки, так как у нас в таблице есть заголовок. destSheet.getRange(2, 1, destSheet.getLastRow(), destSheet.getLastColumn()).clearContent(); //Перебираем все ячейки с галочками, ищем те ячейки, в которых галочки проставлены. range = sourceSheet.getRange(1, 1, sourceSheet.getLastRow(), sourceSheet.getLastColumn()); for (i = 2; i > > // Затем, вызываем функцию-триггер, которая будет вызывать наш скрипт при каждом редактировании ячейки. function onEdit(e)
В итоге, если галочка в столбце выделена, то соответствующая строка будет перенесена на другой лист. Также, важно помнить, что, при удалении галочки, строка убирается с другого листа. 
В нашем примере мы получили следующие две таблицы:
Выводы
Сегодня мы показали вам простой пример скрипта, который упростит, а может даже улучшит, рутинную работу с таблицами. Конечно, с помощью скриптов Google Sheets можно автоматизировать еще очень много других процессов. Вы можете сами прочитать о других функциях, а мы подробно расскажем о некоторых из них в следующих постах!
Как написать скрипт в гугл таблицах
Уровень сложности: Начинающий






Последнее обновление: 2021-01-22
Что такое Apps Script?
Apps Script - это платформа для быстрой разработки приложений, которая дает вам возможность автоматизировать, настраивать и расширять возможности Google Workspace. С помощью Apps Script вы можете автоматизировать и упростить обременительную или сложную работу в Google Workspace, сэкономив вам и вашей команде время и силы.
Возможности Apps Script включают следующее:

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

Сборник лаб "Основы Apps Script в Google Таблицах" учит основам Apps Script и тому, как использовать эти сервисы для улучшения работы с Google Таблицами. Эта лаба посвящена обучению основам Apps Script.
Сервис Spreadsheet Service 
Вы можете использовать Apps Script для расширения функционала Google Таблиц, чтобы сэкономить время и силы. Apps Script предоставляет службу Spreadsheet Service , которая позволяет скриптам взаимодействовать с вашими файлами Google Sheets и данными, которые они содержат. Вы можете использовать эту службу для автоматизации следующих общих задач с электронными таблицами:

Создавать или изменять электронные таблицы.
Читать и обновлять данных ячеек, формул и форматирования.
Создавать собственные кнопки и меню.
Импортировать и экспортировать данные из других приложений Google или сторонних источников.
Регулировать совместное использование и изменять контроль доступа к таблицам
. и многое другое.

Что вы узнаете из этого сборника
Этот сборник лаб охватывает все темы, которые вам понадобятся, чтобы начать использовать Apps Script с Google Sheets:

Макросы и пользовательские функции
Работа с книгами, листами и диапазонами
Работа с данными
Форматирование данных
Диаграммы и представление данных на слайдах

Этот сборник предназначен для чтения и освоения по порядку, поэтому начните с этой лабы и проходите их последовательно для наилучшего обучения.
Перейдите к следующему разделу, чтобы узнать больше о текущей теме.
Добро пожаловать в первую лабу этого сборника! Тут вы узнаете основы использования Apps Script с Google Таблицами. В частности, эта лаба фокусируется на двух ключевых концепциях: макросах и пользовательских функциях.
Макрос - это серия записанных действий в Google Таблицах. После записи вы можете активировать макрос, чтобы повторить эти действия позже с помощью элемента меню или сочетания клавиш. Вы можете создавать и обновлять собственные макросы как в Google Таблицах, так и в редакторе кода Apps Script.
В редакторе кода Apps Script вы также можете создавать собственные функции. Подобно встроенным функциям, которые предлагают Таблицы (например, SUM или AVERAGE), вы можете использовать Google Apps Script для написания собственных пользовательских функций для простых и нишевых операций (таких как преобразования или конкатенация строк). После создания вы можете вызывать эти функции в Таблицах, как встроенную функцию. Пользовательские функции также можно использовать в формулах ячеек, которые вы пишете, комбинируя их с другими функциями по мере необходимости.






Ниже показано, какие концепции и требования включает в себя эта лаба.
Что вы изучите

Как создать скрипт для Google Таблиц.
Как пользоваться редактором Apps Script.
Как создавать и обновлять макросы.
Как создать свою первую пользовательскую функцию для Таблиц.

Что вам потребуется

Базовое знакомство с JavaScript
Базовое знакомство с Google Таблицами
Умение читать А1-нотацию табличных процессоров

Примечание. Apps Script основан на JavaScript. Обучение написанию кода JavaScript выходит за рамки данного урока. Текущая цель - научить вас комфортно использовать среду разработки Apps Script и дать вам представление о типах приложений, которые вы можете создавать с ее помощью. Вы можете получить поддержку в вопросах программирования на ресурсах, перечисленных здесь contributor.pw/contacts
Вы закончили знакомство. Перейдите в следующий раздел, чтобы начать работу с макросами!
Как правило, работая с электронными таблицами, вы можете войти в цикл повторяющихся действий - копирование значений ячеек, форматирование, создание формул и т.д. - которые могут стать утомительными и привести к ошибкам. Чтобы дать вам возможность автоматизировать повторяющиеся действия, в Google Таблицах есть макросы. Макросы позволяют "записывать" серию действий в Таблице. С записанным макросом вы можете повторить эту серию действий в другом месте этой же таблицы простым нажатием горячей клавиши.
В этом разделе вы узнаете, как создать макрос в Таблицах. В следующем разделе вы увидите, как создаются макросы с помощью редактора скриптов.
Прежде чем вы начнете
Прежде чем продолжить, вам понадобится электронная таблица с некоторыми данными. Мы уже подготовили одну: щелкните кнопку ниже, чтобы скопировать Таблицу данных, а затем, перейдя по ссылке, щелкните кнопку "Создать копию".
Скопировать Таблицу данных

Копия примера Таблицы, которую вы можете использовать, будет размещена в папке Google Диска и называться "Копия Топ-10 самых кассовых фильмов (2020)".
Создание макроса
Теперь, когда у вас есть ваша Таблица, вы можете записать макрос. В этом примере вы создадите макрос, который форматирует строку заголовка данных. Просто выполните следующие действия:







Щелкните ячейку A1, чтобы навести курсор на эту строку. Это ваша строка заголовка.
В меню выберите Меню >Инструменты >Макросы >Записать макрос.

Как только вы начнете запись, Google Таблица начнет запоминать действия, которые вы выполняете: выделение ячеек, добавление данных, переключение на разные листы, форматирование и т.д. Эти действия позже становятся "сценарием", который повторяется после сохранения и активации макроса.

В окне записи макроса ("макрос-бокс") выберите "Использовать относительные ссылки".

Ключевые термины: макросы в Таблицах могут использовать два типа ссылок на определенные ячейки. Абсолютные ссылки указывают на точные местоположения при записи (например, ячейка A5), в то время как относительные ссылки применяются с использованием текущего выбора пользователя в качестве отправной точки (например, четвертая ячейка ниже текущего выбора).
Эта лаба предполагает использовать относительные ссылки для ваших макросов.

Измените цвет заливки выделенной строки с белого на темно-пурпурный (3).



Измените цвет текста выделенной строки с черного на белый.



Сделайте текст жирным шрифтом, нажав [Ctrl]+[B] (или [Cmd]+[B] на Mac).
Выберите Меню >Вид >Закрепить >1 строку, чтобы зафиксировать верхнюю строку.



Нажмите кнопку "Сохранить" в окне записи макроса внизу экрана. Затем в новом диалоговом окне вас попросят назвать макрос; дайте ему имя "Заголовок" и нажмите "Сохранить".

Используя пользовательский интерфейс Таблиц, вы создали макрос, специально предназначенный для форматирования заголовков!

Активация вашего макроса
Вы создали макрос в Таблицах! Вы можете попробовать применить его, выполнив следующие действия:







Щелкните слева от вкладок листов или Меню >Вставка >Новый лист, чтобы создать новый лист.
На новом листе добавьте какой-нибудь текст в A1:C2 . Не стесняйтесь следовать приведенным ниже примерам:



Выделите первую строку.



Выберите Меню >Инструменты >Макросы >Заголовок, чтобы применить макрос к выбранной области.



Авторизуйте макрос, следуя инструкциям на экране.

Примечание. Если вы используете учетную запись gmail.com, при первом использовании макроса вы также можете увидеть диалоговое окно "Непроверенное приложение". Google использует это, чтобы предупредить пользователей, которые могут использовать код неизвестных или ненадежных авторов. Если вы видите этот диалог, можно продолжить, поскольку вы являетесь автором макроса. Следуйте инструкциям на экране, чтобы продолжить авторизацию макроса. Процесс получения этого разрешения называется авторизацией.

Повторите шаг 4, чтобы снова запустить макрос (авторизация останавливает первое выполнение).

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

Макросы позволяют эффективно создавать электронные таблицы, и в следующей части этой лабы вы узнаете, как сделать ваши макросы еще более мощными! Секрет в том, что когда вы записываете макрос, на самом деле вы пишете код Apps Script. За кулисами Таблицы создают код, который соответствует макрокомандам за вас. А в следующем разделе вы узнаете, как изменить этот код напрямую с помощью редактора Apps Script в браузере.
Когда вы создаете макрос, Google Таблицы сохраняют ваши действия как функцию. Когда вы активируете макрос, Google Sheets вызывает функцию Apps Script, чтобы выполнить те же действия в том же порядке.
Взгляд на редактор
Теперь, когда вы создали макрос, вы можете посмотреть его код. Вы можете просмотреть свой макрос-скрипт, выбрав Меню > Инструменты > Редактор скриптов, чтобы открыть редактор кода в браузере для Apps Script.






Примечание. Макросы и сценарии, которые вы создаете в этой лабе, прикреплены к файлу Таблицы, с которым они работают, и могут быть доступны в любое время из пункта Меню > Инструменты > Редактор скриптов. Скрипты, которые прикреплены к Google Таблице, называются привязанными к контейнеру.
Понимание Макросы.gs
Посмотрите текущий сценарий. Таблицы создали файл скриптов Макросы.gs, когда вы записали макрос "Заголовок", заполнив его соответствующей функцией сценария приложений под названием "Заголовок". Когда вы активируете макрос, Таблицы запускают эту функцию.
Посмотрите на изображение ниже, чтобы познакомиться со структурой вашей макрос-функции в Apps Script. Ваш код может выглядеть немного иначе, если вы записали шаги в другом порядке или щелкнули на поле Таблице во время записи.

Первая строка - это аннотационный комментарий, влияющий на авторизацию:
/** @OnlyCurrentDoc */
Большинство скриптов перед запуском запрашивают у пользователя некоторый набор разрешений. Эти разрешения определяют, что пользователь разрешает делать сценарию. Если в проекте сценария присутствует комментарий @OnlyCurrentDoc , Apps Script запрашивает только разрешение на доступ и обновление текущей Таблицы. Без этого комментария Apps Script будет запрашивать разрешение на доступ и обновление всех Таблиц пользователя. Всегда полезно включать эту аннотацию, если вы планируете работать только с одним файлом Таблиц (Документов, Слайдов). Инструмент записи макросов автоматически добавляет этот комментарий.
Чтобы начать понимать, как Apps Script представляет инструкции вашего макроса, вы можете взглянуть на функцию:
function myFunction() < var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate(); spreadsheet.getActiveRangeList().setBackground('#4c1130') .setFontColor('#ffffff') .setFontWeight('bold'); spreadsheet.getActiveSheet().setFrozenRows(1); >;
Этот код запускается, когда вы активируете макрос "Заголовок". После function отметка myFunction() определяет имя функции и ее параметры. Помните, что myFunction() не требует параметров, поскольку макрос-функции в Apps Script вызываются напрямую. В фигурных скобках всегда заключено тело функции Apps Script.
Следующие лабы из этого сборника объясняют классы и концепции, участвующие в создании макроса. Но пока вы можете посмотреть описания кода ниже, чтобы получить общее представление о его компонентах и ​​их роли в создании программы. Рассмотрим первую строку:
var spreadsheet = SpreadsheetApp.getActive();
Здесь getActive() возвращает объект, представляющий текущий активный файл Таблицы, и устанавливает его в новую переменную spreadsheet .






var sheet = spreadsheet.getActiveSheet(); sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate();
Этот код соответствует щелчку по первой строке Таблицы, чтобы выделить ее. Это называется активацией. Сначала код сохраняет текущий лист в переменной sheet , а потом получает всю первую строку с помощью метода getRange() , далее вызывает activate() для ее активации. Первая строка Таблицы указывается с использованием конкретных номеров строки и столбца. Вызов spreadsheet.getCurrentCell().getRow() возвращает номер текущей строки, а sheet.getMaxColumns() возвращает максимальное количество столбцов на листе.
spreadsheet.getActiveRangeList().setBackground('#4c1130') .setFontColor('#ffffff') .setFontWeight('bold');
Этот фрагмент кода становится более сложным. Чтобы эффективно вызывать методы с помощью переменной spreadsheet , код объединяется в три метода после getActiveRangeList() , чтобы предотвратить избыточный вызов getActiveRangeList() . По мере того, как вы все больше и больше будете писать код с помощью Apps Script, вы сможете лучше познакомиться с этим соглашением о вызове нескольких методов в одном классе (также известном как цепь вызовов). На данный момент достаточно прочитать следующие краткие объяснения каждого метода в этом блоке:

getActiveRangeList() возвращает список диапазонов RangeList , которые выделены в spreadsheet в текущий момент. В этом случае это просто первая строка, которую активировал наш код.
Оба метода setBackground(color) и setFontColor(color) изменяют атрибуты цвета ячеек в диапазоне.
setFontWeight(fontWeight) регулирует толщину шрифта для ячеек в диапазоне.

Наконец, последняя строка "замораживает" первую строку макроса:
spreadsheet.getActiveSheet().setFrozenRows(1);
И это сценарий, созданный вами при записи макроса! Не беспокойтесь о незнакомых терминах или методах, упомянутых выше. Описание предназначено для того, чтобы вы задумались о некоторых идеях, на которых Apps Script фокусируется в типичной функции макросов, и о том, какие темы будут рассматривать будущие лабы.
Следующий раздел посвящен управлению кодом myFunction() , чтобы показать, как можно использовать редактор сценариев для дальнейшей персонализации макросов.
Настройка макросов с помощью Apps Script
В редакторе скриптов отображается макрос, созданный вами ранее в Google Таблицах. Регулируя содержимого тела функции, вы можете настроить инструкции вашего макроса для выполнения различных или дополнительных действий. Следующие упражнения демонстрируют различные способы управления макросами с помощью редактора скриптов.
Изменение ячеек, на которые будет распространено воздействие

В редакторе скриптов замените sheet.getMaxColumns() на 11 в строке 6. Это изменение изменяет диапазон ячеек в Таблице, на которые влияет макрос.

/** @OnlyCurrentDoc */ function myFunction() < var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, 11).activate(); /* sheet.getMaxColumns() заменено на 11 */ spreadsheet.getActiveRangeList().setBackground('#4c1130') .setFontColor('#ffffff') .setFontWeight('bold'); spreadsheet.getActiveSheet().setFrozenRows(1); >;

Чтобы сохранить сценарий, в верхней части редактора щелкните "Сохранить проект" .
Чтобы переименовать свой проект, вверху нажмите на заголовок "Проект без названия" (или "Записанные макросы (Копия Топ-10 самых кассовых фильмов (2020))", зависит от порядка записи макроса), введите "Макросы и пользовательские функции" в качестве имени нового проекта и выберите "Переименовать".
В Таблице щелкните слева от вкладок листов или Меню >Вставка >Новый лист, чтобы создать новый лист.
В редакторе сценариев в раскрывающемся списке функций выберите myFunction() и щелкните "Выполнить" .

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

Теперь, изменяя активный или целевой диапазон, ваш макрос влияет только на часть первой строки! Многие методы Apps Script принимают диапазон строку в А1-нотации в качестве параметра, чтобы указать, с какими ячейками следует оперировать.
Пришло время узнать о настройке цветов!






Изменение цветов в коде макроса
Чтобы помочь вам разработать цветовую схему макроса или для других элементов в Таблице, Apps Script может произвести заливку диапазона или изменить цвет текста. Выполните следующие инструкции, чтобы узнать, как настроить цвета.
Эти первые несколько шагов касаются изменения цвета фона, который назначает макрос:

В Таблице вернитесь к исходному листу (Лист1), содержащему данные.
Щелкните первую строку, чтобы выделить ее.
В редакторе сценариев замените #4c1130 на #afeeee в строке 6. Эти значения представляют разные цвета с использованием шестнадцатеричной нотации.

/** @OnlyCurrentDoc */ function myFunction() < var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, 11).activate(); /* sheet.getMaxColumns() заменено на 11 */ spreadsheet.getActiveRangeList().setBackground('#afeeee') /* #4c1130 заменено на #afeeee */ .setFontColor('#ffffff') .setFontWeight('bold'); spreadsheet.getActiveSheet().setFrozenRows(1); >;

В Таблице щелкните слева от вкладок листов или Меню >Вставка >Новый лист, чтобы создать новый лист.
В редакторе сценариев в раскрывающемся списке функций выберите myFunction() и щелкните "Выполнить" .

В Таблице фоновая заливка первых 11 столбцов в первой строке будут перекрашены в индивидуальный бирюзовый цвет, как показано здесь:

Переключив шестнадцатеричное значение цвета в параметрах setBackground(color) с #4c1130 (темно-пурпурный 3) на #afeeee (бледно-бирюзовый, параметр недоступный в меню цветов Таблиц по умолчанию), вы изменяете атрибут цвета фона для вашего макроса.
Вы настроили цвет фона, установленный вашим макросом. Предположим, теперь вы хотите изменить цвет текста, чтобы сделать заголовки более заметными. Вы можете сделать это, редактируя второй цветовой код:

В Таблицах щелкните первую строку, чтобы убедиться, что она все еще выделена.
В редакторе сценариев замените #ffffff на #191970 в строке 8. Это заставит макрос установить цвет шрифта в темно-синий.

/** @OnlyCurrentDoc */ function myFunction() < var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, 11).activate(); /* sheet.getMaxColumns() заменено на 11 */ spreadsheet.getActiveRangeList().setBackground('#afeeee') /* #4c1130 заменено на #afeeee */ .setFontColor('#191970') /* #ffffff заменено на #191970 */ .setFontWeight('bold'); spreadsheet.getActiveSheet().setFrozenRows(1); >;

В Таблице щелкните слева от вкладок листов или Меню >Вставка >Новый лист, чтобы создать новый лист.
В редакторе сценариев в раскрывающемся списке функций выберите myFunction() и щелкните "Выполнить" .

Вернитесь в Таблицу. Обратите внимание, что цвет текста в строке заголовка теперь темно-синий!

Теперь вы увидели, что макросы на самом деле являются действиями в Таблицах, записанными в виде кода Apps Script. В следующем разделе вы увидите еще одну возможность, которой Apps Script может помочь вам в работе с Google Таблицами: пользовательские функции.
Как и большинство приложений для работы с электронными таблицами, Google Таблицы имеют ряд встроенных формул, таких как =СУММ() , которые позволяют выполнять быстрые вычисления с данными таблицы. Пользовательские функции - это просто функции, которые вы определяете сами с помощью Apps Script. После того как вы определили пользовательскую функцию, вы можете использовать ее в любом месте вашей таблицы, как встроенную формулу.






В этом разделе показано, как создать пользовательскую функцию в Apps Script, которая выполняет валютную конвертацию.
Создание нового файла сценария
Используя ту же таблицу и проект скриптов, что и в разделе выше с макросами, вы можете следовать инструкциям ниже, чтобы узнать, как создать новый сценарий (который в конечном итоге можно использовать для создания своей первой настраиваемой функции!):


Чтобы создать новый файл Apps Script в текущем проекте, в левой части редактора рядом с полем "Файлы" нажмите "Добавить файл" >Скрипт.
Назовите новый файл сценария "Пользовательские функции" (Apps Script автоматически добавляет расширение ".gs" к имени).

В редакторе появится новая вкладка с именем "Пользовательские функции.gs".
Теперь, когда вы создали сценарий специально для пользовательских функций, вы можете заполнить его кодом.
Перевод долларов США в российские рубли
Предположим, вы хотите скорректировать данные для "Топ-10 самых кассовых фильмов (2020)", чтобы они отображали не только значения в долларах, но также и в рублях. С пользовательскими функциями это легко сделать. В следующем упражнении показано, как создать пользовательскую функцию для математического преобразования ваших долларовых значений в значения в рублях.
Прежде чем вы сможете написать свою первую настраиваемую функцию, настройте набор данных, чтобы функция демонстрировала правильный результат. Сделайте следующее:

В Таблице щелкните правой кнопкой мыши на столбец I .
В появившемся меню нажмите Вставить справа: 1.



Добавьте текст "Мировой прокат, рубли" в ячейку J1 .

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

В Пользовательские функции.gs замените код пустой функции myFunction() на следующий:

/** * Конвертирует доллары в рубли. * * @param dollars Значение суммы в долларах. * @return rubles Конвертированное значение в рублях. * @customfunction */ function USDTORUB(dollars)
Это код, который конвертирует доллары в рубли. Ниже идет инструкция, как запустить пользовательскую функцию в вашей Таблице.


Чтобы сохранить сценарий, в верхней части редактора щелкните "Сохранить проект" .
На листе с данными выберите ячейку J2 .
В поле функций введите =USDTORUB(I2)

Чтобы применить формулу к остальным ячейкам столбца:


Переместите курсор в нижний правый угол ячейки J2 и выберите маленькую синий квадратик (ваш курсор должен трансформироваться в при наведении на синий квадратик в ).
Щелкните и перетащите синий прямоугольник вниз, чтобы выделить J3:J11 .

В столбце J теперь указана конвертированная стоимость в рублях!

Поздравляем, вы создали свою первую пользовательскую функцию. Далее рассмотрим код, из которого состоит USDTORUB() .
Анализ функции USDTORUB() 
В начале кода вы можете распознать комментарии, подробно описывающие назначение этой части программы:
/** * Конвертирует доллары в рубли. * * @param dollars Значение суммы в долларах. * @return rubles Конвертированное значение в рублях. * @customfunction */
Подобные блоки комментариев часто используются в программировании, чтобы объяснить, что делают функции.
В этом комментарии вы можете выделить две части: описание функции (сообщение о преобразовании) и аннотации, описывающие параметры функции и тип возвращаемого значения.
Apps Script использует JSDoc для аннотаций, чтобы помочь вам документировать и создавать подсказки автозаполнения для вашего кода. Вы можете прочитать ниже, как каждая аннотация, используемая в USDTORUB() , помогает в разработке Apps Script:

@param : вы можете использовать аннотацию @param для описания каждого параметра, передаваемого в функцию.
@return : вы можете использовать аннотацию @return , чтобы описать, что возвращает функция.
@customfunction : вы всегда должны добавлять @customfunction в комментарий к пользовательской функции. Эта аннотация уведомляет Таблицу о возможности автозаполнения вашей пользовательской функции, так же как Таблицы автоматически заполняют встроенные формулы, когда вы вводите их имена в ячейку, как показано ниже:

Обратите внимание, что текст, который появляется во всплывающем окне автозаполнения, точно соответствует тексту описания, который вы поместили в блок комментариев. Вы можете упростить дальнейшее использование пользовательских функций, убедившись, что создаваемые вами описания хорошо написаны и полны.
Далее сфокусируемся на коде функции USDTORUB() :
function USDTORUB(dollars)
Как упоминалось ранее, USDTORUB() принимает числовую переменную в долларах и возвращает это значение, преобразованное в рубли в числовой переменной rubles , умножая его на фиксированный обменный курс. Входной параметр - это значение, содержащееся в ячейке, которую вы указали при добавлении пользовательской функции в ячейку. В этом примере входные суммы в долларах поступают из столбца I . Выходное значение rubles помещается в ячейку функции (то есть в столбец J в этом примере).
Пользовательские функции могут работать с числовыми или строковыми значениями. Это будет показано далее.
Объединение строкового суффикса
Предположим, вы хотите, чтобы числовой вывод функции USDTORUB() включал суффикс рублей " руб. ". Вы можете сделать это с помощью Apps Script, используя оператор конкатенации " + ", как показано в следующем примере:

В редакторе измените return rubles; в строке 10 на return rubles + ' руб.' ;.

Важное замечание. Данный пример является сильно надуманным, т.к. в Таблицах вывод форматированного чиста удобнее и надежнее сделать через формат ячеек. Но как общий простой пример, данный хорошо код демонстрирует требуемые концепции.
Оператор + добавляет строку " руб. " в конец значения, содержащегося в rubles . Теперь ваш код должен выглядеть так:
/** * Конвертирует доллары в рубли. * * @param dollars Значение суммы в долларах. * @return rubles Конвертирует переданное значение в рубли. * @customfunction */ function USDTORUB(dollars)


Чтобы сохранить сценарий, в верхней части редактора щелкните "Сохранить проект" .

Значения в рублях теперь отображаются в колонке J :

Вы обновили свою пользовательскую функцию, которая теперь не только конвертирует доллары в рубли, но и добавляет строковый суффикс к выводимому значению.
Дополнительно: получение внешних данных
Это хорошее начало для базовой пользовательской функции, но в этом примере предполагается, что курс обмена доллара на рубли постоянен. Предположим, вместо этого вы хотите, чтобы всегда использовался текущий обменный курс, чтобы каждый раз при перезагрузке Таблицы значения пересчитывались для представления текущего преобразования. Для этого вам понадобятся средства определения текущего обменного курса. Это не та информация, которая доступна в Google Таблицах, но, к счастью, вы можете использовать Apps Script, чтобы получить ее!
Вы можете использовать приведенный ниже код, чтобы получить текущий курс конвертации рублей в доллары:
/** * Конвертирует доллары в рубли. * * @param dollars Значение суммы в долларах. * @return rubles Конвертирует переданное значение в рубли. * @customfunction */ function USDTORUB(dollars) < // Получает кэш, общий для всех пользователей скрипта. var cache = CacheService.getScriptCache(); // Доступ к ячейке памяти (rate.RUB) кеша скриптов. var rate = cache.get("rates.RUB"); // Если кэш отсутствует, // программа получает текущее значение RUB из API // и сохраняет его в кеше для дальнейшего использования. if (!rate) < var response = UrlFetchApp.fetch( "https://api.exchangeratesapi.io/latest?base=USD" ); var result = JSON.parse(response.getContentText()); rate = result.rates.RUB; cache.put("rates.RUB", rate); >// Конвертирует доллары в рубли по последнему курсу. var rubles = dollars * rate; // Возвращает значение в рублях с суффиксом. return rubles + " руб."; >
Этот код получает текущий обменный курс с сервера финансовой информации с помощью стороннего API обменного курса. Это делается с помощью служб Apps Script, таких как UrlFetchApp и CacheService . Эти расширенные концепции выходят за рамки этой конкретной лабы, но вы можете начать видеть универсальность Apps Script, автоматизирующего более сложные и более реальные задачи в Google Таблицах.
Рекомендации по пользовательским функциям
Поздравляем с выполнением упражнений по пользовательским функциям. При использовании пользовательских функций в своих проектах важно понимать, что у них есть определенные ограничения. В следующем списке перечислены ограничения, подробно описанные в руководстве по пользовательским функциям к Google Таблицам:

Не создавайте пользовательские функции, требующие авторизации пользователя. Создавайте функции для выполнения простых задач, таких как расчет выборки данных, преобразование текста и т.д. См. использование служб Apps Script.
Не называйте пользовательскую функцию именами встроенных формул, и не завершайте имя знаком подчеркивания. См. рекомендации по именованию.
Не передавайте переменные (тиковые) аргументы пользовательским функциям. В качестве аргументов пользовательским функциям можно передавать только детерминированные (фиксированные) значения. Передача переменных аргументов, таких как результат =RAND() или =NOW() , нарушит работу пользовательской функции. См. руководство по аргументам.
Не создавайте функции, выполнение которых занимает более 30 секунд. Если это займет больше времени, произойдет ошибка, поэтому код функции должен быть простым и ограниченным по объему. Лучше всего, чтобы вычисления, проводимые в пользовательских функциях, были как можно проще. См. рекомендации по возвращаемым значениям.

Теперь у вас есть возможность улучшить свои Таблицы, используя редактор сценариев для работы с макросами и для создания пользовательских функций! В следующем и последнем разделе вы можете просмотреть, что вы узнали, и что вы можете сделать дальше, чтобы улучшить свои навыки написания скриптов.
Вы завершили первую лабу по основам Apps Script. Создавая и редактируя макросы и пользовательские функции Таблиц, вы изучили основные концепции Apps Script. Вы можете расширить свои знания об Apps Script в следующей лабе!
Считаете ли вы эту лабу полезной?
Что мы рассмотрели

Основные концепции Apps Script.
Как ориентироваться в редакторе скриптов.
Как создавать и обновлять макросы и скрипты для Таблиц.
Как создавать собственные функции для Google Таблиц.

Ключевые термины

Apps Script: Платформа и одноименный язык программирования для быстрой разработки программ на основе JavaScript, которая позволяет быстро и легко расширять приложения Google Workspace и автоматизировать работу.
JSDoc: Язык разметки, используемый для написания пояснительных примечаний к исходным файлам JavaScript.
Script editor [Script editor]: Редактор кода для Apps Script, запускаемый в браузере.
Абсолютная ссылка [Absolute reference]: параметр записи макроса, который указывает, что записанные макрокоманды применяются к точным адресам ячеек, используемым в записи.
Авторизация [Authorization]: Процесс предоставления пользователем разрешений, позволяющих скрипту получать доступ к пользовательским данным или иным образом выполнять действия от имени пользователя.
Активный [Active] (статус): Указывает, что таблица, лист, диапазон или ячейка в настоящее время просматривается или выделены пользователем.
Диапазон [Range]: Группировка из одной или нескольких соседних ячеек Таблицы.
Лист [Sheet]: Одна из страниц Таблицы. Иногда используется для ссылки на файл Google Таблиц на Google Диске.
Макрос [Macro] (в данных лабах): Скрипт, полученный с помощью инструментов записи, которые перефразируют (или любым другим способом преобразуют) действия пользователя в программу. М. также, записанная серия действий в Google Таблицах, которые можно быстро повторить с помощью пункта меню или сочетания клавиш.
Относительная ссылка [Relative reference]: Параметр записи макроса, который указывает, что записанные макрокоманды применяются к ячейкам относительно текущего пользовательского выбора (активной ячейки).
Пользовательская функция [Custom functions]: Пользовательские формулы созданные в Apps Script, которые используются в Таблицах для простых операций.
Служба Spreadsheet [Spreadsheet Service]: Служба Apps Script, которая позволяет скриптам создавать файлы Google Sheets, получать к ним доступ и изменять их.
Скрипт, привязанный к контейнеру [Container bound script]: Любой скрипт, связанный с документом Google Workspace и созданный из него, например, Google Таблица или Google Документ.
Скрипт (в данных лабах): Сценарий, написанный на языке Google Apps Script или другом языке сценариев.
Сценарий (в данных лабах): Устойчивая (записанная или другим способом определенная) последовательность действий.
Таблица: Электронная таблица из приложений Google. Она же "Google Таблица", "Google Sheet".
таблица: любая информация, представленная в виде таблицы.
Цепь вызовов: Идиома программирования для вызова нескольких методов объекта, основанная на том факте, что каждый вызов метода возвращает сам объект.

Дополнительные сведения об Apps Script можно найти в документации.
Что дальше
Следующая лаба в этом сборнике представляет основные классы и терминологию службы Spreadsheet Service Apps Script. Эта служба позволяет вам достаточно полно контролировать значения и представления данных в Google Таблицах с помощью Apps Script.


Похожие публикации:

Где x а где y
Как в сводной таблице посчитать количество уникальных значений
Как загрузить html файл на сайт wordpress
Как проверить простое ли число в питоне