Полное руководство по автоматизации Google Sheets (макросы)

Макросы Google Sheets — это небольшие программы, которые вы создаете внутри Google Sheets без необходимости написания кода.

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

По-моему, звучит неплохо.

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

Содержание

1. Что такое макросы Google Sheets?
2. Зачем использовать макросы в Google Sheets?
3. Шаги для записи вашего первого макроса
4. Другие варианты
5. Заглянем под капот макроса Google Sheets
6. Примеры макросов Google Sheets
Заключение

1. Что такое макросы Google Sheets?

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

Все это отнимает ваше время, верно?

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

Как они работают:

Нажмите кнопку, чтобы начать запись макроса
Делайте свою работу
Нажмите кнопку, чтобы остановить запись макроса
Повторите процесс, когда вам нужно одним нажатием кнопки
Занимайтесь любимым делом все время, которое у вас освободилось

Они действительно так просты.

2. Зачем использовать макросы в Google Sheets?

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

3. Шаги для записи вашего первого макроса

Давайте пройдемся по процессу создания супер базового макроса, выполнив следующие шаги:

Откройте новый Google Sheet (вариант 1: введите sheet.new в браузере, чтобы мгновенно создать новый лист; вариант 2: в папке «Диск» нажмите Shift + s, чтобы мгновенно создать новый лист в этой папке. ). Введите несколько слов в ячейку A1.
Зайдите в меню макроса: Инструменты> Макросы> Запись макроса

У вас есть выбор между Абсолютными или Относительными ссылками. Для этого первого примера давайте выберем относительные ссылки:

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

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

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

Когда вы закончите, нажмите Сохранить и дайте вашему макросу имя:

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

Нажмите СОХРАНИТЬ еще раз, и Google Sheets запомнит ваш макрос.

Ваш макрос теперь доступен для использования через меню Инструменты > Макросы:

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

Сначала нажмите «Продолжить» во всплывающем окне «Авторизация»:

Затем выберите свой аккаунт Google:

Наконец, просмотрите разрешения и нажмите Разрешить:

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

Поздравляем с первым макросом Google Sheets! Видите, это было легко!

Вот быстрый GIF, показывающий процесс записи макроса в полном объеме:

И вот как это выглядит при запуске:

Полное руководство по автоматизации Google Sheets (макросы)

4. Другие варианты

4.1 Горячие клавиши

Это дополнительная функция при сохранении макроса в Google Sheets. Сочетание клавиш можно добавить позже через меню «Инструменты» > «Макросы» > «Настроить макросами».

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

В приведенном выше примере я мог запустить макрос, нажав:

Ctrl + Alt + Shift + 1

4.2 Удаление макросов

Вы можете удалить макросы Google Sheets со своего листа через меню управления макросами: Инструменты > Макросы > Настроить макрос

Под списком ваших макросов найдите тот, который вы хотите удалить. Нажмите на три вертикальные точки справа от макроса и выберите «Удалить макрос»:

4.3 Импорт других макросов

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

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

5. Заглянем под капот макроса Google Sheets

За кулисами макросы в Google Sheets преобразуют ваши действия в код Apps Script, который является всего лишь версией Javascript, запускаемой в Google Cloud.

Если вы хотите взглянуть на код, вы можете увидеть его, открыв редактор скриптов (Инструменты > Редактор скриптов или Инструменты > Макросы > Настроить макрос > Изменить скрипт).

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

/** @OnlyCurrentDoc */

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRangeList().setFontWeight('bold')
  .setFontColor('#38761d')
  .setFontSize(12)
  .setFontFamily('Roboto');
  spreadsheet.getActiveSheet().setColumnWidth(1, 135);
  spreadsheet.getActiveRangeList().setHorizontalAlignment('center')
  .setBackground('#d9ead3');
};

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

Затем макрос делает выделение жирным шрифтом (строка 5), зеленым (строка 6), размером шрифта 12 (строка 7) и, наконец, меняет семейство шрифтов на Roboto (строка 8).

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

Если вам интересно, строка / ** @OnlyCurrentDoc * / гарантирует, что процедура авторизации запрашивает только доступ к текущему файлу, в котором находится ваш макрос.

6. Примеры макросов Google Sheets

6.1 Форматирование таблиц

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

6.2 Создание диаграмм

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

Следующие макросы предназначены для копирования в редактор сценариев, а затем импорта в меню макросов и запуска оттуда.

6.3. Преобразовать все формулы в значения на текущем листе.

Откройте редактор скриптов (Инструменты > Редактор скриптов). Скопируйте и вставьте следующий код в новую строку:

