Уровень сложности: Начинающий

Последнее обновление: 2021-03-13

Добро пожаловать в четвертую часть сборника "Основы Apps Script в Google Таблицах"!

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

Что вы изучите

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

Это четвертая лаба из сборника "Основы Apps Script в Google Таблицах". Перед тем, как начать эту лабу, убедитесь, что вы все усвоили из лаб:

Что вам потребуется

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

  1. Щелкните эту ссылку, чтобы скопировать Таблицу, а затем нажмите кнопку "Создать копию". Новая Таблица будет помещена в вашу домашнюю папку Google Диска с именем "Копия Форматирование данных".
  2. Откройте Таблицу. Щелкните заголовок и измените его с "Копия Форматирование данных" на "Форматирование данных". Ваша Таблица, с некоторой базовой информацией о первых трех фильмах "Звездные войны" должна выглядеть так:
  3. Выберите Инструменты > Редактор скриптов, чтобы открыть редактор скриптов.
  4. Щелкните заголовок проекта и измените его с "Проект без названия" на "Форматирование данных".
  5. Нажмите "Переименовать".

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

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

Реализация

Создадим собственное меню!

  1. Замените весь код в своем проекте следующим:
/**
 * Специальная функция, которая запускается при открытии или перезагрузке Таблицы,
 * используется для добавления настраиваемого меню.
 */
function onOpen() {
  // Получить объект Ui
  var ui = SpreadsheetApp.getUi();

  // Создать и добавить имя меню и его пункты в основное меню
  ui.createMenu('Быстрое форматирование')
    .addItem('Форматировать строку заголовков', 'formatRowHeader')
    .addItem('Форматировать колонку названий', 'formatColumnHeader')
    .addItem('Форматировать набор данных', 'formatDataset')
    .addToUi();
}
  1. Сохраните проект.
  2. В редакторе скриптов в рабочей панели выберите onOpen в раскрывающемся списке функций и нажмите Выполнить. Это запустит onOpen(), чтобы перестроить меню Таблицы, чтобы вам не пришлось обновлять страницу браузера.

Обзор кода

Давайте рассмотрим этот код, чтобы понять, как он работает. В onOpen() первая строка использует метод getUi() для получения объекта Ui, представляющего пользовательский интерфейс активной Таблицы, к которой привязан текущий проект скриптов.

Следующие строки создают новое меню "Быстрое форматирование", добавляют элементы меню "Форматировать строку заголовков", "Форматировать колонку названий" и "Форматировать набор данных" в это меню, а затем добавляют меню в интерфейс Таблицы. Это делается с помощью методов createMenu(caption), addItem(caption, functionName) и addToUi() соответственно.

addItem(caption, functionName) создает связь между пунктом меню и функцией скрипта, которая запускается при выборе этого пункта. Например, выбор пункта меню "Форматировать строку заголовков" приводит к тому, что Таблица пытается запустить функцию formatRowHeader() (которая еще не существует).

Результат

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

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

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

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

Реализация

Чтобы реализовать операцию форматирования, вы будете использовать те же методы службы Spreadsheet Service, которые вы использовали раньше, добавим лишь некоторые методы форматирования этой службы. Сделайте следующие шаги:

  1. В редакторе скриптов добавьте в конец файла проекта следующую функцию:
/**
 * Форматирует верхнюю строку листа, используя определенный стиль.
 */
function formatRowHeader() {
  // Получает текущий активный лист и диапазон верхней строки.
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());

  // Применяет определенный формат к верхней строке:
  // жирный белый текст,
  // сине-зеленый фон
  // и сплошная черная рамка вокруг ячеек.
  headerRange
    .setFontWeight('bold')
    .setFontColor('#ffffff')
    .setBackground('#007272')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}
  1. Сохраните проект.

Обзор кода

Как и во многих других задачах форматирования, код скрипта, необходимый для его реализации, прост. В первых двух строках используются методы, которые вы видели ранее, чтобы получить ссылку на текущий активный лист (sheet) и верхнюю строку листа (headerRange). Метод Sheet.getRange(row, column, numRows, numColumns) указывает на верхнюю строку, включая только те столбцы, в которых есть данные. Метод Sheet.getLastColumn() просто возвращает номер последнего столбца, который содержит данные на листе. В нашем примере это столбец E ("Ссылка").

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

