Что такое макросы в гугл таблицах

Что такое макросы в гугл таблицах

Уровень сложности: Начинающий

Последнее обновление: 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)".

Создание макроса

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

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

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

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

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

Эта лаба предполагает использовать относительные ссылки для ваших макросов.

1. Выберите строку 1.

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

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

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

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

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

Активация вашего макроса

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

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

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

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

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

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

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

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

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

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

Взгляд на редактор

Теперь, когда вы создали макрос, вы можете посмотреть его код. Вы можете просмотреть свой макрос-скрипт, выбрав Меню > Инструменты > Редактор скриптов, чтобы открыть редактор кода в браузере для Apps Script.

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

Понимание Макросы.gs

Посмотрите текущий сценарий. Таблицы создали файл скриптов Макросы.gs, когда вы записали макрос "Заголовок", заполнив его соответствующей функцией сценария приложений под названием "Заголовок". Когда вы активируете макрос, Таблицы запускают эту функцию.

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

Первая строка — это аннотационный комментарий, влияющий на авторизацию:

Большинство скриптов перед запуском запрашивают у пользователя некоторый набор разрешений. Эти разрешения определяют, что пользователь разрешает делать сценарию. Если в проекте сценария присутствует комментарий @OnlyCurrentDoc , Apps Script запрашивает только разрешение на доступ и обновление текущей Таблицы. Без этого комментария Apps Script будет запрашивать разрешение на доступ и обновление всех Таблиц пользователя. Всегда полезно включать эту аннотацию, если вы планируете работать только с одним файлом Таблиц (Документов, Слайдов). Инструмент записи макросов автоматически добавляет этот комментарий.

Чтобы начать понимать, как Apps Script представляет инструкции вашего макроса, вы можете взглянуть на функцию:

Этот код запускается, когда вы активируете макрос "Заголовок". После function отметка myFunction() определяет имя функции и ее параметры. Помните, что myFunction() не требует параметров, поскольку макрос-функции в Apps Script вызываются напрямую. В фигурных скобках всегда заключено тело функции Apps Script.

Следующие лабы из этого сборника объясняют классы и концепции, участвующие в создании макроса. Но пока вы можете посмотреть описания кода ниже, чтобы получить общее представление о его компонентах и ​​их роли в создании программы. Рассмотрим первую строку:

Здесь getActive() возвращает объект, представляющий текущий активный файл Таблицы, и устанавливает его в новую переменную spreadsheet .

Этот код соответствует щелчку по первой строке Таблицы, чтобы выделить ее. Это называется активацией. Сначала код сохраняет текущий лист в переменной sheet , а потом получает всю первую строку с помощью метода getRange() , далее вызывает activate() для ее активации. Первая строка Таблицы указывается с использованием конкретных номеров строки и столбца. Вызов spreadsheet.getCurrentCell().getRow() возвращает номер текущей строки, а sheet.getMaxColumns() возвращает максимальное количество столбцов на листе.

Этот фрагмент кода становится более сложным. Чтобы эффективно вызывать методы с помощью переменной spreadsheet , код объединяется в три метода после getActiveRangeList() , чтобы предотвратить избыточный вызов getActiveRangeList() . По мере того, как вы все больше и больше будете писать код с помощью Apps Script, вы сможете лучше познакомиться с этим соглашением о вызове нескольких методов в одном классе (также известном как цепь вызовов). На данный момент достаточно прочитать следующие краткие объяснения каждого метода в этом блоке:

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

Наконец, последняя строка "замораживает" первую строку макроса:

И это сценарий, созданный вами при записи макроса! Не беспокойтесь о незнакомых терминах или методах, упомянутых выше. Описание предназначено для того, чтобы вы задумались о некоторых идеях, на которых Apps Script фокусируется в типичной функции макросов, и о том, какие темы будут рассматривать будущие лабы.

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

Настройка макросов с помощью Apps Script

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

Изменение ячеек, на которые будет распространено воздействие

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

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

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

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

Пришло время узнать о настройке цветов!

Изменение цветов в коде макроса

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

Эти первые несколько шагов касаются изменения цвета фона, который назначает макрос:

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

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

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

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

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

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

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

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

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

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

В этом разделе показано, как создать пользовательскую функцию в Apps Script, которая выполняет валютную конвертацию.

Создание нового файла сценария

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

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

В редакторе появится новая вкладка с именем "Пользовательские функции.gs".

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

Перевод долларов США в российские рубли

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

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

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

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

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

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

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

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

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

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

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

Поздравляем, вы создали свою первую пользовательскую функцию. Далее рассмотрим код, из которого состоит USDTORUB() .

Анализ функции USDTORUB()

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

Подобные блоки комментариев часто используются в программировании, чтобы объяснить, что делают функции.

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

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

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

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

Далее сфокусируемся на коде функции USDTORUB() :

Как упоминалось ранее, USDTORUB() принимает числовую переменную в долларах и возвращает это значение, преобразованное в рубли в числовой переменной rubles , умножая его на фиксированный обменный курс. Входной параметр — это значение, содержащееся в ячейке, которую вы указали при добавлении пользовательской функции в ячейку. В этом примере входные суммы в долларах поступают из столбца I . Выходное значение rubles помещается в ячейку функции (то есть в столбец J в этом примере).

Пользовательские функции могут работать с числовыми или строковыми значениями. Это будет показано далее.

Объединение строкового суффикса

Предположим, вы хотите, чтобы числовой вывод функции USDTORUB() включал суффикс рублей " руб. ". Вы можете сделать это с помощью Apps Script, используя оператор конкатенации " + ", как показано в следующем примере:

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

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

Оператор + добавляет строку " руб. " в конец значения, содержащегося в rubles . Теперь ваш код должен выглядеть так:

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

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

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

Дополнительно: получение внешних данных

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

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

Этот код получает текущий обменный курс с сервера финансовой информации с помощью стороннего 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.

Как записать макрос в Google Таблицы (простое пошаговое руководство)

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

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

Что такое макрос в Google Таблицах?

Макрос — это фрагмент кода в бэкэнде Google Таблиц (не волнуйтесь, это совсем несложно).

Этот макрос (фрагмент кода) представляет собой последовательность шагов, которые вы определили, и как только вы запустите этот макрос, он автоматически выполнит все эти шаги.

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

• Удалите все повторяющиеся записи
• Удалите все лишние пробелы между словами
• Установить границу для всего набора данных

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

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

Не волнуйтесь! Для этого вам не нужно знать код. Когда вы записываете макрос, вы просто показываете в Google Таблицах шаги, которые необходимо выполнить (делая это один раз). Google Sheets автоматически создает для вас код и использует его позже, когда вы запускаете макрос.

Итак, давайте начнем и запишем наш первый макрос.

Запись макроса в Google Таблицы

Запишем простой макрос в Google Таблицы, который будет делать следующее:

• Выберите ячейку A1 на листе
• Введите в него текст Hello
• Раскрасьте ячейку в желтый цвет

Вот шаги, чтобы записать этот макрос в Google Таблицы:

• Щелкните вкладку Инструменты.
• Наведите курсор на опцию Макросы, она покажет некоторые дополнительные опции.
• Щелкните «Записать макросы». Это включит запись макроса, а также отобразит диалоговое окно.
• В диалоговом окне «Макрос» выберите параметр «Использовать абсолютные ссылки» (этот параметр объясняется далее в этом руководстве).

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

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

• Выберите ячейку A1 на листе
• Введите в него текст «Привет».
• Придайте ячейке желтый цвет (используйте параметр Цвет заливки на панели инструментов)

Выполнив эти три действия, нажмите кнопку «Сохранить» в диалоговом окне «Макрос».

Откроется диалоговое окно «Сохранить новый макрос», в котором нужно указать имя макроса. Желательно сделать его кратким, но достаточно информативным, чтобы вы знали, что делает этот макрос. В этом примере я назову эту ячейку Color Hello.

У вас также есть возможность указать ярлык для этого макроса. Когда вы устанавливаете ярлык, вы можете использовать это сочетание клавиш для запуска макроса. Таблицы Google позволяют использовать ярлык в следующем формате — Control + Alt + Shift + Number (где число может быть от 0 до 9)

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

Как только макрос будет сохранен, вы увидите уведомление в левом нижнем углу документа Google Sheets. Он также показывает параметр «Изменить сценарий», и если вы щелкнете по нему, он откроет редактор сценариев Google Apps и покажет вам записанный код.

