Эта страница переведена с помощью Cloud Translation API.

Основы скриптов приложений с Google Таблицами №1: макросы и пользовательские функции

1. Добро пожаловать в AppsScript

Что такое Apps Script?

Apps Script — это платформа для быстрой разработки приложений, которая позволяет автоматизировать, настраивать и расширять возможности Google Workspace . Apps Script поможет вам сэкономить время и силы, оптимизируя трудоёмкую и сложную работу в Google Workspace.

Возможности Apps Script включают в себя следующее:

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

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

Служба электронных таблиц

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

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

Чему вы научитесь

В этом плейлисте рассматриваются все темы, которые вам понадобятся для начала работы с Apps Script с Google Таблицами:

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

Перейдите к следующему разделу, чтобы узнать больше о содержании этой лабораторной работы.

2. Введение

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

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

В редакторе кода Apps Script вы также можете создавать пользовательские функции . Подобно встроенным функциям Sheets (таким как SUM или AVERAGE ), вы можете использовать Apps Script для написания собственных функций для простых и узкоспециализированных операций (например, преобразования или конкатенации строк). После создания этих функций вы можете вызывать их в Sheets так же, как и встроенные функции. Пользовательские функции также можно использовать в формулах ячеек, комбинируя их с другими функциями по мере необходимости.

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

Чему вы научитесь

Как создать скрипт для Google Таблиц.
Как пользоваться редактором Apps Script.
Как создавать и обновлять макросы.
Как создать свою первую пользовательскую функцию в Таблицах.

Что вам понадобится

Базовые знания JavaScript
Базовые знания Google Таблиц
Умение читать нотацию листов А1

Введение закончено. Переходите к следующему разделу, чтобы начать работу с макросами.

3. Создайте макрос в Таблицах

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

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

Прежде чем начать

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

Копия примера таблицы для вашего использования помещена в папку Google Диска и называется «Копия 10 самых кассовых фильмов (2018)».

Создать макрос

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

Щёлкните по ячейке A1, чтобы установить курсор в строке. Это строка заголовка.
В меню выберите Расширения > Макросы > Запись макросов .

После начала записи Google Таблицы запоминают все ваши действия в таблице: выделение ячеек, добавление данных, переключение на другие листы, форматирование и т. д. Эти действия становятся «скриптом», который повторяется после сохранения и активации макроса.

В диалоговом окне Макрос выберите Относительная ссылка.

Ключевые термины: Макросы могут использовать два типа ссылок в Таблицах для ссылки на конкретные ячейки. Абсолютные ссылки привязаны к точному местоположению (например, ячейка A5), а относительные ссылки применяются с использованием текущего выделения пользователя в качестве отправной точки (например, четвёртой ячейки снизу от текущего выделения).

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

Выберите строку 1 .

Измените цвет заливки верхнего ряда с белого на темно-пурпурный 3 .

Измените цвет текста верхней строки с черного на белый .

Чтобы выделить текст жирным шрифтом, нажмите Ctrl+B (или Cmd+B в macOS).
Чтобы закрепить верхнюю строку, выберите Вид > Закрепить > 1 строка .

Нажмите кнопку «Сохранить» в диалоговом окне макроса. В новом диалоговом окне вам будет предложено указать имя макроса. Введите имя «Заголовок» и нажмите кнопку «Сохранить ».

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

Активируйте свой макрос

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

Чтобы создать лист, нажмите «Добавить лист». .

На новом листе добавьте текст в ячейки A1:C2 . Примеры ввода можно посмотреть ниже:

Выделите первую строку.

Чтобы применить макрос к выбранной области, нажмите Расширения > Макросы > Заголовок .
Авторизуйте макрос, следуя инструкциям на экране.

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

Повторите шаг 4 , чтобы снова запустить макрос (разрешив ему остановить первое выполнение).

Поздравляем! Вы научились применять макросы в Таблицах. Ваша таблица должна выглядеть так:

Макросы позволяют эффективно создавать электронные таблицы, и в следующей части этой практической работы вы узнаете, как сделать свои макросы ещё более эффективными. Секрет в следующем: при записи макроса вы фактически пишете код Apps Script . За кулисами Таблицы создают код, соответствующий действиям макроса. В следующем разделе вы узнаете, как изменять код напрямую с помощью встроенного в браузер редактора Apps Script.

4. Макросы в редакторе скриптов

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

Редактор сценария