Последний метод имеет множество параметров, поэтому давайте рассмотрим, что делает каждый из них. Первые четыре параметра здесь (для всех установлено значение true) сообщают Apps Script, что граница должна быть добавлена выше, ниже, а также слева и справа от диапазона. Пятый и шестой параметры (null и null) указываются, чтобы избежать изменения любых линий границ, которые могут быть в пределах выбранного диапазона. Седьмой параметр (null) указывает, что цвет границы по умолчанию должен быть черным. Наконец, последний параметр указывает тип используемого стиля границы, взятый из параметров, предоставляемых SpreadsheetApp.BorderStyle.

Результат

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

  1. Если вы еще этого не сделали, сохраните проект в редакторе Apps Script.
  2. Щелкните пункт меню Таблицы Быстрое форматирование > Форматировать строку заголовков.

Результат должен выглядеть следующим образом:

Вы автоматизировали задачу форматирования! В следующем разделе применяется тот же метод для создания другого стиля для колонки названий.

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

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

Реализация

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

  1. В редакторе скриптов добавьте в конец проекта следующую функцию formatColumnHeader():
/**
 * Форматирует колонку названий активного листа.
 */
function formatColumnHeader() {
  var sheet = SpreadsheetApp.getActiveSheet();

  // Получает общее количество строк в диапазоне данных,
  // не включая строку заголовка.
  var numRows = sheet.getDataRange().getLastRow() - 1;

  // Получает диапазон с названиями. 
  var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);

  // Применяет форматирование текста и добавляет границы.
  columnHeaderRange
    .setFontWeight('bold')
    .setFontStyle('italic')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  // Вызывает вспомогательный метод для создания гиперссылки
  // в первой колонке, на основе данных колонки "Ссылка"
  hyperlinkColumnHeaders_(columnHeaderRange, numRows);
}
  1. Также добавьте следующие вспомогательные функции в конец проекта после функции formatColumnHeader():
/**
 * Вспомогательная функция, которая связывает колонку названий
 * с содержимым колонки "Ссылка".
 * Затем функция удаляет колонку "Ссылка".
 *
 * @param {object} headerRange Диапазон с названиями для обновления.
 * @param {number} numRows Размер диапазона с названиями.
 */
function hyperlinkColumnHeaders_(headerRange, numRows) {
  // Получает индексы колонки с названиями и с ссылками.
  var headerColIndex = 1;
  var urlColIndex = columnIndexOf_('Ссылка');

  // Выходит, если колонки с ссылками нет. 
  if (urlColIndex == -1)
    return;

  // Берет значения из колонки названий и из колонки ссылок.
  var urlRange =
    headerRange.offset(0, urlColIndex - headerColIndex);
  var headerValues = headerRange.getValues();
  var urlValues = urlRange.getValues();

  // Обновляет значения названий до значений с гиперссылками.
  for (var row = 0; row < numRows; row++) {
    headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
      + '","' + headerValues[row] + '")';
  }
  headerRange.setValues(headerValues);

  // Удаляет колонку ссылками,
  // чтобы не засорять лист лишними данными
  SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex);
}

/**
 * Вспомогательная функция, которая просматривает заголовки всех колонок
 * и возвращает индекс колонки по имени в строке 1.
 * Если колонка с таким именем не существует, эта функция возвращает -1.
 * Если несколько колонок имеют одинаковое имя в строке 1,
 * возвращается индекс первой обнаруженной колонки.
 *
 * @param {строка} colName Имя, которое нужно искать в заголовках колонок.
 * @return Индекс колонки на активном листе или -1, если имя не найдено.
 */