Запуск макроса в Google Таблицах

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

Есть несколько способов запустить макрос в Google Таблицах:

• С помощью параметра макроса на вкладке «Инструменты»
• С помощью сочетания клавиш
• Из редактора скриптов Google Apps
• Назначив его фигуре

Давайте быстро рассмотрим каждый из этих способов запуска макроса в Google Таблицах.

Использование параметров макроса

После того, как вы записали макрос, вы можете легко запустить его, разместив его на вкладке «Инструменты».

Щелкните вкладку «Инструменты» и наведите курсор на параметр «Макросы».

В появившихся дополнительных параметрах вы увидите все макросы, перечисленные внизу (после первых трех параметров).

Как только вы нажмете на любое имя макроса, он будет немедленно выполнен.

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

Использование сочетания клавиш

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

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

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

Из редактора скриптов Google

Когда вы записываете макрос в Google Таблицы и сохраняете его, шаги автоматически сохраняются в редакторе скриптов Google Apps.

Теперь, если вы хотите выполнить макрос, вы также можете сделать это из редактора GAS.

Для этого вам сначала нужно открыть редактор GAS (щелкнув вкладку «Инструменты», а затем «Редактор скриптов»).

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

Назначив макрос фигуре

Вы также можете вставить фигуру на лист, а затем назначить макрос этой кнопке.

Чтобы вставить кнопку, щелкните вкладку «Вставка», а затем щелкните «Рисунок».

Откроется диалоговое окно «Рисование».

Щелкните значок Фигуры в диалоговом окне и вставьте фигуру, которую хотите использовать в качестве кнопки для запуска макроса.

Когда вы нажимаете на любую фигуру, вам нужно будет нарисовать ее в отведенной области. Как только вы нарисуете его и нажмете «Сохранить и закрыть», фигура будет вставлена ​​в рабочий лист.

Чтобы назначить макрос этой форме, выберите фигуру и щелкните три точки, которые появляются в правом верхнем углу.

Из появившихся опций нажмите «Назначить скрипт».

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

Теперь, когда вы нажимаете на фигуру, она мгновенно запускает макрос.

Абсолютная ссылка на относительную ячейку при записи макроса

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

• Абсолютная ссылка на ячейку: $ A $ 1
• Относительная ссылка на ячейку: A1

Абсолютные ссылки на ячейки означают, что если вы скопируете и вставите их в формулы, эти ссылки не изменятся. Это абсолютно. Например, предположим, что у вас есть значения в ячейке A1: A10 и формула = СУММ ($ A $ 1: $ A $ 10) в ячейке B1.

В этой формуле используются абсолютные ссылки на ячейки. Это означает, что если я скопирую и вставлю эту формулу из ячейки B1 в ячейку B2 (или любую другую ячейку на листе), она не изменит ячейки, на которые она ссылается.

Напротив, допустим, у меня в ячейке B1 есть следующая формула: = СУММ (A1: A10). Поскольку здесь используются относительные ссылки на ячейки, когда я копирую и вставляю формулу из ячейки B1 в ячейку B2, формула изменится на = СУММ (A2: A11).

Это происходит потому, что использование относительной ссылки на ячейку не блокирует ссылки на ячейки и относится к позиции, в которой она используется. Если я скопирую эту формулу в ячейку C1, она изменится на = СУММ (B1: B10). Это потому, что я сдвинул формулу на один столбец вправо (с B на C), ссылка в формуле также сдвинута на один столбец.

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

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

Но с относительными ссылками на ячейки он запоминает позицию, с которой вы начали, а затем перемещается относительно этой позиции. Например, если я начну записывать макрос, когда у меня выделена ячейка B1, и я сначала выберу A1, а затем введу в него текст «Hello», макрос Google Sheet запомнит, что я переместил одну ячейку влево.

Таким образом, в следующий раз, когда вы запустите этот макрос и выберете ячейку K1, он введет текст в ячейку J1 (которая находится на одну ячейку слева от выбранной ячейки).

Как автоматизировать Google Sheets с помощью макросов

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

Что такое макросы?

Макрос или макроинструкция — это особая последовательность действий, позволяющая автоматизировать последовательность шагов для повышения производительности. Они работают, записывая ваши действия и сохраняя их в файле, который привязан к электронной таблице, в которой они были записаны.

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

