Как записать макрос в 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 Таблицы точные шаги, которые вы хотите автоматизировать. Когда вы закончите, Google Sheets создаст код для шагов, которые вы выполнили. Теперь вы можете запустить макрос, даже не касаясь этого кода.
Если вы сами создаете код и чувствуете, что застряли, вы можете использовать средство записи макросов и просмотреть код, который он создает. Для новичков, изучающих скрипт Google Apps, регистратор макросов может стать отличным помощником.

Чего макрос не может делать в Google Таблицах

Хотя макросы великолепны, у них есть некоторые ограничения:

Невозможно создать пользовательские функции . Я чаще всего использую скрипт Google Apps для создания пользовательских функций. К сожалению, вы не можете сделать это с помощью макроса записи. Чтобы создать настраиваемую функцию, вам нужно будет написать код вручную и указать тип результата, который вы хотите вернуть этой функцией.
Невозможно создать макрос для запуска на основе событий : предположим, вы хотите, чтобы макрос запускался всякий раз, когда пользователь открывает книгу, выбирает определенную ячейку или вносит изменения в рабочий лист. Вы не можете сделать это с помощью средства записи макросов. Это события, и поскольку вы хотите, чтобы макрос запускался при возникновении события, вам придется вручную указать событие запуска и код макроса.
Невозможно запустить макрос, не выбрав объект : когда вы начинаете запись макроса в Google Таблицах, он просто выполняет все ваши шаги и создает код на его основе. Для этого вам необходимо показать макросу точные шаги, которые необходимо выполнить. Это означает, что макрос может записывать только то, что он видит на экране, и не может переходить и вносить изменения на других листах в документы, которые не выбраны. Однако вы можете сделать это при написании кода вручную.
Невозможно создать циклы : допустим, вы хотите, чтобы макрос перебирал каждую ячейку в диапазоне и удалял строку, если значение в ячейке — «Нет», и сохранял ее, если было «Да». Вы не можете сделать это с помощью макроса. Конечно, есть много способов сделать это (например, применить фильтр и удалить ячейки), но идею вы поняли. С помощью средства записи макросов вы не можете перебирать ячейки в цикле, что можно сделать, написав код вручную.
Невозможно проанализировать условия и предпринять действия на их основе : с помощью макроса записи, поскольку вы не можете перебирать ячейки в диапазоне, также невозможно анализировать условия и предпринимать действия на их основе. Например, предположим, что я хочу просмотреть все рабочие листы в документе Google Sheets и получить список всех имен рабочих листов на активном листе. Это невозможно сделать с помощью макроса записи, и вам придется писать для него код вручную.

Вас могут заинтересовать следующие статьи: