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

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

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

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

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

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

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

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

Скрипты и макросы таблиц Google, дополнение

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

Для этого зайдите в редакторе скриптов в меню «Ресурсы
» и выберите там «Триггеры текущего проекта
«. Откроется менюшка, в которой уже будет наша функция onLoad
. Добавляем новую функцию (1
) и задаем название функции (2
) и тип активации оной (3
). Также можно нажать на «Уведомления
» и добавить/убрать свой почтовый адрес из списка уведомлений.

Они будут приходить в случае, если скрипт выполнялся с ошибками.

Конечный результат действа:

Продолжения — -готовим и три. Ну и комментарии конечно содержат много вкусного.

P.S.
За существование оной статьи отдельное спасибо другу проекта и члену нашей команды под ником “barn4k“.

Google Apps Script — это язык для автоматизации работы с онлайн-приложениями, появившийся в 2009 году. Его основа — классический JavaScript, обогащенный расширениями для работы с сервисами Google. После прочтения этой статьи ты овладеешь основами использования этого языка, выучишь пару приемов манипуляции с почтой и документами, а также получишь представление о необозримых возможностях Google Apps Script.

Работа с Google Tasks

С помощью Google Apps Scripts можно работать с сервисом Google Task — создавать новые задачи и парсить уже имеющиеся.

Этот код создает новое дело в списке:

// Найти ID тасклиста можно внутри адресной строки в сервисе Google Task
var taskListId=»id тасклиста,»;
var newTask = {
title: «Выбросить финиковые косточки»,
notes: «Не забыть косточки под кроватью»
};
newTask = Tasks.Tasks.insert(newTask , taskListId);
Logger.log («Задача с ID «%s» создана», newTask.id);

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