Теперь, когда вы создали макрос, вы можете посмотреть его код. Чтобы просмотреть скрипт макроса, нажмите «Расширения» > «Скрипт приложений» , чтобы открыть редактор кода для скрипта приложений в браузере.

Редактор скриптов позволяет писать код на языке Apps Script и запускать эти скрипты на серверах Google.

Анализ `macros.gs`

Проверьте текущий скрипт. Таблицы создали файл скрипта macros.gs при записи макроса Header , заполнив его соответствующей функцией Apps Script с именем Header . При активации макроса Header Таблицы запускают эту функцию.

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

Первая строка — аннотационный комментарий, влияющий на авторизацию:

/** @OnlyCurrentDoc */

Большинство скриптов запрашивают у пользователя определённые разрешения перед запуском. Эти разрешения определяют, какие действия пользователь разрешает скрипту. Если в проекте скрипта присутствует комментарий @OnlyCurrentDoc , Apps Script запрашивает только разрешение на доступ и обновление текущей таблицы. Без этого комментария 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() возвращает объект, представляющий текущий активный файл электронной таблицы в Таблицах, и устанавливает его в качестве новой переменной 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 вы лучше познакомитесь с этим соглашением о вызове нескольких методов в одном классе (также известным как цепочка методов ). А пока вы можете прочитать следующие краткие пояснения к каждому методу в блоке кода:

getActiveRangeList() возвращает текущий активный RangeList в spreadsheet . В данном случае это просто первая строка, активированная предыдущей строкой.
Методы setBackground(color) и setFontColor(color) изменяют атрибуты цвета ячеек в активном диапазоне.
setFontWeight(fontWeight) регулирует насыщенность шрифта для ячеек в активном диапазоне.

Наконец, последняя строка фиксирует первую строку макроса:

spreadsheet.getActiveSheet().setFrozenRows(1);

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

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

Настройте макросы с помощью Apps Script

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

Измените затронутые клетки

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

В редакторе скриптов замените sheet.getMaxColumns() на 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);
}

Чтобы сохранить ваш сценарий, нажмите «Сохранить». .
Чтобы переименовать проект, введите «Макросы и пользовательские функции» в качестве нового имени проекта и нажмите «Переименовать» .
Чтобы создать лист, в Таблицах нажмите «Добавить лист». .

В редакторе скриптов в списке функций выберите Header и нажмите Выполнить .

На новом листе вы должны увидеть следующий результат:

Изменяя активный или целевой диапазон, ваш макрос теперь влияет только на часть первой строки. Многие методы Apps Script принимают диапазон или нотацию A1 в качестве параметра, чтобы указать, с какими ячейками следует выполнять действие.

Далее давайте узнаем, как настраивать цвета макроса.

Измените цвета вашего макроса

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

Эти инструкции направлены на изменение цвета фона вашего макроса:

В Таблицах вернитесь на лист, содержащий исходные данные (Лист 1).
Щелкните первую строку, чтобы выделить ее.
В редакторе скриптов замените цвет фона #4c1130 на #afeeee . Эти значения представляют собой различные цвета в шестнадцатеричной системе счисления .

