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

1. Добро пожаловать в скрипт приложений

Что такое скрипт приложений?

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

Функции скриптов приложений включают следующее:

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

Этот плейлист «Основы скрипта приложений с кодовой лабораторией Google Sheets» учит основам скрипта приложений и тому, как его использовать для улучшения работы с Google Sheets. Эта лаборатория кода фокусируется на обучении основам скриптов приложений.

Сервис электронных таблиц

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

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

Что вы узнаете

Этот плейлист охватывает все темы, которые вам понадобятся, чтобы начать использовать Apps Script с Google Sheets:

1. Макросы и пользовательские функции
2. Электронные таблицы, листы и диапазоны
3. Работа с данными
4. Форматирование данных
5. Диаграмма и представление данных в слайдах

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

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

2. Введение

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

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

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

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

Что вы узнаете

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

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

• Базовое знакомство с JavaScript
• Базовое знакомство с Google Sheets
• Умение читать листы формата А1.

Примечание. Скрипт приложений основан на JavaScript. Если вы новичок в JavaScript, Codecademy предлагает бесплатный вводный курс JavaScript . Хотя Google не создавал этот курс, он должен охватывать все, что вам нужно знать.

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

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

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

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

Прежде чем вы начнете

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание. Макросы и сценарии, которые вы создаете в этой лаборатории кода, прикреплены к файлу Google Sheet, с которым они работают, и к ним можно получить доступ в любое время в меню « Расширения Таблиц» > « Скрипт приложений ». Скрипты, прикрепленные к листу Google подобным образом, называются контейнерными .

Анализ macro.gs

Просмотрите текущий сценарий. 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 вы лучше познакомитесь с этим соглашением о вызове нескольких методов в одном классе (также известном как цепочка методов ). А пока вы можете прочитать следующие краткие пояснения по каждому методу в блоке кода:

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

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

spreadsheet.getActiveSheet().setFrozenRows(1);

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

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

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

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

Изменить пораженные клетки

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

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

2. Чтобы сохранить скрипт, нажмите Сохранить .
3. Чтобы переименовать проект, введите «Макросы и пользовательские функции» в качестве имени нового проекта и нажмите « Переименовать ».
4. Чтобы создать лист, в Sheets нажмите Add Sheet .

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

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

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

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

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

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

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

1. В Таблицах вернитесь к листу, содержащему исходные данные (Лист 1).
2. Щелкните первую строку, чтобы выделить ее.
3. В редакторе скриптов замените цвет фона #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);
}

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

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

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

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

1. В Таблицах щелкните первую строку, чтобы убедиться, что она по-прежнему выделена.
2. В редакторе скриптов замените цвет шрифта #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);
}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4. В 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;
}

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

5. Чтобы сохранить скрипт, нажмите Сохранить .
6. В Листах выберите ячейку I2 .
7. В строке функций введите =USDTOCHF(H2) .

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

8. Переместите курсор в правый нижний угол ячейки I2 и выберите маленькую синюю рамку (курсор должен превратиться в при указывании на синее поле).
9. Перетащите синее поле вниз, чтобы выделить диапазон 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() , помогает вам в разработке скриптов приложений:

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

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

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

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

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

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

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

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

1. В редакторе сценариев обновите аннотацию @return , чтобы она возвращала строку вместо числа.
2. Измените 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;
}

3. Чтобы сохранить скрипт, нажмите Сохранить .

Строка швейцарских франков теперь предшествует значениям столбца 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 Таблицах »:

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

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

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

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

Вы нашли эту кодовую лабораторию полезной?

Да Нет

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

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

Основные условия

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

Узнайте больше о Apps Script в документации по Apps Script .

Что дальше

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

Найдите следующую лабораторию кода в электронных таблицах, листах и ​​диапазонах .