// Кладем все задачи списка в массив
var tasks = Tasks.Tasks.list(taskListId);
for (var i = 0; i

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

Всего есть несколько десятков методов для работы с задачами. Полный их список доступен вGoogle Apps Script References для Google Tasks .

Как написать свой первый Google Apps script

Платформа Google Apps Script предназначена для разработки веб-приложений, и позволяет интегрировать в них все сервисы Google Cloud.

Что можно сделать с помощью приложения Google Script?

Вот лишь несколько примеров того, что умеет Google Script:

• Создавать пользовательские функции в Google Таблицах.
• Интегрировать Google-таблицы или Google Docs с почтовой службой Gmail.
• Создавать веб-приложения, которые можно развернуть с помощью Google Sites.
• Добавлять пользовательское меню в Google Docs.
• Создавать панели мониторинга трафика в Google Таблицах с использованием данных, получаемых от Google Analytics;
• Отправлять электронную почту из Google Таблиц или любой другой службы Google.

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

Написание первого Google Apps script

Ваш первый скрипт будет отправлять электронное письмо из аккаунта Google с сообщением, встроенным в Google Script. Для его написания:

1. Перейдите в браузере по адресу google.com .
2. Войдите в свой аккаунт Google.
3. Нажмите кнопку « Создать скрипт».
4. В поле « Проект без названия» вводим « Мой первый скрипт» .

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

Нажимаем « Сохранить» . Затем кликаем по иконке « Выполнить», чтобы запустить код. Затем предоставьте приложению разрешение на выполнение скрипта и отправку писем.

Данный скрипт использовал почтовую службу Gmail для отправки электронного письма из вашего аккаунта с помощью Google Apps Script.

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

Добавление других функций к службам Google

К Google Script можно получить доступ из многих других служб. Это позволяет расширить возможности данных сервисов. Например, в Google-таблицах можно получить доступ к редактору скриптов Google, перейдя в меню « Инструменты» — « Редактор скриптов» .

Также можно использовать Google Apps Script для создания надстройки Gmail, чтобы повысить удобство работы с электронной почтой: изменить окно сообщений, написать скрипт, который будет архивировать старые письма. Для этого необходимо активировать надстройки разработчика в вашем аккаунте Gmail. Переходим в « Настройки» , « Дополнения» и выбираем пункт « Разрешить устанавливать дополнения для разработчиков в этом аккаунте». Далее во всплывающем окне нужно кликнуть по кнопке « Включить».

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

Получение доступа к Google Script API

Из редактора Google Script можно получить доступ к каждой из служб Google. В приведенном выше примере приложения «Hello World» в качестве глобального объекта мы использовали приложение GmailApp.

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

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

Внизу страницы, на которой вы включили API Library, есть ссылка на справочную документацию . В ней приводятся примеры и синтаксис интеграции API в приложение Google Scripts.

Просмотр библиотеки API Library показывает, какое количество сервисов вы можете интегрировать в свои скрипты.

Что дальше?

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

Convert US dollars to Swiss francs

Suppose you wanted to modify the data for ‘Top 10 Highest Grossing Films 2018′ to show not only the worldwide gross values in U.S. dollars but also in Swiss francs. With custom functions, you can do that easily. The following exercise demonstrates how to create a custom function to mathematically convert your dollar values to franc values.

Before you can write your first custom function, modify your dataset to allow the function to demonstrate a proper output. To do this:

1. In Sheets, right-click column H.
2. In the resulting menu, click Insert 1 right.

3. Label the column «Worldwide_Gross (Swiss francs)» in cell I1.

Now you have a column that can store the results of your conversion custom function. Next, you can use the script editor to create your first custom function.

4. In , replace the code for with the following code:

This is the code that will convert U.S. dollars to Swiss francs. Try the instructions below, and see how you can run a custom function in sheets.

5. To save your script, click Save .
6. In Sheets, select the I2 cell.
7. In the function bar, enter .

To apply the formula to the rest of the cells in the column:

8. Move your cursor to the bottom-right corner of the I2 cell and select the small blue box (your cursor should transform into when pointing to the blue box).
9. Drag the blue box downwards to highlight the range I3:I11.

Column I now lists the Swiss franc conversions of the US dollar values in Column H.

Congrats, you’ve created your first custom function. The next section explains the code that comprises .

Analysis of

The initial comments detail the code’s purpose:

Comment blocks like this are used frequently in programming to explain what functions do.

In this comment, you can identify two parts: the function description (to convert dollars to francs) and annotations that describe the function’s parameters and return type.

With the annotations, Apps Script utilizes JSDoc to help you document and create autocomplete hints for your code. You can read below how each annotation used in helps you with your Apps Script development:

• : You can use the annotation to describe each parameter passed into the function.
• : You can utilize the annotation to describe what the function returns.
• : You should always add in any custom function’s doc comment. This annotation notifies Sheets to your custom function just as Sheets autocompletes built-in functions when you enter a function name in a cell as seen below:

Notice the text that appears in the autocomplete pop-up exactly matches the description text you placed in the comment block. You can make your custom functions easier to use by making sure the descriptions you create are well-written and complete.

Next, focus on the code in the function :

As mentioned previously, takes the numeric variable dollars, multiplies it by a fixed exchange rate, and returns a value converted to Swiss francs in the numeric variable . The input parameter is the value contained in the cell specified when adding the custom function to a cell. In this example, the input dollar amounts are coming from column H. The output value is placed in the function’s cell (column I in this example).

Custom functions can work with numeric or string values, as you’ll see in the next section.

Google Apps Script Resources

For further reading, I’ve created this list of resources for information and inspiration:

Course

Learn moreLearn how to write Apps Script and turbocharge your Google Workspace experience with the new Beginner Apps Script course

Elsewhere On The Internet

A huge big up-to-date list of Apps Script resources hosted on GitHub.

For general Javascript questions, I recommend this JavaScript tutorial page from W3 Schools when you’re starting out.
​
When you’re more comfortable with Javascript basics, then I recommend the comprehensive JavaScript documentation from Mozilla.

Imagination and patience to learn are the only limits to what you can do and where you can go with GAS. I hope you feel inspired to try extending your Sheets and Docs and automate those boring, repetitive tasks!

Первый скрипт

Создаем свой первый скрипт.

Можно, конечно, сходить вот по этой ссылочке тыц и внимательно почитать что пишут знающие люди, или…

Не ходить, а просто отдаться в руки автора (т.е. в мои)) ) и читать дальше…

