1. Введение
Добро пожаловать в четвертую часть плейлиста практической работы «Основы написания скриптов приложений с использованием Google Таблиц».
Выполнив эту лабораторную работу, вы научитесь форматировать данные электронных таблиц в Apps Script и писать функции для создания упорядоченных электронных таблиц, заполненных форматированными данными, полученными из общедоступного API.
Чему вы научитесь
- Как применять различные операции форматирования Google Таблиц в Apps Script.
- Как преобразовать список объектов JSON и их атрибутов в организованный лист данных с помощью Apps Script.
Прежде чем начать
Это четвёртая практическая работа в плейлисте «Основы скриптов приложений с Google Таблицами». Прежде чем приступить к этой практической работе, обязательно выполните предыдущие:
Что вам понадобится
- Понимание основных тем Apps Script, рассмотренных в предыдущих практикумах этого плейлиста.
- Базовые знания редактора Apps Script
- Базовые знания Google Таблиц
- Умение читать нотацию листов А1
- Базовые знания JavaScript и его класса
String
2. Настройка
Прежде чем продолжить, вам понадобится электронная таблица с данными. Как и прежде, мы предоставили таблицу, которую вы можете скопировать для этих упражнений. Выполните следующие действия:
- Нажмите на эту ссылку, чтобы скопировать таблицу данных , а затем нажмите «Сделать копию ». Новая таблица будет помещена в папку Google Диска и получит имя «Копия форматирования данных».
- Щёлкните по названию таблицы и измените его с «Копия форматирования данных» на «Форматирование данных». Ваша таблица должна выглядеть примерно так, с некоторыми основными сведениями о первых трёх фильмах «Звёздных войн»:
- Выберите Расширения > Скрипт приложений , чтобы открыть редактор скриптов.
- Щёлкните по названию проекта Apps Script и измените его с «Безымянный проект» на «Форматирование данных». Щёлкните «Переименовать », чтобы сохранить изменение названия.
С этой таблицей и проектом вы готовы приступить к работе над кодом. Перейдите к следующему разделу, чтобы начать изучать основы форматирования в Apps Script.
3. Создайте индивидуальное меню
В Apps Script можно применять несколько базовых методов форматирования к таблицам. В следующих упражнениях показано несколько способов форматирования данных. Чтобы лучше контролировать форматирование, давайте создадим пользовательское меню с необходимыми элементами. Процесс создания пользовательских меню был описан в практической работе «Работа с данными », но мы ещё раз кратко изложим его здесь.
Выполнение
Давайте создадим индивидуальное меню.
- В редакторе скриптов приложений замените код в вашем проекте скрипта следующим:
/**
* 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();
}
- Сохраните свой проект сценария.
- В редакторе скриптов выберите
onOpenв списке функций и нажмите кнопку «Выполнить» . Это запуститonOpen()для перестроения меню таблицы, поэтому вам не придётся перезагружать таблицу.
Обзор кода
Давайте рассмотрим этот код, чтобы понять, как он работает. В onOpen() первая строка использует метод getUi() для получения объекта Ui , представляющего пользовательский интерфейс активной электронной таблицы, к которой привязан этот скрипт.
Следующие строки создают меню ( Quick formats ), добавляют в него пункты ( Format row header , Format column header и Format dataset ), а затем добавляют меню в интерфейс электронной таблицы. Это делается с помощью методов createMenu(caption) , addItem(caption, functionName) и addToUi() соответственно.
Метод addItem(caption, functionName) создаёт связь между меткой пункта меню и функцией Apps Script, которая запускается при выборе пункта меню. Например, при выборе пункта меню Format row header Таблицы пытаются выполнить функцию formatRowHeader() (которая пока не существует).
Результаты
В электронной таблице щелкните меню Quick formats , чтобы просмотреть новые пункты меню:
Нажатие на эти элементы приводит к ошибке, поскольку вы не реализовали соответствующие им функции, поэтому давайте сделаем это дальше.
4. Отформатируйте строку заголовка
Наборы данных в электронных таблицах часто содержат строки заголовков, которые идентифицируют данные в каждом столбце. Рекомендуется отформатировать строки заголовков, чтобы визуально отделить их от остальных данных в таблице.
В первой лабораторной работе вы создали макрос для заголовка и скорректировали его код. Здесь вы отформатируете строку заголовка с нуля с помощью Apps Script. В созданной вами строке заголовка текст заголовка будет выделен жирным шрифтом, фон будет тёмно-сине-зелёным, текст — белым, а также будут добавлены сплошные границы.
Выполнение
Для реализации операции форматирования вам понадобятся те же методы сервиса электронных таблиц , что и раньше, но теперь вы также будете использовать некоторые из его методов форматирования. Выполните следующие действия:
- В редакторе скриптов приложений добавьте следующую функцию в конец проекта скрипта:
/**
* 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);
}
- Сохраните свой проект сценария.
Обзор кода
Как и многие другие задачи форматирования, код Apps Script для его реализации прост. Первые две строки используют методы, которые вы уже видели ранее, для получения ссылки на текущий активный лист ( sheet ) и верхнюю строку листа ( headerRange) . Метод Sheet.getRange(row, column, numRows, numColumns) определяет верхнюю строку, включая только те столбцы, в которых есть данные. Метод Sheet.getLastColumn() возвращает индекс последнего столбца, содержащего данные на листе. В нашем примере это столбец E ( url ).
Остальная часть кода просто вызывает различные методы 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 ) предписывают Apps Script не изменять линии границ в выбранном диапазоне. Седьмой параметр ( null ) указывает, что цвет границы по умолчанию должен быть чёрным. Наконец, последний параметр определяет тип используемого стиля границы, взятый из параметров, предоставляемых SpreadsheetApp.BorderStyle .
Результаты
Вы можете увидеть функцию форматирования в действии, выполнив следующие действия:
- Если вы еще этого не сделали, сохраните свой проект скрипта в редакторе Apps Script.
- Нажмите пункт меню Быстрое форматирование > Форматировать заголовок строки .
Результаты должны выглядеть следующим образом:
Вы автоматизировали задачу форматирования. В следующем разделе тот же метод применяется для создания другого стиля форматирования заголовков столбцов.
5. Форматирование заголовка столбца
Если вы можете создать персонализированный заголовок строки, вы можете создать и заголовок столбца. Заголовки столбцов повышают читаемость некоторых наборов данных. Например, столбец « Заголовки» в этой таблице можно улучшить, используя следующие варианты форматирования:
- Выделение текста жирным шрифтом
- Выделение текста курсивом
- Добавление границ ячеек
- Вставка гиперссылок с использованием содержимого столбца URL . После добавления гиперссылок столбец URL можно удалить, чтобы навести порядок в таблице.
Далее вы реализуете функцию formatColumnHeader() , чтобы применить эти изменения к первому столбцу таблицы. Чтобы сделать код более удобным для чтения, вы также реализуете две вспомогательные функции.
Выполнение
Как и прежде, вам необходимо добавить функцию для автоматизации форматирования заголовков столбцов. Выполните следующие действия:
- В редакторе скриптов приложений добавьте следующую функцию
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);
}
- Добавьте следующие вспомогательные функции в конец вашего проекта скрипта после функции
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;
}
- Сохраните свой проект сценария.
Обзор кода
Давайте рассмотрим код каждой из этих трех функций отдельно:
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)
Эта вспомогательная функция — простая вспомогательная функция, которая ищет заданное имя в первой строке таблицы. Первые три строки используют уже знакомые вам методы для получения списка заголовков столбцов из первой строки таблицы. Эти имена хранятся в переменной columnNames.
Затем функция просматривает каждое имя по порядку. Если находит имя, совпадающее с искомым, функция останавливается и возвращает индекс столбца. Если достигает конца списка имён, но искомое имя не найдено, функция возвращает -1, что означает, что имя не найдено.
Результаты
Вы можете увидеть функцию форматирования в действии, выполнив следующие действия:
- Если вы еще этого не сделали, сохраните свой проект скрипта в редакторе Apps Script.
- Нажмите пункт меню Быстрое форматирование > Форматировать заголовок столбца .
Результаты должны выглядеть следующим образом:
Вы автоматизировали ещё одну задачу форматирования. После форматирования заголовков столбцов и строк в следующем разделе показано, как отформатировать данные.
6. Отформатируйте свой набор данных
Теперь, когда у вас есть заголовки, давайте создадим функцию, которая форматирует остальные данные на вашей таблице. Мы будем использовать следующие параметры форматирования:
- Чередование цветов фона строк (известное как чередование )
- Изменение формата даты
- Применение границ
- Автоматическое изменение размера всех столбцов и строк
Теперь вы создадите функцию formatDataset() и дополнительный вспомогательный метод для применения этих форматов к данным вашего листа.
Выполнение
Как и прежде, добавьте функцию для автоматизации форматирования данных. Выполните следующие действия:
- В редакторе скриптов приложений добавьте следующую функцию
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());
}
- Добавьте следующую вспомогательную функцию в конец проекта скрипта после функции
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)");
}
- Сохраните свой проект сценария.
Обзор кода
Давайте рассмотрим код каждой из этих двух функций отдельно:
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) для применения форматирования.
Результаты
Вы можете увидеть функцию форматирования в действии, выполнив следующие действия:
- Если вы еще этого не сделали, сохраните свой проект скрипта в редакторе Apps Script.
- Нажмите пункт меню Быстрое форматирование > Форматировать набор данных .
Результаты должны выглядеть следующим образом:
Вы автоматизировали ещё одну задачу форматирования. Теперь, когда вам доступны эти команды форматирования, давайте добавим больше данных, к которым их можно применить.
7. Извлечение и форматирование данных API
В этой лабораторной работе вы уже рассмотрели, как использовать Apps Script в качестве альтернативного способа форматирования электронной таблицы. Далее вы напишете код, который извлекает данные из общедоступного API, вставляет их в вашу таблицу и форматирует для удобства чтения.
В предыдущей лабораторной работе вы научились извлекать данные из API. Здесь вы будете использовать те же методы. В этом упражнении мы воспользуемся общедоступным API «Звёздных войн» (SWAPI) для заполнения вашей таблицы. В частности, вы будете использовать API для получения информации о главных персонажах трёх оригинальных фильмов «Звёздных войн».
Ваш код будет вызывать API для получения большого объема данных JSON, анализировать ответ, помещать данные на новую таблицу, а затем форматировать таблицу.
Выполнение
В этом разделе вы добавите несколько дополнительных пунктов меню. Каждый пункт меню вызывает скрипт-обёртку, который передаёт переменные, специфичные для этого пункта, в основную функцию (createResourceSheet_()). Вы реализуете эту функцию и три дополнительные вспомогательные функции. Как и прежде, вспомогательные функции помогают изолировать логически разрозненные части задачи и поддерживать читаемость кода.
Выполните следующие действия:
- В редакторе скриптов приложений обновите функцию
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();
}
- Сохраните свой проект сценария.
- В редакторе скриптов выберите
onOpenв списке функций и нажмите кнопку «Выполнить» . Это запуститonOpen()которая перестроит меню электронной таблицы с учетом добавленных вами новых пунктов. - Чтобы создать файл скрипта приложений, рядом с пунктом «Файлы» нажмите «Добавить файл».
> Сценарий . - Назовите новый скрипт «API» и нажмите Enter. (Apps Script автоматически добавляет расширение
.gsк имени файла скрипта.) - Замените код в новом файле 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();
}
- Добавьте следующие вспомогательные функции в конец файла проекта скрипта 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);
}
- Сохраните свой проект сценария.
Обзор кода
Вы только что добавили много кода. Давайте рассмотрим каждую функцию отдельно, чтобы понять, как они работают:
onOpen()
Здесь вы добавили несколько пунктов меню в меню Quick formats . Вы установили разделительную линию, а затем использовали метод Menu.addSubMenu(menu) для создания вложенной структуры меню с тремя новыми пунктами. Новые пункты добавляются с помощью метода Menu.addItem(caption, functionName) .
Функции-обертки
Все добавленные пункты меню выполняют схожую функцию: пытаются создать таблицу с данными, полученными из 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) использованной в предыдущей практической работе «Работа с данными» . Она принимает заданный URL-адрес и использует метод 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() если он содержит данные о предыдущих нажатиях на пункты меню. После этого новые значения записываются на лист.
Результаты
Вы можете увидеть результаты своей работы, выполнив следующие действия:
- Если вы еще этого не сделали, сохраните свой проект скрипта в редакторе Apps Script.
- Нажмите пункт меню Быстрое форматирование > Создать лист персонажа > Эпизод IV .
Результаты должны выглядеть следующим образом:
Теперь вы написали код для импорта данных в Таблицы и их автоматического форматирования.
8. Заключение
Поздравляю с завершением этой лабораторной работы. Вы познакомились с некоторыми вариантами форматирования таблиц, которые можно включить в проекты Apps Script, и создали впечатляющее приложение, импортирующее и форматирующее большой набор данных API.
Оказался ли этот практический практикум полезным?
Что вы узнали
- Как применять различные операции форматирования таблиц с помощью Apps Script.
- Как создать подменю с помощью функции
onOpen(). - Как отформатировать извлеченный список объектов JSON в новый лист данных с помощью Apps Script.
Что дальше?
В следующем практическом занятии в этом плейлисте показано, как использовать Apps Script для визуализации данных в виде диаграммы и экспорта диаграмм в презентации Google Slides.
Найдите следующую лабораторную работу на Chart и представьте данные в слайдах .