/** @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);
}

Чтобы сохранить ваш сценарий, нажмите «Сохранить». .
В списке функций выберите Header и нажмите Выполнить .

В Таблицах фоновая заливка первых 10 столбцов в первой строке изменит цвет на пользовательский бирюзовый:

Изменяя шестнадцатеричную нотацию цвета в параметрах setBackground(color) с #4c1130 (темно-пурпурный 3) на #afeeee (бледно-бирюзовый, опция, недоступная в меню цветов по умолчанию Таблиц), вы меняете цветовой атрибут фонового цвета вашего макроса.

Вы изменили цвет фона, заданный макросом. Если вы хотите изменить и цвет текста, измените второй цветовой код.

В Таблицах щелкните первую строку, чтобы убедиться, что она все еще выделена.
В редакторе скриптов замените цвет шрифта #ffffff на #191970 . Это приведёт к тому, что макрос установит тёмно-синий цвет шрифта.

/** @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);
}

Чтобы сохранить ваш сценарий, нажмите «Сохранить». .
В списке функций выберите Header и нажмите Выполнить .

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

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

5. Напишите свой первый скрипт: пользовательские функции

Как и большинство приложений для работы с электронными таблицами, Google Таблицы имеют несколько встроенных функций для работы с формулами, таких как =SUM() , которые позволяют быстро выполнять вычисления с данными в таблицах. Пользовательские функции — это функции, которые вы указываете с помощью Apps Script. После определения пользовательской функции вы можете использовать её в любом месте таблицы, как и встроенную функцию.

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

Создать файл сценария

Используя ту же электронную таблицу и проект скрипта из раздела «Макросы», следуйте этим инструкциям, чтобы узнать, как создать новый скрипт (который вы в конечном итоге сможете использовать для создания своей первой пользовательской функции):

Чтобы создать файл скрипта приложения, вернитесь в редактор скриптов.
Рядом с пунктом «Файлы» нажмите «Добавить файл». > Сценарий .
Назовите новый файл скрипта customFunctions и нажмите Enter . (Apps Script автоматически добавит расширение .gs к имени файла скрипта.)

В редакторе появится новая вкладка с именем customFunctions.gs .

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

Конвертировать доллары США в швейцарские франки

Предположим, вы хотите изменить данные для «Топ-10 самых кассовых фильмов 2018 года», чтобы отобразить мировые сборы не только в долларах США, но и в швейцарских франках. С помощью пользовательских функций это легко сделать. В следующем упражнении показано, как создать пользовательскую функцию для математического перевода долларовых сумм во франки.

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

В Таблицах щелкните правой кнопкой мыши столбец H.
В появившемся меню нажмите Вставить 1 справа .

menu showing Insert 1 right

Назовите столбец «Worldwide_Gross (швейцарские франки)» в ячейке I1 .

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

В customFunctions.gs замените код myFunction() следующим кодом:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The converted total of Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

Это код, который конвертирует доллары США в швейцарские франки. Попробуйте выполнить инструкции ниже и узнайте, как запустить пользовательскую функцию в таблицах.

Чтобы сохранить ваш сценарий, нажмите «Сохранить». .
В Таблицах выберите ячейку I2 .
В строке функций введите =USDTOCHF(H2) .

Чтобы применить формулу к остальным ячейкам столбца:

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

В столбце I теперь указаны конвертации в швейцарские франки значений в долларах США из столбца H.

Поздравляем, вы создали свою первую пользовательскую функцию. В следующем разделе объясняется код, из которого состоит USDTOCHF() .

Анализ `USDTOCHF()`

В начальных комментариях подробно описывается цель кода:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */

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

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

Благодаря аннотациям Apps Script использует JSDoc для документирования и создания подсказок автодополнения для вашего кода. Ниже вы можете узнать, как каждая аннотация, используемая в USDTOCHF() помогает вам в разработке Apps Script:

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

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

Далее сосредоточимся на коде в функции USDTOCHF() :

function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

Как упоминалось ранее, USDTOCHF() принимает числовую переменную dollars, умножает её на фиксированный обменный курс и возвращает значение, преобразованное в швейцарские франки, в числовой переменной swissFrancs . Входным параметром является значение, содержащееся в ячейке, указанной при добавлении пользовательской функции к ячейке. В этом примере входные суммы в долларах берутся из столбца H. Выходное значение swissFrancs помещается в ячейку функции (столбец I в данном примере).

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

Объединить префикс строки

Предположим, вы хотите, чтобы числовой вывод функции USDTOCHF() включал префикс швейцарского франка CHF . Это можно сделать с помощью Apps Script, используя оператор конкатенации ( + ) , как показано в следующих инструкциях:

В редакторе скриптов обновите аннотацию @return , чтобы она возвращала строку вместо числа.
Измените return swissFrancs на return 'CHF' + swissFrancs .

Оператор + добавляет строку CHF к началу значения, содержащегося в swissFrancs . Ваш код должен выглядеть следующим образом:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {string} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99;
  return 'CHF' + swissFrancs;
}

Чтобы сохранить ваш сценарий, нажмите «Сохранить». .

Строка швейцарского франка теперь предшествует значениям столбца I:

Ваша пользовательская функция теперь не только конвертирует доллары США в швейцарские франки, но и выводит валюту со строковым префиксом.

Дополнительно: Извлечение внешних данных

Это хорошее начало для простой пользовательской функции, но в этом примере предполагается, что обменный курс долларов к швейцарским франкам постоянен. Предположим, вы хотите использовать текущий обменный курс, чтобы при каждой перезагрузке таблицы значения пересчитывались в соответствии с текущим обменным курсом? Для этого вам понадобится способ узнать текущий обменный курс. Эта информация не всегда доступна в Google Таблицах, но, к счастью, её можно получить с помощью 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 Таблицах» :