Тем, кто никогда не пробовал свои силы в программировании, наверняка, “лаконичное” окно редактора покажется совершенно загадочным и возможно некоторым даже захочется сбежать с криками: “Меня обманули, говорили, что это просто!!!”

Не спешите смазывать лыжи! Это действительно просто, если, конечно, знать что делать.

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

Предлагаю чуть, отступить от канонов и вывести куда-нибудь сразу перевод знаменитого приветствия…ну, скажем, на суахили!

Для начала меняем название функции с myFunction на firstTest.

Далее пишем вот такую аБрА-КадабРу:

function firstTest () {
  return Logger.log( LanguageApp.translate("Hello World!", "en", "sw") );
}

Давайте распишем тот же пример более подробно и разберем каждую строку

function firstTest () {
    var text = "Hello World!",
        text_language = "en",
        language_of_translation = "sw";
	
    text = LanguageApp.translate(
        text,
        text_language,
        language_of_translation
    );
	
    Logger.log( text );

    return;
}

Строка 1. Директива function создает новую функцию, далее указываем ее название firstTest.

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

А в фигурных скобках {} указывается код который выполняется когда эта функция будт вызвана.

function firstTest () {
    // Тут код функции
}

Если поставить два слеша //, то код после них и до конца строки выполнятся не будет.

Это называется комментарий.

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

Строки 2-4. Объявляем переменные с помощью директивы var.

• Переменная text со значением “Hello World!” — это текст, который требуется перевести,
• text_language со значением “en” — код языка на котором написан текст, если оставить его пустым (вот так “”), язык будет определяться автоматически,
• И language_of_translation со значением “sw” — код языка на который будет переведен текст.

(коды языков можно подсмотреть здесь)

var text = "Hello World!",
    text_language = "en",
    language_of_translation = "sw";

Строки 6-10. Обращаемся к объекту LanguageApp, а точнее к методу этого объекта translate, который принимает три параметра (их мы уже описали выше в строках 2-4).

Если некоторые термины вам непонятны и/или у вас нет базовых знаний JavaScript или другого языка, не расстраивайтесь!

В конце статьи я дам ссылку на простой учебник по JS.

Результат

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

text = LanguageApp.translate(
    text,
    text_language,
    language_of_translation
);

Строка 12. Logger.log( text ) — это вывод нашего текста в журнал, где потом мы и будем его смотреть.

Logger.log( text );

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

Строка 14. Необязательная директива return — возвращает данные, указанные поле нее (мы ничего не указали, а это значит что по умолчанию возвратится false, с таким же успехом данную директиву можно было не указывать).

    return;

Продолжим, наша программа написана, теперь сохраним ее нажав на эту кнопку (иконка дискеты)

К вам тут же выскочит окошко.

В нем вводим название проекта в поле Укажите новое название проекта, пусть будет Привет мир!. Затем жмем на кнопку ok

Теперь запустим ее, сначала выбрав нужную функцию а затем нажав на вот тут (иконка треугольник)

И наконец, идем во вкладку Вид > Журналы или просто нажимаем Ctrl+Enter (горячии клавиши полезная штука).

И вот результат наших стараний…

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

а красным наше содержимое переменной text.

Да, да — Salamu, Dunia! И есть перевод знаменитого Hello World на суахили.

Задача выполнена!

Овации! Чувствуете гордость? Совершенно заслуженно.

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

Согласитесь, без ложной скромности, что это было не так уж и сложно.

Остались вопросы?

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

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

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

СВЯЗАННЫЕ: Как перезарядить ваши Google Apps с помощью редактора скриптов

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

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

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

What is Apps Script?

Apps Script is a rapid application development platform that gives you the ability to automate, customize, and extend your Google Workspace experience. With Apps Script, you can save time and effort by streamlining onerous or complex work within Google Workspace.

