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

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

  • автор:

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

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

Последнее обновление: 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. Измените цвет заливки выделенной строки с белого на темно-пурпурный (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 */

Большинство скриптов перед запуском запрашивают у пользователя некоторый набор разрешений. Эти разрешения определяют, что пользователь разрешает делать сценарию. Если в проекте сценария присутствует комментарий @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 Таблицах. Регулируя содержимого тела функции, вы можете настроить инструкции вашего макроса для выполнения различных или дополнительных действий. Следующие упражнения демонстрируют различные способы управления макросами с помощью редактора скриптов.

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

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

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

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

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

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

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

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

  1. В Таблице вернитесь к исходному листу (Лист1), содержащему данные.
  2. Щелкните первую строку, чтобы выделить ее.
  3. В редакторе сценариев замените #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); >;
  1. В Таблице щелкните слева от вкладок листов или Меню >Вставка >Новый лист, чтобы создать новый лист.
  2. В редакторе сценариев в раскрывающемся списке функций выберите myFunction() и щелкните «Выполнить» .

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

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

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

  1. В Таблицах щелкните первую строку, чтобы убедиться, что она все еще выделена.
  2. В редакторе сценариев замените #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); >;
  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() на следующий:
/** * Конвертирует доллары в рубли. * * @param dollars Значение суммы в долларах. * @return rubles Конвертированное значение в рублях. * @customfunction */ function USDTORUB(dollars)

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

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

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

  1. Переместите курсор в нижний правый угол ячейки J2 и выберите маленькую синий квадратик (ваш курсор должен трансформироваться в при наведении на синий квадратик в ).
  2. Щелкните и перетащите синий прямоугольник вниз, чтобы выделить 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, используя оператор конкатенации » + «, как показано в следующем примере:

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

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

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

/** * Конвертирует доллары в рубли. * * @param dollars Значение суммы в долларах. * @return rubles Конвертирует переданное значение в рубли. * @customfunction */ function USDTORUB(dollars)

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

Значения в рублях теперь отображаются в колонке 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.

web-SuN –
создание и продвижение сайтов

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

Мне стало грустно от мысли, что придется вспоминать Basic, но при первом же знакомстве с документацией для разработчика Google Spreadsheet Service, я изменил свое мнение, созданное первым впечатлением. Для начала, оказалось, что макросы пишутся на JavaScript. Это в корне меняло сам подход к решению задачи, т.е. мне предстояло попрактиковаться действительно в полезном языке, применяя его в новой задаче, расширяющей мои знания.

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

function onOpen()

Данная функция срабатывает при открытии документа и создает пункт меню «Export» с двумя выпадающими менюшками «Export One» и «Export Two». Вместе с тем, каждому выпадающему меню присваивается функция срабатывающая при клике. Рассмотрим тело первой функции:

function Ex1() < SpreadsheetApp.getUi().alert('Начнем!'); var newSheetName = 'Export_1'; var sourceSheetName = 'Исходник'; var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets(); for (var sheet in sheets) < if(sheets[sheet].getSheetName() == newSheetName) < //Удаляем лист, если он уже создан ранее ss.setActiveSheet(sheets[0]); ss.deleteSheet(sheets[sheet]); >> //Создание нового листа ss.insertSheet().setName(newSheetName); //Создаем рыбу таблицы var newSheet = ss.getSheetByName(newSheetName); var header = [ 'Campaign', 'Campaign Daily Budget', 'Campaign Type' ]; //Заголовки таблицы и авторесайз столбцов var i = 0; var row = newSheet.getRange('A1'); for(var title in header) < var newrow = row.offset(0, i); newrow.setValue(header[title]); i++; newSheet.autoResizeColumn(i); >>

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

15 строка создает новый лист с заранее заданным именем. Потом производится первоначальное заполнение таблицы, а именно, заполняется шапка: в цикле (26 строка) происходит сдвиг выделенной ячейки, в которую вставляется значение из заготовленного массива.

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

Однако такой подход к записи данных при больших объемах занимает много времени и может привести к ошибке Exceeded maximum execution time. По ссылке предложен более правильный способ работы с чтением/записью в Google Apps Scripts.

Ключевые слова этой записи

макросы , Google , spreadsheet

Как создать макрос в Google таблицах

таблицы google sheets

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

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

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

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

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

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

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

Более подробнее о создании использовании макросов смотрите в следующем видео:

Чем полезны макросы в Google Sheets

Андрей Мазур

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

Содержание:

Но как бы ни совершенствовались программы, рутинная однообразная работа, появление ошибок из-за усталости никуда не исчезают. Тем более возрастает вероятность ошибок, если с одним документом работают несколько человек. А ведь удобство совместной работы — из преимуществ онлайн редакторов Google. Однако в арсенале Google Sheets есть средство, помогающее избавиться от чрезмерной рутины. А заодно — уменьшить число ошибок и сэкономить время пользователя. Ведь чем меньше задач выполняется вручную, тем меньше ошибок в обработке данных.

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

Рассказываю простыми словами — что такое макрос Google Sheets, как применять макрос, как создать макрос.

Что такое макрос — объясняю в самом общем виде

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

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

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

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

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

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

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

Свяжите сервисы между собой без программистов за 5 минут!

Используейте ApiX-Drive для самостоятельной интеграции разных сервисов между собой. Доступно 350+ готовых интеграций.

  • Автоматизируйте работу интернет магазина или лендинга
  • Расширяйте возможности за счет интеграций
  • Не тратьте деньги на программистов и интеграторов
  • Экономьте время за счет автоматизации рутинных задач

Бесплатно протестируйте работу сервиса прямо сейчас и начните экономить до 30% времени! Перейти

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

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

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

Удобная интеграция. Макросы Google Sheets создаются с помощью инструмента Google Apps Script. Он же используется для создания программных продуктов для других сервисов Google Workspace (Docs, Drive, Forms etc). Поэтому интеграция электронных таблиц с другими сервисами Google упрощается. Например, можно применить макросы для операций перемещения, копирования или удаления файлов на Google Диске прямо из Google Sheets.

Как создать макрос — пошаговая инструкция

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

Шаг 1. Открываем таблицу, в которой собираемся создать макрос. В верхнем меню открываем Extensions > Macros > Record macro.


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

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


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

Назовите макрос. После чего нажмите кнопку Save.

Чтобы запустить макрос, последовательно щелкните Extensions > Macros. После чего выберите нужный макрос в списке уже существующих.

Важные замечания

  • Макросы, созданные для документов MS Excel (и даже преобразованных в формат Google Sheets) не будут работать в документах Google Sheets.
  • Не раздувайте макрос, не записывайте сразу много действий. Например, есть много требований к форматированию на одном листе. Не пытайтесь запихнуть все в один сценарий. Макросы лучше работают, если набор команд короткий. Лучше создать несколько макросов и включать их поочередно.
  • Максимальное число макросов для одной таблицы — 10.
  • Начиная работать с новой таблицей максимально используйте уже созданные макросы. Для этого скопируйте лист с новыми данными в исходную таблицу (ту, которая содержит нужные макросы).
  • Редактор Google Sheets может работать медленнее чем MS Excel. Вполне возможно, что макрос выполнит задание не мгновенно, а за несколько секунд. Тем более уйдет больше времени, если макрос содержит много действий.

Заключение

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

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *