Оптимизируйте свои подборки Сохраняйте и классифицируйте контент в соответствии со своими настройками.
Apps Script – это платформа для быстрой разработки приложений, позволяющая автоматизировать, настраивать и расширять возможности Google Workspace . С помощью Apps Script вы можете сэкономить время и усилия, оптимизируя обременительную или сложную работу в Google Workspace.
Этот плейлист «Основы скрипта приложений с кодовой лабораторией Google Sheets» учит основам скрипта приложений и тому, как его использовать для улучшения работы с Google Sheets. Эта лаборатория кода фокусируется на обучении основам скриптов приложений.
Вы можете использовать Apps Script для расширения Google Таблиц , чтобы сэкономить время и усилия. Apps Script предоставляет службу электронных таблиц, которая позволяет сценариям взаимодействовать с вашими файлами Google Sheet и содержащимися в них данными. Вы можете использовать эту службу для автоматизации следующих распространенных задач работы с электронными таблицами:
Этот плейлист охватывает все темы, которые вам понадобятся, чтобы начать использовать Apps Script с Google Sheets:
Лаборатории кода в этом плейлисте предназначены для чтения по порядку, поэтому начните с этого и выполняйте их последовательно для лучшего обучения.
Перейдите к следующему разделу, чтобы узнать больше о содержании этой лаборатории кода.
Добро пожаловать в первую лабораторию этого плейлиста. В этой лаборатории кода вы изучите основы использования Apps Script с Google Sheets. В частности, в этой лаборатории кода основное внимание уделяется двум ключевым понятиям: макросам и пользовательским функциям .
Макрос — это серия записанных действий в Google Таблицах. После записи вы можете активировать макрос, чтобы повторить эти действия позже с помощью пункта меню или сочетания клавиш. Вы можете создавать и обновлять свои собственные макросы как в Google Sheets, так и в редакторе кода Apps Script.
В редакторе кода Apps Script вы также можете создавать собственные функции . Подобно встроенным функциям Sheets (таким как SUM или AVERAGE ), вы можете использовать Apps Script для написания собственных пользовательских функций для простых и специализированных операций (таких как преобразования или объединение строк). После создания вы можете вызывать эти функции в Таблицах так же, как и встроенные функции. Пользовательские функции также можно использовать в формулах ячеек, которые вы пишете, комбинируя их с другими функциями по мере необходимости.
Прочтите ниже, чтобы узнать, какие концепции и требования включает в себя эта лаборатория кода.
Примечание. Скрипт приложений основан на JavaScript. Если вы новичок в JavaScript, Codecademy предлагает бесплатный вводный курс JavaScript . Хотя Google не создавал этот курс, он должен охватывать все, что вам нужно знать.
Вы закончили с представлениями. Перейдите к следующему разделу, чтобы начать работу с макросами.
Как правило, при работе с электронными таблицами вы можете войти в цикл повторяющихся действий — копирование значений ячеек, форматирование, создание формул и т. д. — которые могут стать утомительными и привести к ошибкам. Для автоматизации повторяющихся действий в Google Таблицах предусмотрены макросы . Макросы позволяют «записывать» серию действий на листе. С записанным макросом вы можете повторить те же действия в другом месте электронной таблицы простым нажатием горячей клавиши.
В этом разделе вы узнаете, как создать макрос в Таблицах. В следующем разделе вы увидите, как создаются макросы с помощью Apps Script.
Прежде чем продолжить, вам нужна электронная таблица с некоторыми данными. Мы подготовили его для вас: щелкните эту ссылку, чтобы скопировать лист данных, а затем щелкните Сделать копию .
Копия примера электронной таблицы, которую вы можете использовать, помещена в папку на вашем Google Диске и названа «Копия 10 самых кассовых фильмов (2018 г.)».
Теперь, когда у вас есть электронная таблица для работы, вы можете записать макрос в Google Sheets. В этом примере вы создадите макрос, который форматирует строку заголовка для ваших данных. Просто выполните следующие действия:
Как только вы начинаете запись, Google Sheets запоминает каждое действие, которое вы совершаете в электронной таблице: выделение ячеек, добавление данных, переключение на другие листы, форматирование и т. д. Эти действия становятся «сценарием», который повторяется после сохранения и активации макроса позже.
Ключевые термины: макросы могут использовать два типа ссылок в Таблицах для ссылки на определенные ячейки. Абсолютные ссылки фиксируются на точных записанных местоположениях (например, ячейка A5), а относительные ссылки применяются с использованием текущего выбора пользователя в качестве отправной точки (например, четвертая ячейка ниже текущего выбора).
Эта лаборатория кода просит вас использовать относительные ссылки для ваших макросов.
Используя пользовательский интерфейс Таблиц, вы создали макрос, предназначенный для форматирования заголовков.
Вы можете применить новый макрос в Таблицах, следуя этим инструкциям:
Примечание. Если вы используете учетную запись gmail.com , вы также можете получить диалоговое окно непроверенного приложения при первом использовании сценария. Google использует это, чтобы предупредить пользователей, которые могут использовать код от неизвестных или ненадежных авторов. Если вы видите это диалоговое окно, вы можете продолжить, так как вы являетесь автором сценария. Следуйте инструкциям на экране, чтобы продолжить авторизацию скрипта. Процесс получения этого разрешения называется авторизацией .
Поздравляем, вы научились применять макросы в Таблицах. Ваша таблица должна выглядеть так:
Макросы позволяют эффективно создавать электронные таблицы, и в следующей части этой лаборатории кода вы узнаете, как сделать свои макросы еще более мощными. Вот в чем секрет: когда вы записываете макрос, на самом деле вы пишете код Apps Script . В фоновом режиме Sheets создает код, соответствующий макрокомандам. В следующем разделе вы узнаете, как изменить код напрямую с помощью встроенного в браузер редактора Apps Script.
Когда вы создаете макрос, Google Sheets сохраняет ваши действия как функцию скрипта приложений. Когда вы активируете макрос, Google Sheets вызывает функцию Apps Script, чтобы применить эти действия в том же порядке.
Теперь, когда вы создали макрос, вы можете посмотреть его код. Чтобы просмотреть сценарий макроса, нажмите « Расширения» > « Скрипт приложений », чтобы открыть редактор кода браузера для сценария приложений.
Редактор скриптов позволяет писать код в Apps Script и запускать эти скрипты на серверах Google.
Примечание. Макросы и сценарии, которые вы создаете в этой лаборатории кода, прикреплены к файлу Google Sheet, с которым они работают, и к ним можно получить доступ в любое время в меню « Расширения Таблиц» > « Скрипт приложений ». Скрипты, прикрепленные к листу Google подобным образом, называются контейнерными .
Просмотрите текущий сценарий. Sheets создал файл сценария macros.gs , когда вы записали макрос Header , заполнив его соответствующей функцией Apps Script с именем Header . Когда вы активируете макрос Header , Таблицы запускают эту функцию.
Посмотрите на изображение ниже, чтобы ознакомиться со структурой макроса в Apps Script. Если вы записали шаги в другом порядке или щелкали по электронной таблице во время записи, ваш код может выглядеть немного иначе, чем это.
Первая строка — аннотационный комментарий, влияющий на авторизацию:
/** @OnlyCurrentDoc */ Большинство сценариев перед запуском запрашивают у пользователя некоторые разрешения. Эти разрешения контролируют, что пользователь разрешает делать скрипту. Когда комментарий @OnlyCurrentDoc присутствует в проекте сценария, сценарий приложений запрашивает только разрешение на доступ и обновление текущей электронной таблицы. Без этого комментария Apps Script запросит разрешение на доступ и обновление всех электронных таблиц пользователя. Всегда рекомендуется включать эту аннотацию, когда вы работаете только с одним файлом. Средство записи макросов добавляет этот комментарий автоматически.
Чтобы понять, как Apps Script представляет инструкции вашего макроса, вы можете посмотреть на функцию:
function Header()< var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); sheet.getRange( spreadsheet.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate(); spreadsheet.getActiveRangeList().setBackground('#4c1130') .setFontColor('#ffffff') .setFontWeight('bold'); spreadsheet.getActiveSheet().setFrozenRows(1); >; Этот код запускается, когда вы активируете макрос Header . После function метка Header() определяет имя функции и ее параметры. Учтите, что Header() не требует параметров, поскольку макрофункциям в Apps Script не нужны входные данные. Фигурные скобки всегда заключают тело функции в Apps Script.
Более поздние лабораторные работы в этом плейлисте объясняют классы и концепции, связанные с созданием макроса. А пока вы можете просмотреть следующие описания кода, чтобы получить общее представление о его компонентах и их роли в построении вашего макроса. Рассмотрим первую строку:
var spreadsheet = SpreadsheetApp.getActive(); Здесь getActive() возвращает объект, представляющий текущий активный файл электронной таблицы в Sheets, и устанавливает его в новую переменную spreadsheet .
var sheet = spreadsheet.getActiveSheet(); sheet.getRange( spreadsheet.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate(); Эти строки соответствуют действию щелчка по первой строке, чтобы выделить ее. Это называется активацией . Первая строка сохраняет текущий лист в переменной sheet , а вторая строка получает всю первую строку с помощью getRange() , а затем вызывает activate() для ее активации. Первая строка указывается с помощью конкретных номеров строк и столбцов. spreadsheet.getCurrentCell().getRow() возвращает номер текущей строки, а sheet.getMaxColumns() возвращает максимальное количество столбцов на листе.
spreadsheet.getActiveRangeList().setBackground('#4c1130') .setFontColor('#ffffff') .setFontWeight('bold'); Этот фрагмент кода становится более сложным. Чтобы эффективно вызывать методы с помощью spreadsheet , код объединяет три метода в getActiveRangeList() , чтобы предотвратить избыточный вызов кода этого метода spreadsheet более одного раза. По мере написания кода с помощью Apps Script вы лучше познакомитесь с этим соглашением о вызове нескольких методов в одном классе (также известном как цепочка методов ). А пока вы можете прочитать следующие краткие пояснения по каждому методу в блоке кода:
Наконец, последняя строка замораживает первую строку макроса:
spreadsheet.getActiveSheet().setFrozenRows(1); И это сценарий, который вы создали, когда записывали свой макрос. Не беспокойтесь о любых незнакомых терминах или методах, упомянутых выше. Описание предназначено для того, чтобы вы подумали о некоторых идеях, на которых Apps Script фокусируется в типичной макро-функции, а также о темах, которые будут изучать будущие лаборатории кода.
В следующем разделе основное внимание уделяется управлению кодом функции Header() , чтобы показать, как вы можете использовать редактор скриптов для дальнейшей персонализации макросов.
Редактор сценариев приложений показывает макрос, который вы ранее создали в Google Таблицах. Изменяя содержимое тела функции, вы можете дополнительно настроить инструкции макроса для выполнения других или дополнительных действий. В следующих упражнениях демонстрируются различные способы работы с макросами с помощью редактора сценариев.
Предположим, вы хотите изменить свой макрос, чтобы он воздействовал только на первые 10 столбцов первой строки, а не на всю строку. Вы можете удалить макрос и перезаписать его. Но с помощью редактора сценариев приложений вы можете вносить эти изменения напрямую. Вот один из способов сделать это:
/** @OnlyCurrentDoc */ function Header() < var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); sheet.getRange( spreadsheet.getCurrentCell().getRow(), 1, 1, 10).activate(); /* sheet.getMaxColumns() replaced with 10.*/ spreadsheet.getActiveRangeList().setBackground('#4c1130') .setFontColor('#ffffff') .setFontWeight('bold'); spreadsheet.getActiveSheet().setFrozenRows(1); >
На новом листе вы должны увидеть следующий результат:
Изменяя активный или целевой диапазон, ваш макрос теперь влияет только на часть первой строки. Многие методы Apps Script принимают диапазон или нотацию A1 в качестве параметра, чтобы указать, с какими ячейками нужно действовать.
Далее давайте узнаем о настройке цветов макроса.
Чтобы помочь вам разработать цветовую схему макросов или других элементов в Таблицах, Apps Script может изменить заливку диапазона или цвет текста. Выполните следующие инструкции, чтобы узнать, как можно настроить цвета макроса.
Эти инструкции сосредоточены на изменении цвета фона вашего макроса:
/** @OnlyCurrentDoc */ function Header() < var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); sheet.getRange( spreadsheet.getCurrentCell().getRow(), 1, 1, 10).activate(); spreadsheet.getActiveRangeList().setBackground('#afeeee') /* #4c1130 replaced with #afeeee.*/ .setFontColor('#ffffff') .setFontWeight('bold'); spreadsheet.getActiveSheet().setFrozenRows(1); >В Таблицах фоновая заливка первых 10 столбцов в первой строке перекрашивается в пользовательский бирюзовый цвет:
Переключив шестнадцатеричное обозначение цвета в параметрах setBackground(color) с #4c1130 (темно-пурпурный 3) на #afeeee (бледно-бирюзовый, параметр, недоступный в меню цветов по умолчанию в Sheets), вы измените атрибут цвета фона вашего макроса. цвет.
Теперь вы изменили цвет фона, установленный вашим макросом. Если вы хотите изменить цвет текста, измените второй код цвета.
/** @OnlyCurrentDoc */ function Header() < var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); sheet.getRange( spreadsheet.getCurrentCell().getRow(), 1, 1, 10).activate(); spreadsheet.getActiveRangeList().setBackground('#afeeee') .setFontColor('#191970')/* #ffffff replaced with #191970.*/ .setFontWeight('bold'); spreadsheet.getActiveSheet().setFrozenRows(1); >Вернитесь в Таблицы, чтобы увидеть, что цвет текста в строке заголовка теперь темно-синий.
Теперь вы видели, что макросы на самом деле являются действиями Таблиц, записанными как код скрипта приложений. В следующем разделе вы можете увидеть еще один способ, которым Apps Script может помочь вам в работе с Google Sheets: пользовательские функции .
Как и в большинстве приложений для работы с электронными таблицами, в Google Таблицах есть несколько встроенных функций формул, таких как =SUM() , которые позволяют выполнять быстрые вычисления с данными электронных таблиц. Пользовательские функции — это просто функции, которые вы указываете с помощью Apps Script. После того как вы определили пользовательскую функцию, вы можете использовать ее в любом месте электронной таблицы, как и встроенную функцию.
В этом разделе показано, как создать пользовательскую функцию в Apps Script, которая выполняет денежное преобразование.
Используя ту же электронную таблицу и проект сценария из раздела «Макросы», следуйте этим инструкциям, чтобы узнать, как создать новый сценарий (который вы в конечном итоге сможете использовать для создания своей первой пользовательской функции):
В редакторе появится новая вкладка с именем customFunctions.gs .
Теперь, когда вы создали сценарий специально для пользовательских функций, вы можете заполнить его кодом.
Предположим, вы хотите изменить данные для «10 самых кассовых фильмов 2018 года», чтобы показать мировые валовые значения не только в долларах США, но и в швейцарских франках. С помощью пользовательских функций вы можете сделать это легко. В следующем упражнении показано, как создать пользовательскую функцию для математического преобразования значений в долларах в значения во франках.
Прежде чем вы сможете написать свою первую пользовательскую функцию, измените набор данных, чтобы функция могла демонстрировать правильный вывод. Сделать это:
Теперь у вас есть столбец, в котором могут храниться результаты вашей пользовательской функции преобразования. Затем вы можете использовать редактор сценариев для создания своей первой пользовательской функции.
/** * Converts US dollars to Swiss francs. * * @param dollars The total number of dollars. * @return swissFrancs The converted total of Swiss francs. * @customfunction */ function USDTOCHF(dollars)
Это код, который конвертирует доллары США в швейцарские франки. Попробуйте выполнить приведенные ниже инструкции и узнайте, как запустить пользовательскую функцию в листах.
Чтобы применить формулу к остальным ячейкам в столбце:
В столбце I теперь перечислены значения в долларах США, переведенные в швейцарские франки в столбце H.
Поздравляем, вы создали свою первую пользовательскую функцию. В следующем разделе объясняется код, содержащий USDTOCHF() .
Начальные комментарии детализируют назначение кода:
/** * Converts US dollars to Swiss francs. * * @param dollars The total number of dollars. * @return swissFrancs The provided value in Swiss francs. * @customfunction */ Подобные блоки комментариев часто используются в программировании для объяснения того, что делают функции.
В этом комментарии можно выделить две части: описание функции (для конвертации долларов во франки) и аннотации, описывающие параметры функции и возвращаемый тип.
С аннотациями Apps Script использует JSDoc , чтобы помочь вам документировать и создавать подсказки автозаполнения для вашего кода. Ниже вы можете прочитать, как каждая аннотация, используемая в USDTOCHF() , помогает вам в разработке скриптов приложений:
Обратите внимание, что текст, который появляется во всплывающем окне автозаполнения, точно соответствует тексту описания, который вы разместили в блоке комментариев. Вы можете упростить использование своих пользовательских функций, убедившись, что создаваемые вами описания хорошо написаны и полны.
Далее сосредоточьтесь на коде функции USDTOCHF() :
function USDTOCHF(dollars)
Как упоминалось ранее, USDTOCHF() принимает числовую переменную доллары, умножает ее на фиксированный обменный курс и возвращает значение, преобразованное в швейцарские франки, в числовой переменной swissFrancs . Входной параметр — это значение, содержащееся в ячейке, указанной при добавлении пользовательской функции в ячейку. В этом примере входные суммы в долларах поступают из столбца H. Выходное значение swissFrancs помещается в ячейку функции (столбец I в этом примере).
Пользовательские функции могут работать с числовыми или строковыми значениями, как вы увидите в следующем разделе.
Предположим, вы хотите, чтобы числовой вывод функции USDTOCHF() включал префикс CHF в швейцарских франках. Вы можете сделать это с помощью Apps Script, используя оператор конкатенации ( + ) , как показано в следующих инструкциях:
Оператор + добавляет строку CHF в начало значения, содержащегося в swissFrancs . Теперь ваш код должен выглядеть так:
/** * Converts US dollars to Swiss francs. * * @param dollars The total number of dollars. * @return swissFrancs The provided value in Swiss francs. * @customfunction */ function USDTOCHF(dollars)
Строка швейцарских франков теперь предшествует значениям столбца I:
Ваша пользовательская функция теперь не только конвертирует доллары США в швейцарские франки, но и выводит валюту со строковым префиксом.
Это хорошее начало для базовой пользовательской функции, но в этом примере предполагается, что обменный курс доллара к швейцарскому франку является постоянным. Предположим, вместо этого вы хотите использовать текущий обменный курс, поэтому всякий раз, когда таблица перезагружается, значения пересчитываются для представления текущей конверсии? Для этого вам понадобится средство для определения текущего обменного курса. Эта информация недоступна в Google Sheets, но, к счастью, вы можете использовать Apps Script, чтобы получить ее.
Вы можете использовать код, как показано ниже, чтобы получить текущий курс конвертации швейцарских франков в доллары США:
function USDTOCHF(dollars) < // Gets a cache that is common to all users of the script. var cache = CacheService.getScriptCache(); // Accesses the memory location (rates.CHF) of the script cache. var rate = cache.get('rates.CHF'); // If a cache miss occurs, the program fetches the current // CHF rate from an API and stores the rate in the cache // for later convenience. if (!rate) < var response = UrlFetchApp.fetch('https://api.exchangeratesapi.io/latest?base=USD'); var result = JSON.parse(response.getContentText()); rate = result.rates.CHF; cache.put('rates.CHF', rate); >// Converts dollars to CHF according to the latest rate. var swissFrancs = dollars * rate; // Returns the CHF value. return 'CHF' + swissFrancs; > Этот код получает текущий обменный курс с сервера финансовой информации с помощью стороннего API обменного курса . Это делается с помощью сервисов Apps Script, таких как UrlFetchApp и CacheService . Эти расширенные концепции выходят за рамки этой конкретной кодовой лаборатории, но вы можете увидеть универсальность Apps Script для автоматизации сложных задач в Google Таблицах.
Поздравляем с завершением упражнений для пользовательских функций. При использовании пользовательских функций в своих проектах важно понимать, что они имеют определенные ограничения. В следующем списке перечислены ограничения, подробно описанные в руководстве « Пользовательские функции в Google Таблицах »:
Теперь вы можете улучшить свои электронные таблицы, используя редактор скриптов для работы с макросами и создания пользовательских функций. В следующем разделе вы можете просмотреть, что вы узнали, и что вы можете сделать дальше, чтобы улучшить свои навыки написания сценариев.
Вы завершили первую лабораторную работу по основам скриптов приложений с Google Sheets. Создавая и редактируя макросы и пользовательские функции Таблиц, вы изучили основные концепции сценариев приложений. Вы можете расширить свои знания о сценариях приложений в следующей лаборатории кода.
Основные условия
Узнайте больше о Apps Script в документации по Apps Script .
В следующей лаборатории кода в этом плейлисте представлены основные классы и терминология службы электронных таблиц сценариев приложений. Этот сервис позволяет точно контролировать значения и представление данных в Google Sheets с помощью Apps Script.
Если не указано иное, контент на этой странице предоставляется по лицензии Creative Commons "С указанием авторства 4.0", а примеры кода – по лицензии Apache 2.0. Подробнее об этом написано в правилах сайта. Java – это зарегистрированный товарный знак корпорации Oracle и ее аффилированных лиц.