include the following:

• Apps Script’s built-in services let you read, update, and manipulate your Google Workspace application data with scripts.
• You can create scripts using Apps Script’s in-browser code editor—there’s no need to install or run code development software.
• You can design user interfaces for Google Workspace editors that let you activate scripts directly from those editors using menu items, dialogs, and sidebars.

This Fundamentals of Apps Script with Google Sheets codelab playlist teaches the basics of Apps Script and how to use it to improve your Google Sheets experience. This codelab focuses on teaching Apps Script basics.

Importing functions as macros

If there is already a script bound to a sheet,
you can import a function in the script as a new macro and then assign it
a keyboard shortcut. You can do this by

file and adding another element to the
property.

Alternatively, follow these steps to import a function as a macro from the
Sheets UI:

1. In the Google Sheets UI, select Extensions >
   Macros > Import.
2. Select a function from the list presented and then click Add function.
3. Select clear to close the dialog.
4. Select Extensions > Macros
   > Manage macros.
5. Locate the function you just imported in the list. Assign a unique keyboard
   shortcut to the macro. You can also change the macro name here; the name
   defaults to the name of the function.
6. Click Update to save the macro configuration.

Как перейти с Excel на Таблицы

Как использовать макросы и дополнения

Далее: Как использовать Таблицы для совместной работы

Хотите использовать сервисы Google на работе или в учебном заведении?  Оформите пробную подписку на Google Workspace.

Как автоматизировать задачи с помощью макросов

Excel: отдельные сообщения

Таблицы: макросы и Google Apps Script

Excel 2013 и 2010

Автоматизируйте повторяющиеся задачи с помощью макросов в Таблицах. Если вам необходимы специальные функции, меню или окна, создайте их с помощью Google Apps Script. Рекомендации по работе с макросами Для эффективной работы макроса сократите число действий в нем. Используйте макросы для часто повторяющихся операций, которые не требуют сложной настройки. Используйте уникальные клавиши для запуска макросов. Для таблицы можно задать до 10 таких клавиш. Открывайте дополнительные макросы из меню Инструменты Макросы. Сведите к минимуму копии макросов. Для этого применяйте макрос, созданный для ячейки, к диапазону ячеек, выбрав весь их диапазон, и активируйте макрос.  Скрипты макросов связываются с конкретной таблицей и могут использоваться только в Таблицах и не будут работать в Google Документах, Формах или Презентациях. Как записать макрос Откройте файл в Таблицах. В верхей части экрана нажмите Инструменты Макросы Записать макрос. Внизу экрана выберите тип ссылки: Использовать абсолютные ссылки. Действия макроса применяются только к выбранным вами ячейкам. Например, если вы начнете запись с ячейки B1 (запись начинается, как только вы помещаете курсор в ячейку), а затем перейдете к ячейке D1 и измените шрифт на полужирный, при выполнении макроса данные в ячейке D1 всегда будут выделяться полужирным шрифтом. Использовать относительные ссылки. Действия макроса применяются к выбранной вами ячейке и к другим ячейкам, в которых выполнялись операции. Например, если вы начнете запись с ячейки B1 и добавите формулу, а затем перейдете к ячейке D1 и измените шрифт на полужирный, каждый раз при выполнении макроса будет добавляться формула, затем курсор будет смещаться на два столбца вправо, и данные в этой ячейке будут выделяться полужирным шрифтом. Выполните все действия, которые хотите записать. Закончив, нажмите кнопку Сохранить. Укажите название макроса. При необходимости создайте ярлык для макроса. Нажмите Сохранить.Примечание. Когда вы записываете макрос в Google Таблицах, скрипт создается с помощью Google Apps Script. Чтобы изменить его, выберите вверху страницы Инструменты Редактор скриптов. Чтобы запустить макрос, нажмите Инструменты Макросы ваш_макрос. Как создать скрипт Подробнее о Google Apps Script, специальных функциях и макросах…

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

Excel: как изменять макросы

Таблицы: как повторно создать макросы в Таблицах