Макросы — это мощная функция, которая может выполнять практически все, что способен Sheets. Вот лишь несколько примеров его функциональности:

• Примените форматирование и стили.
• Создавайте совершенно новые таблицы.
• Используйте любую функцию Google Sheets, панель инструментов, меню или функцию.

Небо это предел.

Как записать макрос в Google Sheets

Запустите Google Sheet и нажмите Инструменты> Макросы> Запись макроса.

Это открывает меню записи в нижней части окна, с двумя вариантами записи ваших действий:

• Абсолютные ссылки: макрос будет выполнять задачи только в тех ячейках, которые вы записали. Если вы выделите курсором ячейку B1, макрос выделит только курсив B1 независимо от того, на какую ячейку вы щелкнули.
• Относительные ссылки . Макрос выполняет задачи в выбранных ячейках независимо от того, где они находятся на листе. Если вы выделите курсором B1 и C1, вы можете повторно использовать один и тот же макрос для выделения курсором ячеек D1 и E1 позже.

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

После того, как вы захватили все действия для этого макроса, нажмите «Сохранить».

Введите имя для вашего макроса. Google также позволяет создавать ярлыки для десять макросов. Если вы хотите привязать макрос к сочетанию клавиш, введите число от 0 до 9 в соответствующем поле. Когда вы закончите, нажмите «Сохранить».

Если вам нужно изменить имя макроса или ярлык, вы можете отредактировать макрос, щелкнув Инструменты> Макросы> Управление макросами.

В открывшемся окне настройте по желанию, а затем нажмите «Обновить».

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

Как запустить макрос в Google Sheets

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

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

Как импортировать макросы

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

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

Откройте Google Sheet с макросом, который вы хотите скопировать, а затем нажмите Инструменты> Макросы> Управление макросами.

Затем нажмите значок «Дополнительно» рядом с макросом, который вы хотите скопировать, а затем нажмите «Редактировать сценарий».

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

Выделите макрос (ы), который вы хотите скопировать, затем нажмите Ctrl + C. Обязательно скопируйте все до и включая заключительную точку с запятой.

Теперь откройте другую электронную таблицу, в которую вы будете импортировать макрос, и нажмите «Инструменты»> «Макросы»> «Записать макрос».

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

Нажмите «Сохранить» еще раз.

Откройте «Сценарий Google Apps», нажав «Инструменты»> «Редактор сценариев», а затем откройте файл macros.gs на левой панели. Удалите существующую функцию, а затем нажмите Ctrl + V, чтобы вставить макрос из другого листа.

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

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

Далее нажмите Инструменты> Макросы> Импорт.

Наконец, нажмите «Добавить функцию» под макросом, который вы хотите добавить.

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

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

Как создать макрос в Google Таблицах и назначить для него кнопку

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

Пример задачи для макроса

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

У меня есть небольшая форма для заполнения с номерами товаров, их названием, серийным номером, датой.

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

Дата тоже проставляется автоматически.

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

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

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

Запись макроса в Google Таблицах

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

Для начала я заполню динамические данные в своей таблице. Сделайте это и вы, если в этом возникнет необходимость.

После этого откройте меню «Расширения», наведите курсор на пункт «Макросы» и нажмите кнопку «Записать макрос».

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

Я скопировал всю строку для переноса в базу данных и использую специальную вставку «Только значения», чтобы избежать вставки функций, которые тут не нужны.

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

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

Остается только нажать на «Сохранить», завершая тем самым запись макроса. Если вы не уверены, что все сделали правильно, щелкните на «Отмена», снова запустите запись и повторите действия.

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

Проверьте макрос через то же самое меню «Макросы», отыскав его по названию.

Если результат вас устраивает, переходите к следующему шагу. В противном случае удалите текущую программу и создайте новую.

Назначение кнопки для запуска макроса

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

Разверните меню «Вставка» и нажмите по пункту «Рисунок».

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

Расположите рисунок на листе, щелкните по трем точкам справа от него и выберите «Назначить скрипт».

В качестве скрипта укажите точное название макроса, который создали ранее.

Теперь кликните по картинке ЛКМ и убедитесь в том, что скрипт был успешно выполнен (на экране появится соответствующее уведомление).

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