Основы скрипта приложений с Google Таблицами № 4: форматирование данных

1. Введение

Добро пожаловать в четвертую часть плейлиста «Основы скриптов приложений с лабораторным кодом Google Sheets».

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

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

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

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

Это четвертая лаборатория кода в плейлисте Fundamentals of Apps Script with Google Sheets. Прежде чем приступить к этой лабораторной работе, обязательно выполните предыдущие лабораторные работы:

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

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

• Понимание основных тем сценариев приложений, рассмотренных в предыдущих лабораторных работах этого плейлиста.
• Базовое знакомство с редактором скриптов приложений
• Базовое знакомство с Google Sheets
• Умение читать листы формата А1.
• Базовое знакомство с JavaScript и его классом String

2. Настроить

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

1. Щелкните эту ссылку, чтобы скопировать лист данных, а затем щелкните Сделать копию . Новая таблица помещается в папку Google Диска и называется «Копия форматирования данных».
2. Щелкните заголовок электронной таблицы и измените его с «Копия форматирования данных» на «Форматирование данных». Ваш лист должен выглядеть так, с некоторой базовой информацией о первых трех фильмах «Звездных войн»:

3. Выберите « Расширения» > « Сценарий приложений », чтобы открыть редактор сценариев.
4. Нажмите на название проекта Apps Script и измените его с «Проект без названия» на «Форматирование данных». Нажмите « Переименовать », чтобы сохранить изменение заголовка.

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

3. Создайте собственное меню

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

Реализация

Давайте создадим пользовательское меню.

1. В редакторе скриптов приложений замените код в проекте скрипта следующим:

/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the spreadsheet's user-interface object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
   .addItem('Format row header', 'formatRowHeader')
   .addItem('Format column header', 'formatColumnHeader')
   .addItem('Format dataset', 'formatDataset') 
  .addToUi();
}

2. Сохраните проект сценария.
3. В редакторе скриптов выберите onOpen из списка функций и нажмите Run . Это запустит onOpen() для перестройки меню электронной таблицы, поэтому вам не нужно перезагружать электронную таблицу.

Обзор кода

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

Следующие строки создают меню ( Quick formats ), добавляют в меню пункты меню ( Format row header , Format column header и Format dataset ), а затем добавляют меню в интерфейс электронной таблицы. Это делается с помощью createMenu(caption) , addItem(caption, functionName) и addToUi() соответственно.

Метод addItem(caption, functionName) создает связь между меткой пункта меню и функцией скрипта приложений, которая запускается при выборе пункта меню. Например, выбор пункта меню « Format row header строки» приводит к тому, что Sheets пытается запустить formatRowHeader() (которой еще не существует).

Полученные результаты

В электронной таблице щелкните меню « Quick formats », чтобы просмотреть новые элементы меню:

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

4. Отформатируйте строку заголовка

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

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

Реализация

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

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

/**
 * Formats top row of sheet using our header row style.
 */
function formatRowHeader() {
  // Get the current active sheet and the top row's range.
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
 
  // Apply each format to the top row: bold white text,
  // blue-green background, and a solid black border
  // around the cells.
  headerRange
    .setFontWeight('bold')
    .setFontColor('#ffffff')
    .setBackground('#007272')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

}

2. Сохраните проект сценария.

Обзор кода

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

Примечание . Здесь мы предполагаем, что строка заголовка, которую мы хотим отформатировать, всегда находится в строке 1, что наши данные начинаются с A1 и распространяются на некоторое количество смежных столбцов. Это нормально для данного упражнения, но в целом вы можете избежать этих предположений, разрешив пользователям указывать, какие ячейки следует форматировать (на случай, если структура их электронной таблицы отличается).

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

• Range.setFontWeight(fontWeight) используется для установки полужирного шрифта.
• Range.setFontColor(color) используется для установки белого цвета шрифта.
• Range.setBackground(color) используется для установки темно-сине-зеленого цвета фона.
• setBorder(top, left, bottom, right, vertical, horizontal, color, style) помещает сплошную черную рамку вокруг ячеек диапазона.

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

Полученные результаты

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

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

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

1. В диалоговом окне непроверенного приложения нажмите « Дополнительно » .
2. Щелкните Перейти к управлению данными и пользовательским меню (небезопасно) .
3. Щелкните Разрешить .