Не создавайте пользовательские функции, требующие авторизации пользователя . Вместо этого создавайте пользовательские функции для выполнения более простых задач, таких как вычисление выборочных данных, редактирование текста и т. д. Перейдите к разделу «Использование служб Apps Script» .
Не называйте пользовательскую функцию так же, как и встроенную функцию, и не заканчивайте имя символом подчеркивания. Ознакомьтесь с рекомендациями по именованию .
Не передавайте переменные аргументы пользовательским функциям. В качестве аргументов пользовательским функциям можно передавать только детерминированные (фиксированные) значения. Передача переменных аргументов, таких как результат =RAND() , нарушит работу пользовательской функции. См. руководство по аргументам .
Не создавайте функции, выполнение которых занимает более 30 секунд. Если это займёт больше времени, возникнет ошибка, поэтому код функции должен быть простым и ограниченным по объёму. Вычисления, выполняемые в пользовательских функциях, желательно сделать максимально простыми. См. рекомендации по возвращаемым значениям .

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

6. Заключение

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

Оказался ли этот практический практикум полезным?

Да

Нет

Что мы рассмотрели

Базовые концепции сценариев приложений.
Как работать с редактором сценариев.
Как создавать и обновлять макросы Таблиц.
Как создавать пользовательские функции для Google Таблиц.

Ключевые термины

Абсолютная ссылка : настройка записи макроса, указывающая, что записанные действия макроса применяются к конкретным ячейкам, используемым в записи.
Активный (статус) : указывает, что указанная электронная таблица, лист, диапазон или ячейка — это та, которую пользователь электронной таблицы в данный момент просматривает или выделяет.
Apps Script : платформа быстрой разработки приложений на основе JavaScript, которая позволяет быстро и легко расширять приложения Google Workspace и автоматизировать работу.
Авторизация : процесс предоставления пользователем разрешений, позволяющих Apps Script получать доступ к данным пользователя в соответствующих службах Google или иным образом выполнять действия от имени пользователя.
Скрипт, привязанный к контейнеру : любой скрипт, привязанный к документу Google Workspace и созданный на его основе, например, Google Sheet или Google Doc.
Пользовательские функции : определяемые пользователем формулы в Apps Script, используемые в электронных таблицах для простых операций.
JSDoc : язык разметки, используемый для написания пояснительных заметок к исходным файлам на основе JavaScript.
Макрос : сокращение от «макросинструкция». Записанная последовательность действий в Google Таблицах, которую можно повторить с помощью пункта меню или сочетания клавиш.
Цепочка методов : программная идиома для вызова нескольких методов объекта, основанная на том факте, что каждый вызов метода возвращает объект.
Диапазон : группа из одной или нескольких смежных ячеек на листе.
Относительная ссылка : настройка записи макроса, указывающая, что записанные действия макроса применяются к ячейкам относительно текущего выбора ячеек.
Редактор скриптов : встроенный в браузер редактор кода для Apps Script.
Лист : отдельная страница электронной таблицы (аналогично рабочим книгам Microsoft Excel). Иногда используется для обозначения файла Google Таблиц в Google Диске.
SpreadsheetApp : этот класс служит родительским классом для службы электронных таблиц и предоставляет отправную точку для кода, который считывает или обрабатывает данные Google Таблиц.
Электронная таблица : файл Google Таблиц, который находится на Google Диске.
Служба электронных таблиц : служба скриптов приложений, которая позволяет скриптам создавать, получать доступ и изменять файлы Google Таблиц.

Дополнительную информацию о Apps Script можно найти в документации по Apps Script .

Что дальше?

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

Следующую лабораторную работу можно найти в разделе «Таблицы, таблицы и диапазоны» .

1. Добро пожаловать в AppsScript

Что такое Apps Script?

Служба электронных таблиц

Чему вы научитесь

2. Введение

Чему вы научитесь

Что вам понадобится

3. Создайте макрос в Таблицах

Прежде чем начать

Создать макрос

Активируйте свой макрос

4. Макросы в редакторе скриптов

Редактор сценария

Анализ macros.gs

Настройте макросы с помощью Apps Script

Измените затронутые клетки

Измените цвета вашего макроса

5. Напишите свой первый скрипт: пользовательские функции

Создать файл сценария

Конвертировать доллары США в швейцарские франки

Анализ USDTOCHF()

Объединить префикс строки

Дополнительно: Извлечение внешних данных

Руководства по пользовательским функциям

6. Заключение

Оказался ли этот практический практикум полезным?

Что мы рассмотрели

Что дальше?

Анализ `macros.gs`

Анализ `USDTOCHF()`