Написание первого скрипта: пользовательские функции

Создание нового файла сценария. Перевод долларов США в российские рубли. Анализ функции USDTORUB(). Объединение строкового суффикса. Дополнительно: получение внешних данных. Рекомендации по пользовательским функциям

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

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

Создание нового файла сценария

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

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

В редакторе появится новая вкладка с именем “Пользовательские функции.gs”.

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

Перевод долларов США в российские рубли

Предположим, вы хотите скорректировать данные для “Топ-10 самых кассовых фильмов (2020)”, чтобы они отображали не только значения в долларах, но также и в рублях. С пользовательскими функциями это легко сделать. В следующем упражнении показано, как создать пользовательскую функцию для математического преобразования ваших долларовых значений в значения в рублях.

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

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

Таблицы Гугл вставить столбец справа

Добавьте текст “Мировой прокат, рубли” в ячейку J1.

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

В Пользовательские функции.gs замените код пустой функции myFunction() на следующий:

/**
 * Конвертирует доллары в рубли.
 *
 * @param {number} dollars Значение суммы в долларах.
 * @return {number} rubles Конвертированное значение в рублях.
 * @customfunction
 */
function USDTORUB(dollars){
  var rubles = dollars * 75.0;
  return rubles;
}

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

Чтобы сохранить сценарий, в верхней части редактора щелкните “Сохранить проект” .
На листе с данными выберите ячейку J2.
В поле функций введите =USDTORUB(I2)

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

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

Таблицы Гугл протягивание диапазона

В столбце J теперь указана конвертированная стоимость в рублях!

Таблицы Гугл результат выполнения пользовательской функции

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

Анализ функции USDTORUB()

В начале кода вы можете распознать комментарии, подробно описывающие назначение этой части программы:

/**
 * Конвертирует доллары в рубли.
 *
 * @param {number} dollars Значение суммы в долларах.
 * @return {number} rubles Конвертированное значение в рублях.
 * @customfunction
 */

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

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

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

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

Таблицы Гугл автозаполнение аннотации к пользовательским функциям

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

Далее сфокусируемся на коде функции USDTORUB():

function USDTORUB(dollars){
  var rubles = dollars * 75.0; 
  return rubles;
}

Как упоминалось ранее, USDTORUB() принимает числовую переменную в долларах и возвращает это значение, преобразованное в рубли в числовой переменной rubles, умножая его на фиксированный обменный курс. Входной параметр - это значение, содержащееся в ячейке, которую вы указали при добавлении пользовательской функции в ячейку. В этом примере входные суммы в долларах поступают из столбца I. Выходное значение rubles помещается в ячейку функции (то есть в столбец J в этом примере).

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

Объединение строкового суффикса

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

В редакторе измените return rubles; в строке 10 на return rubles \+ ' руб.';.

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

Оператор + добавляет строку " руб." в конец значения, содержащегося в rubles. Теперь ваш код должен выглядеть так:

/**
 * Конвертирует доллары в рубли.
 *
 * @param {number} dollars Значение суммы в долларах.
 * @return {number} rubles Конвертирует переданное значение в рубли.
 * @customfunction
 */
function USDTORUB(dollars) {
  var rubles = dollars * 75.0;
  return rubles + ' руб.';
}

Чтобы сохранить сценарий, в верхней части редактора щелкните “Сохранить проект” .

Значения в рублях теперь отображаются в колонке J:

Таблицы Гугл обновленная пользовательская функция

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

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

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

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

/**
 * Конвертирует доллары в рубли.
 *
 * @param {number} dollars Значение суммы в долларах.
 * @return {number} rubles Конвертирует переданное значение в рубли.
 * @customfunction
 */
function USDTORUB(dollars) {
  // Получает кэш, общий для всех пользователей скрипта.
  var cache = CacheService.getScriptCache();

  // Доступ к ячейке памяти (rate.RUB) кеша скриптов.
  var rate = cache.get("rates.RUB");

  // Если кэш отсутствует,
  // программа получает текущее значение RUB из API
  // и сохраняет его в кеше для дальнейшего использования.
  if (!rate) {
    var response = UrlFetchApp.fetch(
      "https://api.exchangeratesapi.io/latest?base=USD"
    );
    var result = JSON.parse(response.getContentText());
    rate = result.rates.RUB;
    cache.put("rates.RUB", rate);
  }
  // Конвертирует доллары в рубли по последнему курсу.
  var rubles = dollars * rate;
  // Возвращает значение в рублях с суффиксом.
  return rubles + " руб.";
}

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