В этой лаборатории кода вы можете получить несколько запросов на разрешение. Подробнее об этом процессе можно прочитать в разделе Авторизация для сервисов Google .

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

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

5. Отформатируйте заголовок столбца

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

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

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

Реализация

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

1. В редакторе скриптов приложений добавьте следующую formatColumnHeader() в конец проекта скрипта:

/**
 * Formats the column header of the active sheet.
 */ 
function formatColumnHeader() {  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get total number of rows in data range, not including
  // the header row.
  var numRows = sheet.getDataRange().getLastRow() - 1;
  
  // Get the range of the column header.
  var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);
  
  // Apply text formatting and add borders.
  columnHeaderRange
    .setFontWeight('bold')
    .setFontStyle('italic')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
 
  // Call helper method to hyperlink the first column contents
  // to the url column contents.
  hyperlinkColumnHeaders_(columnHeaderRange, numRows); 
}

2. Добавьте следующие вспомогательные функции в конец проекта скрипта после функции formatColumnHeader() :

/**
 * Helper function that hyperlinks the column header with the
 * 'url' column contents. The function then removes the column.
 *
 * @param {object} headerRange The range of the column header
 *   to update.
 * @param {number} numRows The size of the column header.
 */
function hyperlinkColumnHeaders_(headerRange, numRows) {
  // Get header and url column indices.
  var headerColIndex = 1; 
  var urlColIndex = columnIndexOf_('url');  
  
  // Exit if the url column is missing.
  if(urlColIndex == -1)
    return; 
  
  // Get header and url cell values.
  var urlRange =
    headerRange.offset(0, urlColIndex - headerColIndex);
  var headerValues = headerRange.getValues();
  var urlValues = urlRange.getValues();
  
  // Updates header values to the hyperlinked header values.
  for(var row = 0; row < numRows; row++){
    headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
      + '","' + headerValues[row] + '")';
  }
  headerRange.setValues(headerValues);
  
  // Delete the url column to clean up the sheet.
  SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex);
}

/**
 * Helper function that goes through the headers of all columns
 * and returns the index of the column with the specified name
 * in row 1. If a column with that name does not exist,
 * this function returns -1. If multiple columns have the same
 * name in row 1, the index of the first one discovered is
 * returned.
 * 
 * @param {string} colName The name to find in the column
 *   headers. 
 * @return The index of that column in the active sheet,
 *   or -1 if the name isn't found.
 */ 
function columnIndexOf_(colName) {
  // Get the current column names.
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnHeaders =
    sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var columnNames = columnHeaders.getValues();
  
  // Loops through every column and returns the column index
  // if the row 1 value of that column matches colName.
  for(var col = 1; col <= columnNames[0].length; col++)
  {
    if(columnNames[0][col-1] === colName)
      return col; 
  }

  // Returns -1 if a column named colName does not exist. 
  return -1; 
}

3. Сохраните проект сценария.

Обзор кода

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

formatColumnHeader()

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

• Активный лист хранится в файле sheet .
• Количество строк в заголовке столбца вычисляется и сохраняется в numRows . Здесь код вычитает единицу, поэтому количество строк не включает заголовок столбца: title .
• Диапазон, охватывающий заголовок столбца, хранится в columnHeaderRange .

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

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

hyperlinkColumnHeaders_(headerRange, numRows)

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

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

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

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

columnIndexOf_(colName)

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

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

Полученные результаты

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

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

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

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

6. Отформатируйте свой набор данных

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

• Чередующиеся цвета фона строк (известные как полосы )
• Изменение формата даты
• Применение границ
• Авторазмер всех столбцов и строк

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

Реализация

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

1. В редакторе скриптов приложений добавьте следующую formatDataset() в конец проекта скрипта:

/**
 * Formats the sheet data, excluding the header row and column.
 * Applies the border and banding, formats the 'release_date'
 * column, and autosizes the columns and rows.
 */
