Учебное пособие по скриптам Google Apps для освоения макросов

Эффективные руководители знают, что ограничивающим фактором является время… Пожалуй, ничто другое не отличает эффективных руководителей так, как их нежная и любовная забота о времени.

Питер Друкер

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

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

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

Вдохновившись этим, я хочу представить учебник по скриптам Google Apps. Google Apps Script позволяет вам писать скрипты и программы на JavaScript для автоматизации, подключения и расширения продуктов в Google G Suite, включая Sheets, Docs, Slides, Gmail, Drive и некоторые другие. Его изучение требует затрат времени, как и написание сценариев, но продуктивность возрастает, а дополнительные возможности, которые оно открывает, оправдывают это.

В качестве первого шага давайте рассмотрим знакомую концепцию: макросы.

Запись и использование макросов в Google Sheets

Если вы потратили значительное время на работу с Excel, то в какой-то момент вы обязательно столкнулись с интерфейсом макросов Excel VBA (Visual Basic для приложений). Либо путем записи или написания их самостоятельно, либо путем добавления созданных другими.

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

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

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

Давайте начнем с некоторых примеров данных, используя запрос IMPORTHTML для импорта таблицы. В этом примере я скачал из Википедии список 15 крупнейших хедж-фондов мира. Само собой разумеется, но это произвольный пример; цель состоит в том, чтобы вы больше сосредоточились на приложении, а не на предмете.

Процесс записи макроса запускается по следующему пути меню: Инструменты > Макросы > Записать макрос.

Затем мы проходим через действия (в формате ПК), которые мы хотим записать:

1. Выберите первую строку
2. Нажмите Shift + Ctrl + стрелка вниз, чтобы выбрать все
3. Ctrl + C, чтобы скопировать
4. Shift + F11, чтобы создать новый лист
5. Дайте листу новое имя
6. Нажмите Shift + Control + V, чтобы вставить значения

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

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

Написание скрипта Google Apps вручную

Теперь мы впервые увидим костяк Google Apps Script; платформа программирования, работающая на серверах Google. Это поддерживает наши макросы и позволяет создавать очень сложные рабочие процессы и даже надстройки для самих приложений. Его можно использовать для автоматизации не только работы с электронными таблицами, но и почти всего, что связано с G Suite от Google.

Языком программирования Apps Script является JavaScript , один из самых популярных языков программирования, а это означает, что существует множество ресурсов для всех, кто хочет всесторонне изучить. Но, как и в случае с VBA, вам это не нужно: вы можете использовать ту же функцию записи и просто выполнять шаги, которые вы хотите, чтобы они могли повторяться автоматически. Результат записи может выглядеть грубым и, скорее всего, не будет полностью соответствовать тому, что вы хотите сделать, но он послужит достаточно надежной отправной точкой. Давайте теперь сделаем это для сценария, который мы только что записали.

При записи имеет смысл быть осторожным, чтобы случайно не записать какие-либо дополнительные шаги, которые вы не хотите зафиксировать в окончательной записи, но иногда этого трудно избежать: достаточно просто выбрать другую ячейку перед нажатием кнопки «Стоп». Кнопка записи будет захвачена и впоследствии повторяться каждый раз, когда вы запускаете скрипт. Первым шагом при редактировании нашего скрипта будет его очистка и удаление любых подобных шагов. Давайте погрузимся, перейдя в Инструменты> Редактор сценариев в меню файла.

Если вы знаете JavaScript, вы сразу узнаете это, и вы также можете быть удивлены, увидев ключевое слово «var» вместо «let» или «const», как в современном JavaScript. Это отражает тот факт, что версия JavaScript в Apps Script довольно старая и не поддерживает многие из последних функций языка. Ближе к концу я представлю обходной путь для тех, кто хотел бы использовать самые последние функции языка.

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

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

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

 function createSnapshot() { var spreadsheet = SpreadsheetApp.getActive(); var date = new Date().toISOString().slice(0,10); var destination = spreadsheet.insertSheet(date); spreadsheet.getRange('HTML!A1:F1').activate(); spreadsheet.getSelection() .getNextDataRange(SpreadsheetApp.Direction.DOWN) .activate(); spreadsheet.getActiveRange() .copyTo(SpreadsheetApp.setActiveSheet(destination) .getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false); var sheet = spreadsheet.setActiveSheet(destination) sheet.getRange("D1").setValue("AUM $bn") sheet.setHiddenGridlines(true); sheet.getRange("A1:D1").setFontWeight("bold"); sheet.autoResizeColumns(1, 4); };

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

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

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

 function createColumnChart() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('C1:D16').activate(); var sheet = spreadsheet.getActiveSheet(); chart = sheet.newChart() .asColumnChart() .addRange(spreadsheet.getRange('B1:D16')) .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS) .setTransposeRowsAndColumns(false) .setNumHeaders(-1) .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH) .setOption('useFirstColumnAsDomain', true) .setOption('curveType', 'none') .setOption('domainAxis.direction', 1) .setOption('isStacked', 'absolute') .setOption('series.0.color', '#0b5394') .setOption('series.0.labelInLegend', 'AUM $bn') .setPosition(19, 6, 15, 5) .build(); sheet.insertChart(chart); };

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