function columnIndexOf_(colName) {
  // Получет имена текущих колонок.
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnHeaders =
    sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var columnNames = columnHeaders.getValues();

  // Перебирает каждую колонку и возвращает индекс,
  // если значение первой строки этой колонки совпадает с colName.
  for (var col = 1; col <= columnNames[0].length; col++) {
    if (columnNames[0][col - 1] === colName)
      return col;
  }

  // Возвращает -1, если колонка с именем colName не существует. 
  return -1;
}
  1. Сохраните проект.

Обзор кода

Давайте рассмотрим код каждой из этих трех функций отдельно:

formatColumnHeader()

Как вы, наверное, ожидали, первые несколько строк этой функции устанавливают переменные, которые ссылаются на интересующий нас лист и диапазон:

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

Добавление гиперссылок в колонку заголовков является более сложной задачей, поэтому formatColumnHeader() вызывает hyperlinkColumnHeaders_(headerRange, numRows), чтобы решить эту задачу. Это помогает сохранять код аккуратным и читабельным.

hyperlinkColumnHeaders_(headerRange, numRows)

Эта функция сначала определяет номера колонки с названиями (предполагается, что это номер 1) и колонки "Ссылка". Он вызывает columnIndexOf_('Ссылка'), чтобы получить номер колонки со ссылками. Если колонка "Ссылка" не найдена, метод завершается без изменения каких-либо данных.

Функция получает новый диапазон (urlRange), который охватывает ссылки, соответствующие строкам в колонке названий. Это делается с помощью метода Range.offset(rowOffset, columnOffset), который гарантирует, что два диапазона будут одного размера. Затем извлекаются значения как названий, так и ссылок (headerValues и urlValues соответственно).

Затем функция перебирает каждое значение ячейки из колонки названий и заменяет его табличной формулой =HYPERLINK(), построенной на основе содержимого колонки с названиями и колонки с ссылками. Далее измененные значения заголовка возвращаются на лист с помощью Range.setValues(values).

Наконец, чтобы сохранить читаемость листа и удалить избыточную информацию, вызывается Sheet.deleteColumn(columnPosition) для удаления столбца "Ссылка".

columnIndexOf_(colName)

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

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

Результат

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

  1. Если вы еще этого не сделали, сохраните проект в редакторе Apps Script.
  2. Щелкните пункт меню Таблицы Быстрое форматирование > Форматировать колонку названий.

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

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

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

Реализация

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

  1. Добавьте функцию formatDataset() в редактор в конец файла вашего проекта:
/**
 * Форматирует данные листа, исключая строку и колонку заголовков.
 * Применяет границу и чередование, форматирует колонку release_date
 * и автоматически изменяет размер строк и колонок.
 */