Excel 2010 и 2013

Вы можете преобразовать макросы из таблицы Microsoft Excel в Google Таблицах с помощью Google Apps Script. Apps Script позволяет создать макросы в Таблицах аналогично тому, как это можно сделать в Excel с помощью Microsoft Visual Basic for Applications. Как создать и изменить макрос в Таблицах с помощью Apps Script Как активировать макрос в Таблицах Совет. Дополнительная информация о том, как библиотека Apps Script может имитировать основные функции Visual Basic for Applications (VBA), приведена в блоге для разработчиков G Suite (только на английском языке).

Как расширить функции Таблиц с помощью дополнений

Excel: дополнения

Таблицы: дополнения

Excel 2013   Excel 2010

С помощью готовых дополнений вы можете расширить функции Таблиц. Например: Чтобы подключить таблицу к Salesforce, используйте коннектор данных для Salesforce. Чтобы подключить MySQL и Oracle, используйте Supermetrics. Чтобы интегрировать данные Copper CRM, используйте Copper CRM Custom Report Builder. Чтобы интегрировать данные о найме из Greenhouse, используйтеGreenhouse Report Connector. Как скачать дополнения

_{Google, Google Workspace, а также другие связанные знаки и логотипы являются товарными знаками компании Google LLC. Все другие названия компаний и продуктов являются товарными знаками соответствующих компаний.}

Advanced Google Services

У Google есть множество API для разработчиков, которые можно внедрять в программы, написанные на Google Apps Script. Для этого надо подключить в редакторе скриптов эту возможность (в меню Resources, далее Advanced Google services). После этого можно будет задействовать возможности следующих сервисов:

• Google AdSense — сервис для работы с контекстными рекламными объявлениями.
• Google Analytics — осуществляет анализ посещаемости веб-сайтов и мобильных приложений.
• Google BigQuery — позволяет производить различные манипуляции (запись, чтение, перемещение и так далее) над большими объемами данных, а также анализировать их.
• Google Fusion Tables — экспериментальный сервис, позволяющий размещать данные в облаке, отправлять к ним запросы и получать результаты выполнения в формате JSON и CSV. Из которых, в свою очередь, можно формировать электронные таблицы, карты, графики и другие виды визуального представления данных.
• Google Domains — сервис для регистрации доменов (новый проект, открылся в конце июня 2014 года).
• Google Mirror — API для взаимодействия с Google Glass.
• Google Prediction — сервис для анализа данных (основанный на технологии машинного обучения). Позволяет внедрять в приложения следующие фичи: классификатор документов и писем, расчет churn rate (показатель оттока пользователей), детектор спама, оптимизатор маршрутизации сообщений и множество других интересных вещей, достойных отдельной статьи.
• Google Tasks — встроенный в Gmail сервис для составления списков дел.
• Google URL Shortener — любимый нашим журналом сервис для сокращения длинных ссылок.
• YouTube Analytics — сервис для анализа статистики просмотров видео на YotTube. Примечателен возможностью узнать демографические и географические характеристики пользователей, смотрящих определенный видеоролик. Ведь интересно выложить очередную копию клипа на песню «До свидания, кореша» и проанализировать, какого пола, возраста и места жительства ее слушатели.

What is Google Apps Script (GAS)?

Google Apps Script is a coding language and allows you to create automation and functions for Google Apps (which can include Google Sheets, Google Docs, Google Forms, Drive, Maps, Calendar, etc.)

In this tutorial (and on this site), I will be focussing on using Script for Google Sheets. However, most of what I cover here can also be used for other Google Apps.

This Google Apps Script (GAS) coding language uses Javascript and is written in the back-end of these Google Sheets (there is a neat interface that allows you to write or copy/paste the code in the back-end).

Since Google Sheets (and other Google Apps) are cloud-based (i.e., can be accessed from anywhere), your Google Apps Script is also cloud-based. This means that if you create a code for a Google Sheets document and save it, you can access it from anywhere. It doesn’t reside on your laptop/system but on Google cloud servers.