Примеры расширенных сценариев Google Apps: подключение таблиц к Google Диску и слайдам

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

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

Теперь это упражнение становится более сложным по двум причинам:

1. Нам нужно будет ознакомиться с тем, как работать с Google Slides (и Google Drive) в дополнение к Sheets.
2. В Презентациях или при работе между приложениями G Suite в целом функция «Запись макроса» недоступна. Это означает, что вам нужно достаточно знать о Apps Script (и уметь ориентироваться в документации по каждому из продуктов G Suite), чтобы писать сценарии с нуля.

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

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

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

https://docs.google.com/presentation/p/this_is_your_presentation_ID/edit#slide=id.p .

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

 function createPresentation() { var template; var template = DriveApp.getFileById(templateId); var copy = template.makeCopy("Weekly report " + date).getId(); var presentation = SlidesApp.openById(copy); }

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

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

 presentation.getSlides()[0] .getPageElements()[0] .asShape() .getText() .setText("Weekly Report " + date);

Теперь все становится немного интереснее, так как мы изменили первую страницу, чтобы включить сегодняшнюю дату. В Slides, как и в Sheets, вы работаете с объектами (представленными классами), каждый из которых имеет свойства и методы (т. е. присоединенные функции). Они организованы в иерархию, где SpreadsheetsApp, DriveApp или SlidesApp являются объектами верхнего уровня. В приведенном выше фрагменте кода нам нужно шаг за шагом перемещаться по этой иерархии, чтобы добраться до элемента, который мы хотим отредактировать, в данном случае: текст в текстовом поле. На практике это означает обращение к объектам Presentation, Slide, PageElement и Shape, пока мы, наконец, не доберемся до объекта TextRange, который хотим отредактировать.

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

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

 var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getSheetByName(date); var chart = sheet.getCharts()[0]; var position = {left: 25, top: 75}; var size = {width: 480, height: 300}; presentation.getSlides()[1] .insertSheetsChart(chart, position.left, position.top, size.width, size.height);

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

Надеемся, что этот пример иллюстрирует принципы и вдохновляет вас на то, чтобы начать собственные эксперименты. Если подумать, я уверен, что вы сможете найти по крайней мере несколько примеров ручной работы, выполняемой сегодня в вашей компании, которую действительно следует автоматизировать таким образом. Служит для того, чтобы освободить время для размышлений, анализа и принятия суждений, а не для механического перетасовки данных из одного формата и/или места в другой. Улучшение процесса разработки Как упоминалось ранее, версия JavaScript, поддерживаемая в Google Apps Script, устарела, а функциональные возможности интерактивного редактора сценариев очень ограничены. Если вы просто записываете макрос или пишете несколько десятков строк, вы этого даже не заметите. Однако, если у вас есть амбициозные планы по автоматизации всех аспектов вашей еженедельной или ежемесячной отчетности или вы хотите создавать плагины, то вы будете рады узнать, что существует инструмент командной строки, который позволяет вам разрабатывать с использованием вашей любимой среды разработки. .

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

Использование Python для программирования Google Sheets

Если вы обнаружите, что работа с Apps Script вам не по душе, есть и другие варианты, в зависимости от варианта использования. Если вы хотите выполнять более сложную обработку чисел, подключаться к API или базам данных или просто предпочитаете язык программирования Python, а не JavaScript, то Google Colaboratory — бесценный продукт. Он дает вам блокнот Jupyter, работающий на серверах Google, который позволяет вам писать сценарии Python, которые легко интегрируются с вашими файлами Google Диска, а через библиотеку «gspread» упрощает работу с данными ваших электронных таблиц.

Я описал многие преимущества Python в статье о том, как использовать его для финансовых функций, которая также служит нежным введением в работу с блокнотами Python и Jupyter в деловом и финансовом контексте. Для меня очень важным преимуществом является то, что, в отличие от Apps Script, записная книжка Python в Colaboratory является интерактивной, поэтому вы видите результаты (или сообщение об ошибке) после выполнения каждой строки или небольшого блока кода.

Автоматизация вызывает привыкание

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

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

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