function formatDataset() {
  // Получает активный лист и диапазон данных.
  var sheet = SpreadsheetApp.getActiveSheet();
  var fullDataRange = sheet.getDataRange();

  // Применяет чередование цветов строк к данным,
  // за исключением строки и колонки с заголовками.
  // Применяет чередование только в том случае,
  // если для диапазона еще не задано чередование.
  var noHeadersRange = fullDataRange.offset(
    1, 1,
    fullDataRange.getNumRows() - 1,
    fullDataRange.getNumColumns() - 1);

  if (!noHeadersRange.getBandings()[0]) {
    // У диапазона еще нет чередования цветов,
    // поэтому можно безопасно применить новое.
    noHeadersRange.applyRowBanding(
      SpreadsheetApp.BandingTheme.LIGHT_GREY,
      false, false);
  }

  // Вызов вспомогательной функции
  // для применения форматирования даты к столбцу с меткой release_date.
  formatDates_(columnIndexOf_('release_date'));

  // Устанавливает границу вокруг всех данных и 
  // изменяет размер колонок и строк по размеру.
  fullDataRange.setBorder(
    true, true, true, true, null, null,
    null,
    SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  sheet.autoResizeColumns(1, fullDataRange.getNumColumns());
  sheet.autoResizeRows(1, fullDataRange.getNumRows());
}
  1. Также добавьте следующую вспомогательную функцию в конце проекта после функции formatDataset():
/** 
 * Вспомогательный метод, который применяет формат даты
 * "день месяц год (имя дня недели)" к указанному столбцу на активном листе.
 *
 * @param {number} colIndex Номер колонки для форматирования
 *
 */
function formatDates_(colIndex) {
  // Выходит, если номер колонки равен -1, что указывает на то,
  // что форматируемая колонка отсутствует на листе.
  if (colIndex < 0)
    return;

  // Устанавливает форматирование даты для колонки даты,
  // исключая строку заголовка.
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
    .setNumberFormat("dd mmmm yyyy (dddd)");
}
  1. Сохраните проект.

Обзор кода

Давайте рассмотрим код каждой из этих двух функций отдельно:

formatDataset()

Эта функция следует шаблону, аналогичному ранее реализованным функциям форматирования. Во-первых, она получает переменные для хранения ссылок на активный лист (sheet) и диапазон данных (fullDataRange).

Во-вторых, она использует метод Range.offset(rowOffset, columnOffset, numRows, numColumns) для создания нового диапазона (noHeadersRange), который охватывает все данные на листе, за исключением заголовков колонок и строк. Затем код проверяет, есть ли в этом новом диапазоне какие-либо существующие форматы чередования (с помощью Range.getBandings()). Это необходимо, потому что Apps Script выдаст ошибку, если вы попытаетесь применить новый формат чередования там, где оно уже существует. Если этот типа форматирования еще не существует, функция добавляет четную светло-серую полосу, используя Range.applyRowBanding(bandingTheme, showHeader, showFooter). В противном случае функция перейдет к следующему шагу.

На следующем шаге вызывается функция formatDates_(colIndex) для форматирования дат в колонке с меткой "release_date" (эта функция описана ниже). Колонка указывается с помощью вспомогательной функции columnIndexOf_(colName), которую вы реализовали ранее.

Наконец, форматирование завершается добавлением еще одной границы (как и раньше) и автоматическим изменением размера каждой строки и колонки в соответствии с данными, которые они содержат, с помощью методов Sheet.autoResizeColumns(columnPosition) и Sheet.autoResizeColumns(columnPosition).

formatDates_(colIndex)

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

Сначала функция проверяет, действителен ли указанный индекс колонки (то есть 0 или больше). Если нет, то она завершается без каких-либо дополнительных действий. Эта проверка предотвращает ошибки, которые могут быть вызваны, например, тем, что на листе нет колонки release_date. После проверки индекса колонки функция получает диапазон, охватывающий эту колонку (за исключением строки его заголовка), и использует Range.setNumberFormat(numberFormat) для применения форматирования.

Результат

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

  1. Если вы еще этого не сделали, сохраните проект в редакторе Apps Script.
  2. Щелкните пункт меню Таблицы Быстрое форматирование > Форматировать набор данных.

Результат должен выглядеть следующим образом:

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

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

В предыдущей лабе "Работа с данными. Пользовательские меню" вы узнали, как извлекать данные из API. Здесь вы будете использовать те же методы. В этом упражнении вы будете использовать общедоступный API Звездных войн (SWAPI) для получения информации для заполнения вашей Таблицы. В частности, вы будете использовать API, чтобы получить много информации об основных персонажах, которые появляются в каждом фильме из оригинальной трилогии "Звездных войн".

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

Реализация

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

Выполните следующие действия:

  1. В редакторе скриптов обновите функцию onOpen() вашего проекта, чтобы она соответствовала следующему:
/**
 * Специальная функция, которая запускается при открытии или перезагрузке Таблицы,
 * используется для добавления настраиваемого меню.
 */
function onOpen() {
  // Получить объект Ui
  var ui = SpreadsheetApp.getUi();

  // Создать и добавить имя меню и его пункты в основное меню
  ui.createMenu('Быстрое форматирование')
    .addItem('Форматировать строку заголовков', 'formatRowHeader')
    .addItem('Форматировать колонку названий', 'formatColumnHeader')
    .addItem('Форматировать набор данных', 'formatDataset')
    .addSeparator()
    .addSubMenu(ui.createMenu('Создать лист персонажей')
      .addItem('Эпизод IV', 'createPeopleSheetIV')
      .addItem('Эпизод V', 'createPeopleSheetV')
      .addItem('Эпизод VI', 'createPeopleSheetVI'))
    .addToUi();
}
  1. Сохраните проект.
  2. В редакторе скриптов выберите onOpen в раскрывающемся списке функций и нажмите Выполнить. Это запустит onOpen(), чтобы перестроить меню Таблицы с новыми опциями, которые вы только что добавили.
  3. Чтобы создать новый файл Apps Script в текущем проекте, в левой части редактора рядом с разделом "Файлы" нажмите "Добавить файл" > "Скрипт". Назовите новый скрипт "API" и нажмите Ввод.
  4. Замените код в новом файле API.gs следующим:
/**
 * Функция локализации, для перевода ключей локализации
 * 
 * @param {string} lang Поддерживаемый язык
 * @param {string} key Параметр локализации, ключ
 * @returns {string} Перевод типа ресурса
 */
function localize_(lang, key) {
  var dictionary = {
    en: {
      "characters": "characters",
      "_missing": "Localization parameter not found"
    },
    ru: {
      "characters": "персонажи",
      "_missing": "Параметр локализации не найден"
    }
  }
  var locale = dictionary["en"];
  if (dictionary[lang])
    locale = dictionary[lang];
  if (!locale[key])
    return locale["_missing"];
  return locale[key];
}

/**
 * Функция-обертка, которая передает аргументы для создания листа
 * с описанием персонажей из Эпизода IV.
 */
function createPeopleSheetIV() {
  createResourceSheet_('characters', 1, "IV");
}

/**
 * Функция-обертка, которая передает аргументы для создания листа
 * с описанием персонажей из Эпизода V.
 */
function createPeopleSheetV() {
  createResourceSheet_('characters', 2, "V");
}

/**
 * Функция-обертка, которая передает аргументы для создания листа
 * с описанием персонажей из Эпизода VI.
 */
function createPeopleSheetVI() {
  createResourceSheet_('characters', 3, "VI");
}

/** 
 * Создает новый форматированный лист,
 * заполненный пользовательской информацией из API Звездных войн.
 * Если лист с этими данными уже существует,
 * он перезаписывается информацией API.
 *
 * @param {string} resourceType Тип ресурса. 
 * @param {number} idNumber Идентификационный номер фильма. 
 * @param {number} episodeNumber Номер эпизода "Звездных войн"
 *                               используется только как имя листа.
 */
function createResourceSheet_(
  resourceType, idNumber, episodeNumber) {

  // Получает базовую информацию о фильме из API. 
  var filmData = fetchApiResourceObject_(
    "https://swapi.dev/api/films/" + idNumber);

  // Извлекаем URL-адреса API для каждого ресурса,
  // чтобы код мог вызвать API для получения дополнительной информации
  // о каждом из них в отдельности.
  var resourceUrls = filmData[resourceType];

  // Извлекаем каждый ресурс из API по отдельности
  // и помещаем их в новый список объектов.
  var resourceDataList = [];
  for (var i = 0; i < resourceUrls.length; i++) {
    resourceDataList.push(
      fetchApiResourceObject_(resourceUrls[i])
    );
  }

  // Получает ключи, используемые для ссылки на каждую часть данных в ресурсах.
  // Предполагается, что ключи идентичны для каждого объекта,
  // поскольку все они относятся к одному и тому же типу ресурса.
  var resourceObjectKeys = Object.keys(resourceDataList[0]);

  // Создает новый лист с подходящим именем.
  // При создании он автоматически становится активным листом.
  var resourceSheet = createNewSheet_(
    "Эпизод " + episodeNumber + " " + localize_('ru', resourceType));

  // Добавляет данные API в новый лист,
  // используя каждый ключ объекта в качестве заголовка колонки. 
  fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);

  // Отформатирует новый лист, используя те же стили, что и пункты меню "Быстрое форматирование".
  // Все эти методы применяются только для активного листа.
  formatRowHeader();
  formatColumnHeader();
  formatDataset();
}
  1. Теперь вам нужно добавить вспомогательные функции. Добавьте следующий код в конец файла API.gs вашего проекта:
