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

Как автоматизировать Google Таблицы с помощью макросов

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

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

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

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

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

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

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

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

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

«Макросы»> «Записать макрос» »width =« 480 ″ height = «291 ″ onload =» pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); » onerror = ”this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);”>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Снова нажмите «Сохранить».

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

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

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

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

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

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

Настройка макросов в Google Таблицах

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

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

Важность автоматизации

Автоматизация используется не только в производстве или исследованиях. Он также все чаще используется в приложениях для работы с электронными таблицами (Microsoft Excel, Google Sheets, Libre Calc) для задач с программным обеспечением. Автоматизация — хищник черных, повторяющихся задач, и офисная работа полна этой пищи.

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

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

Google Таблицы

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

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

Макрос

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

В Google Таблицах вы можете настроить макросы одним из двух способов.

1. Макро запись

​

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

1. Чтобы открыть Google Таблицы, перейдите в docs.google.com/spreadsheets.
2. Перейти к Инструменты> Макросы> Записать макрос.
3. Нажмите Ctrl + B (жирный).
4. Нажмите Ctrl + I (курсив).
5. Установите красный цвет текста.
6. Установите размер шрифта 18.
7. Убедитесь, что ссылка установлена ​​на Родственник.
8. Нажмите Сохранить.
9. Введите имя для макроса и установите номер ярлыка. Вы можете ввести не более 10 макросов.

Чтобы выполнить макрос, нажмите Ctrl + Alt + Shift + (выбранное число). Текст внутри выделенной ячейки будет отформатирован полужирным, курсивным, красным шрифтом, а размер будет равен 18.

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

2. Написание сценария

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

1. Чтобы открыть электронную таблицу, перейдите в docs.google.com/spreadsheets.
2. Перейти к Инструменты> Редактор скриптов.
3. Ниже / ** @OnlyCurrentDoc * / вставьте:
   function FormatText () <var spreadsheet = SpreadsheetApp.getActive ();

spreadsheet.getActiveRangeList (). setFontWeight («полужирный»)

.setFontStyle (курсив)