function formatDataset() {
  // Get the active sheet and data range.
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var fullDataRange = sheet.getDataRange();

  // Apply row banding to the data, excluding the header
  // row and column. Only apply the banding if the range
  // doesn't already have banding set.
  var noHeadersRange = fullDataRange.offset(
    1, 1,
    fullDataRange.getNumRows() - 1,
    fullDataRange.getNumColumns() - 1);

  if (! noHeadersRange.getBandings()[0]) {
    // The range doesn't already have banding, so it's
    // safe to apply it.
    noHeadersRange.applyRowBanding(
      SpreadsheetApp.BandingTheme.LIGHT_GREY,
      false, false);
  }

  // Call a helper function to apply date formatting
  // to the column labeled 'release_date'.
  formatDates_( columnIndexOf_('release_date') );
  
  // Set a border around all the data, and resize the
  // columns and rows to fit.
  fullDataRange.setBorder(
    true, true, true, true, null, null,
    null,
    SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  sheet.autoResizeColumns(1, fullDataRange.getNumColumns());
  sheet.autoResizeRows(1, fullDataRange.getNumRows());
}

2. Добавьте следующую вспомогательную функцию в конец проекта скрипта после функции formatDataset() :

/** 
 * Helper method that applies a
 * "Month Day, Year (Day of Week)" date format to the
 * indicated column in the active sheet. 
 *
 * @param {number} colIndex The index of the column
 *   to format.
 */ 
function formatDates_(colIndex) {
  // Exit if the given column index is -1, indicating
  // the column to format isn't present in the sheet.
  if (colIndex < 0)
    return; 

  // Set the date format for the date column, excluding
  // the header row.
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
    .setNumberFormat("mmmm dd, yyyy (dddd)");
}

3. Сохраните проект сценария.

Обзор кода

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

formatDataset()

Эта функция следует той же схеме, что и предыдущие функции форматирования, которые вы уже реализовали. Во-первых, он получает переменные для хранения ссылок на активный лист (лист) и диапазон данных (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. Если вы еще этого не сделали, сохраните проект сценария в редакторе сценариев приложений.
2. Щелкните элемент меню Быстрое форматирование > Формат набора данных .

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

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

7. Получение и форматирование данных API

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

В последней лаборатории кода вы узнали, как извлекать данные из API. Вы будете использовать те же методы здесь. В этом упражнении мы будем использовать общедоступный API Star Wars (SWAPI) для заполнения электронной таблицы. В частности, вы будете использовать API для получения информации об основных персонажах трех оригинальных фильмов «Звездные войны».

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

Реализация

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

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

1. В редакторе сценариев приложений обновите onOpen() в проекте сценария, чтобы она соответствовала следующему:

/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the Ui object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
    .addItem('Format row header', 'formatRowHeader')
    .addItem('Format column header', 'formatColumnHeader')
    .addItem('Format dataset', 'formatDataset')
    .addSeparator()
    .addSubMenu(ui.createMenu('Create character sheet')
                .addItem('Episode IV', 'createPeopleSheetIV')
                .addItem('Episode V', 'createPeopleSheetV')
                .addItem('Episode VI', 'createPeopleSheetVI')
                )
    .addToUi();
}

2. Сохраните проект сценария.
3. В редакторе скриптов выберите onOpen из списка функций и нажмите Run . Это запустит onOpen() , чтобы перестроить меню электронной таблицы с добавленными вами новыми опциями.
4. Чтобы создать файл скрипта приложений, рядом с файлами щелкните Добавить файл. > Скрипт .
5. Назовите новый скрипт «API» и нажмите Enter. (Apps Script автоматически добавляет расширение .gs к имени файла сценария.)
6. Замените код в новом файле API.gs следующим:

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode IV.
 */
function createPeopleSheetIV() {
  createResourceSheet_('characters', 1, "IV");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode V.
 */
function createPeopleSheetV() {
  createResourceSheet_('characters', 2, "V");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode VI.
 */
function createPeopleSheetVI() {
  createResourceSheet_('characters', 3, "VI");
}

/** 
 * Creates a formatted sheet filled with user-specified
 * information from the Star Wars API. If the sheet with
 * this data exists, the sheet is overwritten with the API
 * information.
 *
 * @param {string} resourceType The type of resource.
 * @param {number} idNumber The identification number of the film.
 * @param {number} episodeNumber The Star Wars film episode number.
 *   This is only used in the sheet name.
 */
function createResourceSheet_(
    resourceType, idNumber, episodeNumber) { 
  
  // Fetch the basic film data from the API. 
  var filmData = fetchApiResourceObject_(
      "https://swapi.dev/api/films/" + idNumber);

  // Extract the API URLs for each resource so the code can
  // call the API to get more data about each individually.
  var resourceUrls = filmData[resourceType];
  
  // Fetch each resource from the API individually and push
  // them into a new object list.
  var resourceDataList = []; 
  for(var i = 0; i < resourceUrls.length; i++){
    resourceDataList.push(
      fetchApiResourceObject_(resourceUrls[i])
    ); 
  } 
  
  // Get the keys used to reference each part of data within
  // the resources. The keys are assumed to be identical for
  // each object since they're all the same resource type.
  var resourceObjectKeys = Object.keys(resourceDataList[0]);
  
  // Create the sheet with the appropriate name. It
  // automatically becomes the active sheet when it's created.
  var resourceSheet = createNewSheet_(
      "Episode " + episodeNumber + " " + resourceType);
  
  // Add the API data to the new sheet, using each object
  // key as a column header. 
  fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);
  
  // Format the new sheet using the same styles the
  // 'Quick Formats' menu items apply. These methods all
  // act on the active sheet, which is the one just created.
  formatRowHeader();
  formatColumnHeader();   
  formatDataset();

}

6. Добавьте следующие вспомогательные функции в конец файла проекта сценария API.gs :

/** 
 * Helper function that retrieves a JSON object containing a
 * response from a public API.
 *
 * @param {string} url The URL of the API object being fetched.
 * @return {object} resourceObject The JSON object fetched
 *   from the URL request to the API.
 */
function fetchApiResourceObject_(url) {
  // Make request to API and get response.
  var response =
    UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  
  // Parse and return the response as a JSON object.
  var json = response.getContentText();
  var responseObject = JSON.parse(json); 
  return responseObject; 
}

/** 
 * Helper function that creates a sheet or returns an existing
 * sheet with the same name.
 *
 * @param {string} name The name of the sheet.
 * @return {object} The created or existing sheet
 *   of the same name. This sheet becomes active.
 */ 
function createNewSheet_(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Returns an existing sheet if it has the specified
  // name. Activates the sheet before returning.
  var sheet = ss.getSheetByName(name);
  if (sheet) {
    return sheet.activate();
  }
  
  // Otherwise it makes a sheet, set its name, and returns it.
  // New sheets created this way automatically become the active
  // sheet.
  sheet = ss.insertSheet(name); 
  return sheet; 
}

/** 
 * Helper function that adds API data to the sheet.
 * Each object key is used as a column header in the new sheet.
 *
 * @param {object} resourceSheet The sheet object being modified.
 * @param {object} objectKeys The list of keys for the resources.
 * @param {object} resourceDataList The list of API
 *   resource objects containing data to add to the sheet.
 */
function fillSheetWithData_(
    resourceSheet, objectKeys, resourceDataList) {
  // Set the dimensions of the data range being added to the sheet.
  var numRows = resourceDataList.length;
  var numColumns = objectKeys.length;
  
  // Get the resource range and associated values array. Add an
  // extra row for the column headers.
  var resourceRange =
    resourceSheet.getRange(1, 1, numRows + 1, numColumns);
  var resourceValues = resourceRange.getValues(); 
  
  // Loop over each key value and resource, extracting data to
  // place in the 2D resourceValues array.
  for (var column = 0; column < numColumns; column++) {

    // Set the column header.
    var columnHeader = objectKeys[column];
    resourceValues[0][column] = columnHeader;
    
    // Read and set each row in this column.
    for (var row = 1; row < numRows + 1; row++) {
      var resource = resourceDataList[row - 1];
      var value = resource[columnHeader];
      resourceValues[row][column] = value;
    }
  }
  
  // Remove any existing data in the sheet and set the new values.
  resourceSheet.clear()
  resourceRange.setValues(resourceValues);
}

7. Сохраните проект сценария.

Обзор кода

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

onOpen()

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

Функции-оболочки

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

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

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

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

Примечание . При желании вы можете создать дополнительные функции-оболочки для заполнения различных типов листов данными. Вам просто нужно знать, какой тип ресурса запрашивать и какой идентификатор фильма использовать. Примеры типов ресурсов включают « planets » и « starships », а API принимает числа от 1 до 7 в качестве идентификаторов фильмов.

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) , которая использовалась в предыдущей лаборатории кода Работа с данными . Он принимает указанный URL-адрес и использует метод UrlFetchApp.fetch(url, params) для получения ответа. Затем ответ преобразуется в объект JSON с использованием HTTPResponse.getContextText() и JavaScript JSON.parse(json) . Затем возвращается результирующий объект JSON.

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

createNewSheet_(name)

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

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

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

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

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

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

Примечание . Помните, что строки и столбцы в Таблицах имеют индекс 1, а массивы JavaScript — индекс 0. Из-за этой разницы нам часто приходится прибавлять или вычитать 1 из индексов при программировании на Apps Script.

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

Полученные результаты

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

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

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

Вы написали код для импорта данных в Таблицы и их автоматического форматирования.

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

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

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

Да Нет

Что вы узнали

• Как применять различные операции форматирования Таблиц с помощью Apps Script.
• Как создать подменю с помощью функции onOpen() .
• Как отформатировать полученный список объектов JSON в новый лист данных с помощью Apps Script.

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

• Активный (статус) : указывает, что указанная электронная таблица, лист, диапазон или ячейка являются теми, которые в данный момент просматривает или выделяет пользователь электронной таблицы.
• Активная ячейка : одна выделенная ячейка на активном листе с фокусом курсора.
• Активный диапазон : группа из одной или нескольких ячеек, выделенных в данный момент на активном листе.
• API : интерфейс прикладного программирования — это служба, которую программы или сценарии могут вызывать для запроса информации или выполнения определенных действий.
• Авторизация . Процесс предоставления пользователем разрешений, позволяющих Apps Script получать доступ к пользовательским данным в соответствующих службах Google или иным образом выполнять действия от имени пользователя.
• Скрипт, привязанный к контейнеру : любой скрипт, связанный с документом Google Workspace, например Google Sheet или Google Doc, и созданный на его основе.
• Вспомогательная функция : функция, обычно закрытая, которая вызывается другим для выполнения небольшой подзадачи. Вспомогательные функции используются, когда подзадачу нужно выполнить много раз в разных местах.
• JSON : формат файла для определения объектов данных и их характеристик из текста.
• onOpen() : если он определен в скрипте, это простой триггер, который срабатывает всякий раз, когда файл открывается или перезагружается.
• Частные функции : функция, которая не может быть вызвана как часть библиотеки или клиентами через взаимодействие сервер-клиент . Частные функции Apps Script имеют имена, оканчивающиеся на _ .
• Диапазон**: группа из одной или нескольких соседних ячеек на листе. Ассортимент Range class дает вам возможность читать и обновлять диапазоны на листе.
• Редактор скриптов : встроенный в браузер редактор кода для скриптов приложений.
• Простые триггеры : подтип триггеров в Apps Script с зарезервированными именами функций триггеров и определенными ограничениями .
• Лист**: одна страница электронной таблицы. Иногда используется для обозначения файла Google Таблиц на Google Диске. Лист Sheet class позволяет вам получать доступ к листам и изменять их.
• SpreadsheetApp : этот класс служит родительским классом для службы электронных таблиц и обеспечивает отправную точку для кода, который читает или обрабатывает данные Google Таблиц.
• Электронная таблица**: файл Google Sheets, который находится на Google Диске. Электронная Spreadsheet ** class позволяет вам получать доступ к электронным таблицам и изменять их.
• Служба электронных таблиц. Служба сценариев приложений, которая позволяет сценариям создавать, получать доступ и изменять файлы Google Таблиц.
• Триггеры : событие, связанное с функцией скрипта приложений. Когда происходит событие (например, открытие электронной таблицы), триггер «срабатывает» и автоматически выполняет связанную функцию скрипта приложений.
• Служба получения URL -адресов: служба сценариев приложений, которая позволяет сценариям подключаться к конечным точкам URL-адресов для выполнения запросов и получения ответов.
• Функции -оболочки: простые служебные функции, используемые для вызова более сложных функций с передачей им определенного набора параметров.

Что дальше

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

Найдите следующую кодовую лабораторию в Chart и представьте данные в Slides .