/** 
 * Вспомогательная функция, которая извлекает объект JSON,
 * содержащий ответ от общедоступного API.
 *
 * @param {string} url Ссылка API на объект, который должен быть получен. 
 * @return {object} resourceObject Объект JSON, полученный из API. 
 */
function fetchApiResourceObject_(url) {
  // Делает запрос к API и получает ответ.
  var response =
    UrlFetchApp.fetch(url, { 'muteHttpExceptions': true });

  // Разобрать и возвращает ответ в виде объекта JSON.
  var json = response.getContentText();
  var responseObject = JSON.parse(json);
  return responseObject;
}

/** 
 * Вспомогательная функция, которая создает новый лист
 * или возвращает существующий лист с тем же именем.
 *
 * @param {string} name Имя нового листа.
 * @return {object} Вновь созданный лист или существующий одноименный лист.
 *                  Этот лист становится активным.
 */
function createNewSheet_(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Возвращает существующий лист, если он имеет указанное имя.
  // Активирует лист перед возвратом.
  var sheet = ss.getSheetByName(name);
  if (sheet) {
    return sheet.activate();
  }

  // В противном случае создает новый лист, задает его имя и возвращает его.
  // Новые листы, созданные таким образом, автоматически становятся активными.
  sheet = ss.insertSheet(name);
  return sheet;
}