// преобразовать все формулы в значения на активном листе
 function FormulasToValuesActiveSheet () {
   var sheet = SpreadsheetApp.getActiveSheet ();
   var range = sheet.getDataRange ();
   range.copyValuesToRange (sheet, 1, range.getLastColumn (), 1, range.getLastRow ());
 };

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

6.4 Преобразование всех формул в значения во всем Google Sheet

// преобразовать все формулы в значения на каждом листе Google Sheet
 функция FormulasToValuesGlobal () {
   var sheet = SpreadsheetApp.getActiveSpreadsheet (). getSheets ();
   sheet.forEach (function (sheet) {
     var range = sheet.getDataRange ();
     range.copyValuesToRange (sheet, 1, range.getLastColumn (), 1, range.getLastRow ());
   });
 };

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

6.5 Сортировка всех листов в Google Sheet по алфавиту

// сортировать листы по алфавиту
 function sortSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var sheetNames = [];
  sheets.forEach(function(sheet,i) {
    sheetNames.push(sheet.getName());
  });
  sheetNames.sort().forEach(function(sheet,i) {
    spreadsheet.getSheetByName(sheet).activate();
    spreadsheet.moveActiveSheet(i + 1);
  });
};

Вернувшись в свой Google Sheet, используйте параметр «Импортировать». Когда вы запустите его, он отсортирует все ваши листы в алфавитном порядке.

6.6 Показать все строки и столбцы в текущем листе

// показать все строки и столбцы в текущем диапазоне данных листа
 function unhideRowsColumnsActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  sheet.unhideRow(range);
  sheet.unhideColumn(range);
}

Используйте параметр «Импортировать». Когда вы запустите его, он покажет все скрытые строки и столбцы в диапазоне данных. (Если у вас есть скрытые строки / столбцы за пределами диапазона данных, они не будут затронуты.)

6.7 Показать все строки и столбцы во всем Google Sheet

// показать все строки и столбцы в диапазонах
 function unhideRowsColumnsGlobal() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    var range = sheet.getDataRange();
    sheet.unhideRow(range);
    sheet.unhideColumn(range);
  });
};

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

6.8 Установите для всех листов определенный цвет вкладки

// установить все вкладки Sheets на красный
 function setTabColor () {
   var sheet = SpreadsheetApp.getActiveSpreadsheet (). getSheets ();
   sheet.forEach (function (sheet) {
     sheet.setTabColor ( "FF0000");
   });
 };

Далее используйте параметр «Импортировать». Когда вы запустите макрос, он установит цвет всех вкладок, как красный.

Хотите другой цвет? Просто измените шестнадцатеричный код в строке 5 на какой хотите, например, васильковый будет 6495ed.

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

6.9 Удалите все цвета вкладок со всех листов

// удаляем все цвета вкладок Sheets
 function resetTabColor () {
   var sheet = SpreadsheetApp.getActiveSpreadsheet (). getSheets ();
   sheet.forEach (function (sheet) {
     sheet.setTabColor (null);
   });
 };

Затем используйте параметр «Импортировать». Когда вы запустите макрос, он удалит все цвета вкладок из вашего листа.

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

6.10 Скрыть все листы кроме активного

Скопируйте и вставьте этот код в редактор сценариев и импортируйте функцию в меню «Макрос»:

function hideAllSheetsExceptActive() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    if (sheet.getName() != SpreadsheetApp.getActiveSheet().getName()) 
      sheet.hideSheet();
  });
};

Запуск этого макроса скроет все листы, кроме того, который вы выбрали (активный лист).

6.11 Покажите все скрытые листы за один клик

function unhideAllSheets () {
   var sheet = SpreadsheetApp.getActiveSpreadsheet (). getSheets ();
   sheet.forEach (function (sheet) {
     sheet.showSheet ();
   });
 };

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

Вот GIF, показывающий, как работают макросы скрытия и отображения:

Лист3 (активный) — единственный, который не скрыт при запуске первого макроса.

6.12 Сброс фильтров

Это один из моих любимых макросов! 🙂

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

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

// сброс всех фильтров для диапазона данных на текущем листе
function resetFilter() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.getFilter().remove();
  range.createFilter();
}

Вернувшись обратно, используйте параметр «Импортировать. Когда вы запустите макрос, он удалит, а затем повторно добавит фильтры в ваш диапазон данных.

Вот GIF-файл, показывающий проблему и ее решение с помощью макроса:

Заключение

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

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

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

Наталья 07.01.2020 в 10:31

Добрый день! Спасибо за статью — очень полезная! Вы могли бы подсказать макрос для удаления всех скрытых листов?

Ответить
Lyudmila 05.04.2020 в 21:48

У меня планшет и что Вы там написали я не поняла

Ответить
Саша 09.07.2020 в 17:10

Спасибо!

Ответить