.setFontColor (‘# ff0000’)

.setFontSize (18);

Результат должен выглядеть так:

​

Чтобы выполнить макрос, просто нажмите Ctrl + Alt + Shift + (выбранное число). Или вы можете перейти к Инструменты> Макросы> (Имя макроса) выполнить вручную.

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

Заключение

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

Как записать макрос в 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 (которая находится на одну ячейку слева от выбранной ячейки).

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

• Ігор Білецький
• 22.11.2021
• Комментариев нет

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

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

Рассмотрим такие вопросы:

Операции с таблицей

Для того, чтобы програмно работать с таблицей, нужно к ней обратиться. Рассмотрим несколько способов.

Обратимся к активной таблице (в которой работаем в текущий момент). Для этого используем конструкцию:

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

У нас в переменной activeSheet будет ссылка на активный лист таблицы

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

Я предпочитаю использовать идентификатор. Как узнать идентификатор гугл-таблицы, можно прочитать ЗДЕСЬ.

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

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

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

Информацией о том, что и как я использовал в своих проектах, а также разные полезности (создание, удаление и пр.), описаны ЗДЕСЬ, а сейчас продолжим.
.

Операции с листом.

Прежде, чем работать непосредственно с данными, нам нужно обратиться к листу, где собственно эти данные находятся. Здесь также имеем несколько способов.

Обращаемся к коллекции листов в активной таблице и берем первый (нумерация начинается с нуля)

Обращаемся к активному листу

Обращаемся к листу по имени (в примере «Лист1»).

Естественно, что подобными способами вы можете обращаться к листам в другой (не активной) таблице. Но есть один нюанс, который касается способа номер 2 (обратиться к активному листу).

Если вы обращаетесь к другой таблице (открываете по идентификатору или ссылке) и обращаетесь к активному листу (getActiveSheet()), вы получаете первый лист, даже если при этом таблица будет открыта у вас в соседней вкладке на нужном вам листе. Поэтому, лучше четко обозначать лист, к которому вы обращаетесь (либо по имени, либо по номеру).

Дополнительные материалы по работе с листами (удаление, переименование, копирование и пр.) — в отдельной статье.
.

Операции с ячейкой.

Обращаемся к ячейке.

В первом варианте мы обращаемся с ячейке «A1», указав явно ее адрес (А1), во втором используем номер строки и колонки (1-я строка, 1-я колонка). Сначала указываем номер строки, затем номер колонки.

Т.е., чтобы обратиться к первой ячейке в третьей строке, нужно указать (3,1).

Предпоследний и последний способ — получить ячейку, активную (выделенную) в текущий момент.

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

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

Теперь, когда мы имеем в переменной myCell нашу ячейку, мы можем делать с ней ряд действий, например.

1. Вписать в ячейку нужное значение:

Вписываем в нашу ячейку строку «Новое значение»

2. Получить текущее значение ячейки:

Сохраняем в переменную a1 значение ячейки A1

Обратите внимание, что в первом случае используется метод setValue (от англ. установить значение), во втором — getValue (от англ. взять значение).

В дополнение к сказанному. Есть метод «getDisplayValue», который возвращает значение ячейки в том виде, в каком их видно на экране. Это значит, что если вы например применили к числу форматирование и видите на экране в ячейке что-то типа «1 078 234,13», то вы это же и получите в переменной, если используете «getDisplayValue». Это также касается и использования форматирования для ячеек с датой и временем. Однако, не все так просто и могут быть нюансы.

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

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

и узнать фон заливки ячейки:

В данном примере я использовал директиву Logger.log для вывода информации в консоль. Это иногда очень удобно при отладке программного кода. Хочу обратить ваше внимание, что цвет заливки выводится в формате #ffff00 (соответствует желтому цвету).

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

Список всех доступных методов открывается, когда вы в редакторе кода после переменной с ссылкой на нашу ячейку ставите точку.

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

Пояснение. К примеру, если вы ранее в коде в переменную myVar сохранили ссылку на лист, то можно получить список всех доступных методов и свойств листа, поставив в редакторе после имени переменной точку ( myVar. ).

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

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

Один из способов что-то узнать, если забыл (или просто не знаешь), использовать встроенный инструмент «Записать макрос».

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

Теперь вы можете открыть код макроса и подсмотреть, что и как реализуется. Я иногда пользуюсь таким способом, чтобы быстро что-то узнать ��

Примечание. Чтобы открыть редактор скриптов, необходимо воспользоваться пунктом меню «Расширения -> Apps Script»:

Операции с диапазоном.

Обратиться к диапазону.

Получаем в переменной myRange нужный диапазон.

Расшифровка. В переменную myRange попадает диапазон c началом в ячейке с координатами (1,1) и окончанием в ячейке с координатами (2,3) — вторая строка, третья колонка.

Важное уточнение. В директиве getRange указывается: номер строки (начало диапазона), номер колонки (начало диапазона), кол-во строчек (размер диапазона по вертикали, кол-во колонок (размер диапазона по горизонтали).

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

То наш диапазон имеет координаты (3 — номер строки (начало), 4 — номер колонки (начало), 12 — номер строки (конец), 8 — номер колонки (конец).

Почему не 13-я строка, а 12-я? Потому что, размерность по вертикали — 10. От строки номер 3, включая саму строчку номер 3 — 12 строка — как раз выходит 10 строчек. По горизонтали — то же самое.

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

Закрасим диапазон желтым и посмотрим, правильно или нет определились с размерами ��

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

На помощь приходит метод, который позволяет получить номер последней строки, где на листе есть данные — «getLastRow» (получить последнюю строчку). Как вы помните, в методе getRange предпоследним параметром идет кол-во строк (размер диапазона по вертикали).

Если в нашей условной табличке со списком клиентов данные начинаются со второй строки (в первой — оглавление таблицы), то кол-во строк в таблице равно getLastRow() — 1.

Для определения правой границы таблицы можна воспользоваться getLastColumn — номер последнего столбца с данными (крайний правый).

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

К примеру, у вас таблица заканчивается (правая граница) на колонке F. В таком случае получить диапазон:

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

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

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

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

Если мы работаем напрямую с диапазоном (с помощью объекта Range), мы имеем по сути массив, каждым елементом которого есть ячейка, которая входит в наш диапазон.

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

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

Соответственно, что-то записать в левую верхнюю ячейку можно так:

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

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

На всякий случай напоминаю, что в переменной sheet лежит ссылка на наш активный лист.

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

Вначале нам необходимо узнать размеры нашего диапазона — кол-во строчек и столбцов. Для этого есть методы «getNumRows» (получить кол-во строк) и «getNumColumns» (получить кол-во столбцов).

В приведенном коде мы перебираем подряд все ячейки выделенного диапазона и каждой присваиваем значение — от 1 до общего кол-ва ячеек в нашем диапазоне.

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

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

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

Так получается намного быстрее. А учитывая тот факт, что время работы скрипта ограничено 6-ю минутами, иногда это важно для решения определенных задач.

Дополнительные возможности при работе с диапазонами и массивами описаны в отдельной статье. Здесь же я отмечу, что для того, чтобы считать значения диапазона в массив, необходимо использовать метод «getValues».

Например, сохраним в массив выделенный диапазон:

Или более читабельный вариант (учитывая созданные ранее переменные)

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

Создание своего меню

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

В данном разделе коснемся создания своего меню.

В гугл таблицах пользовательское меню бывает двух типов:

• пользовательское меню — создается, как отдельное меню
• дополнительное меню — пункт меню, который можно добавить в уже существующее на листе меню «Дополнения»

Скриншот обычного меню:

Скриншот дополнительного меню:

В качестве «донора» для скриншотов была использована таблица «Ведение семейного бюджета» — расширенная версия.

Когда и какие виды меню использовать — зависит от поставленных задач. От себя добавлю, что меню создается с помощью скрипта. Часто скрипт, который создает требуемое меню, запускается при открытии гугл таблицы — используя специальное событие «открытие таблицы».

Более детально об использовании различных меню с примерами кода можно посмотреть ЗДЕСЬ.

Далее коснемся вопроса работы с событиями гугл таблицы.
.

Работа с событиями таблицы

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

Например, когда вы открываете таблицу, либо редактируете данные, возникает событие «Открытие таблицы» или «Редактирование таблицы» соответственно.

Следовательно, вы можете использовать событие — запустить нужный вам скрипт.

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

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

Подробнее об использовании событий с примерами кода читайте ЗДЕСЬ.
.

Готовые решения, наработки, примеры кода

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