/** 
 * Вспомогательная функция, которая добавляет данные API в заданный лист.
 * Каждый ключ объекта используется в качестве заголовка колонки на этом листе.
 *
 * @param {object} resourceSheet Изменяемый лист. 
 * @param {object} objectKeys Список ключей к ресурсам.
 * @param {object} resourceDataList Список объектов ресурсов API,
 *                                  содержащих данные для добавления на лист.
 */
function fillSheetWithData_(
  resourceSheet, objectKeys, resourceDataList) {
  // Задает размеры диапазона данных, добавляемого на лист.
  var numRows = resourceDataList.length;
  var numColumns = objectKeys.length;

  // Получает диапазон ресурсов и связанного массива значений.
  // Добавляет дополнительную строку для заголовков колонок.
  var resourceRange =
    resourceSheet.getRange(1, 1, numRows + 1, numColumns);
  var resourceValues = resourceRange.getValues();

  // Перебирает каждое значение ключа и ресурс,
  // извлекая данные для размещения в двумерном массиве resourceValues.
  for (var column = 0; column < numColumns; column++) {

    // Установливает колонку заголовков. 
    var columnHeader = objectKeys[column];
    resourceValues[0][column] = columnHeader;

    // Читает и назначает каждую строку в этой колонке.  
    for (var row = 1; row < numRows + 1; row++) {
      var resource = resourceDataList[row - 1];
      var value = resource[columnHeader];
      resourceValues[row][column] = value;
    }
  }

  // Удаляет все существующие данные на листе и устанавливает новые значения.
  resourceSheet.clear()
  resourceRange.setValues(resourceValues);
}
  1. Сохраните ваш проект.

Обзор кода

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

onOpen()

Здесь вы только что добавили несколько новых пунктов в меню быстрого форматирования. Вы добавили разделитель, а затем использовали метод Menu.addSubMenu(menu) для создания вложенной структуры меню с тремя новыми элементами. Новые элементы добавляются с помощью метода Menu.addItem(caption, functionName).

Функция локализации localize_()

В этом месте кода мы определяем функцию, которая поможет нам отображать информацию в заданной локализации. Для примера приведено два языка и только три ключа для перевода - characters, planets, starships - имена ресурсов API. В функции определен словарь для перевода и назначается локаль по умолчанию - "en". Если другая локаль, переданная как параметр, и ключ локализации, привязанный к ней, есть в словаре, то возвращается перевод, иначе, вместо ошибки, функция передает сообщение под ключом "_missing".

Функции-обертки

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

Было бы удобно просто написать одну функцию для создания листа и заставить эту функцию принимать параметр, определяющий, какой фильм использовать. Однако метод Menu.addItem(caption, functionName) не позволяет передавать параметры для функции, вызываемой из меню. Итак, как избежать написания одного и того же кода трижды?

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

Здесь код использует три функции-обертки: createPeopleSheetIV(), createPeopleSheetV() и createPeopleSheetVI(). Пункты меню связаны с этими функциями. При щелчке по пункту меню функция-обертка выполняет и немедленно вызывает основную функцию компоновщика листов createResourceSheet_(resourceType, idNumber, EpisodeNumber), передавая параметры, соответствующие этому пункту меню. В данном случае это означает, что вы должны попросить функцию построения листов создать лист, заполненный данными основных персонажей из одного из трех фильмов "Звездные войны".

Больше о функциях-обертках тут.

createResourceSheet_(resourceType, idNumber, episodeNumber)

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

Во-первых, функция использует fetchApiResourceObject_(url), чтобы сделать запрос API для получения базовой информации о фильме. Ответ API включает в себя набор URL-адресов, которые код может использовать для получения дополнительных сведений о конкретных людях (называемых здесь ресурсами) из фильмов. Код собирает все это в массиве resourceUrls.

Затем код многократно использует fetchApiResourceObject_(url) для вызова API для каждого URL-адреса ресурса в resourceUrls. Результаты сохраняются в массиве resourceDataList. Каждый элемент этого массива представляет собой объект, описывающий различных персонажей фильма.

Объекты данных ресурсов имеют ряд общих полей, которые соответствуют информации об этом персонаже. Например, поле "name" соответствует имени этого персонажа в фильме. Мы предполагаем, что поля для каждого объекта данных ресурса идентичны, поскольку они предназначены для использования общих структур объектов. Список полей потребуется позже, поэтому код сохраняет его в resourceObjectKeys с помощью метода JavaScript Object.keys().

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

После создания листа вызывается вспомогательная функция fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) для добавления всех данных API на новый лист.

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

fetchApiResourceObject_(url)

Эта вспомогательная функция аналогична вспомогательной функции fetchBookData_(ISBN), использованной в предыдущей лабе "Работа с данными. Пользовательские меню". Она берет указанную ссылку и использует метод UrlFetchApp.fetch(url, params) для получения ответа. Затем ответ анализируется в объект JSON с использованием методов HTTPResponse.getContextText() и JavaScript JSON.parse(json). Затем возвращается результирующий объект JSON.

createNewSheet_(name)

Эта вспомогательная функция довольно проста. Сначала он проверяет, существует ли лист с заданным именем в Таблице. Если это так, функция активирует этот лист и возвращает его.

Если лист еще не существует, функция создает и активирует его с помощью метода Spreadsheet.insertSheet(sheetName) и возвращает этот лист.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

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

Сначала функция вычисляет количество строк и колонок, необходимых для представления новых данных API. Это просто размер списка ресурсов и полей соответственно. Затем функция определяет выходной диапазон (resourceRange), в который будут помещены данные, добавляя дополнительную строку для хранения заголовков колонок. Переменная resourceValues содержит двумерный массив значений, извлеченный из resourceRange.

Затем функция перебирает каждое поле объекта в списке objectKeys. Поле устанавливается как заголовок колонки, а затем второй цикл проходит через каждый объект ресурса. Для каждой пары (строка, колонка) соответствующая информация API копируется в элемент resourceValues [row][column].

После заполнения resourceValues ​​целевой лист очищается с помощью Sheet.clear() (в случае, если на нем были какие-либо данные из предыдущих вызовов по пункту меню). Наконец, новые значения записываются на лист.

Результат

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

  1. Если вы еще этого не сделали, сохраните проект в редакторе Apps Script.
  2. Щелкните пункт меню Таблицы Быстрое форматирование > Создать лист персонажей > Эпизод IV.

Результат должен выглядеть следующим образом:

Вы написали код, который импортирует данные в Таблицы и автоматически форматирует их! Отличная работа!

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

Считаете ли вы эту лабу полезной?

1) Да 2) Нет

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

Что дальше

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

Пройдите следующую практическую работу Основы Apps Script в Google Таблицах №5: Диаграммы и представление данных на Слайдах!