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

1. Введение

Добро пожаловать в третью часть плейлиста по основам написания сценариев приложений с использованием Google Таблиц.

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

Чему вы научитесь

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

Прежде чем начать

Это третья практическая работа в плейлисте «Основы скриптов приложений с Google Таблицами». Прежде чем приступить к этой практической работе, обязательно выполните предыдущие:

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

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

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

2. Настройка

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

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

545c02912de7d112.png

  1. Чтобы открыть редактор скриптов, нажмите «Расширения» > «Скрипт приложений».
  2. Щёлкните по названию проекта Apps Script и измените его с «Безымянный проект» на «Управление данными и пользовательские меню». Щёлкните «Переименовать» , чтобы сохранить изменение названия.

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

3. Обзор: импорт данных с помощью пользовательского пункта меню

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

d6b694da6b8c6783.png

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

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

4. Функция onOpen()

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

onOpen() — пример простого триггера. Их легко настроить — достаточно написать функцию Apps Script с именем onOpen() , и Apps Script будет запускать её каждый раз при открытии или перезагрузке соответствующей электронной таблицы:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
 /* ... */ 
}

Выполнение

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

  1. Замените код в вашем проекте скрипта следующим: 
/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addToUi();
}
  1. Сохраните свой проект сценария.

Обзор кода

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

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

Метод addItem(caption, functionName) создаёт связь между меткой пункта меню и функцией Apps Script, которая запускается при выборе пункта меню. В этом случае выбор пункта меню Load Book-list приводит к тому, что Sheets пытается запустить функцию loadBookList() (которая пока не существует).

Результаты

Запустите эту функцию сейчас, чтобы убедиться, что она работает:

  1. Перезагрузите таблицу в Google Таблицах. Примечание: обычно при этом закрывается вкладка с редактором скриптов.
  2. Снова откройте редактор сценариев, выбрав «Инструменты» > «Редактор сценариев» .

После перезагрузки электронной таблицы в строке меню должно появиться новое меню Book-list :

687dfb214f2930ba.png

Нажав «Список книг» , вы увидите появившееся меню:

8a4a391fbabcb16a.png

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

5. Импорт данных электронной таблицы

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

Сейчас в пользовательском меню Book-list есть один пункт: Load Book-list. Функция loadBookList(), вызываемая при выборе пункта меню Load Book-list , отсутствует в вашем скрипте, поэтому выбор «Список книг» > «Загрузить список книг» приводит к ошибке:

b94dcef066e7041d.gif

Эту ошибку можно исправить, реализовав функцию loadBookList() .

Выполнение

Вам нужно, чтобы новый пункт меню заполнял электронную таблицу данными для работы, поэтому вы реализуете loadBookList() для чтения данных книги из другой электронной таблицы и копирования их в эту:

  1. Добавьте следующий код в ваш скрипт под onOpen() : 
/** 
 * Creates a template book list based on the
 * provided 'codelab-book-list' sheet.
 */
function loadBookList(){
  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive using
  // the spreadsheet's ID. 
  var bookSS = SpreadsheetApp.openById(
    "1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo" 
  );

  // Gets the sheet, data range, and values of the
  // spreadsheet stored in bookSS.
  var bookSheet = bookSS.getSheetByName("codelab-book-list");
  var bookRange = bookSheet.getDataRange();
  var bookListValues = bookRange.getValues();

  // Add those values to the active sheet in the current
  // spreadsheet. This overwrites any values already there.
  sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth()) 
    .setValues(bookListValues);
  
  // Rename the destination sheet and resize the data
  // columns for easier reading.
  sheet.setName("Book-list");
  sheet.autoResizeColumns(1, 3);
}
  1. Сохраните свой проект сценария.

Обзор кода

Как же работает эта функция? Функция loadBookList() использует методы в основном из классов Spreadsheet , Sheet и Range , представленных в предыдущих практических занятиях. Учитывая эти концепции, код loadBookList() можно разбить на следующие четыре части:

1: Определите целевой лист

Первая строка использует SpreadsheetApp.getActiveSheet() для получения ссылки на текущий объект листа и сохранения её в переменной sheet . Это лист, на который будут скопированы данные.

2. Определите исходные данные

Следующие несколько строк устанавливают четыре переменные, которые ссылаются на исходные данные, которые вы извлекаете:

  • bookSS хранит ссылку на электронную таблицу, из которой код считывает данные. Код находит таблицу по её идентификатору . В этом примере мы указали идентификатор исходной таблицы для чтения и открыли её с помощью метода SpreadsheetApp.openById(id) .
  • bookSheet хранит ссылку на лист в bookSS , содержащий нужные вам данные. Код идентифицирует лист, из которого нужно прочитать данные, по его имени: codelab-book-list .
  • bookRange хранит ссылку на диапазон данных в bookSheet . Метод Sheet.getDataRange() возвращает диапазон, содержащий все непустые ячейки листа. Это простой способ получить диапазон, охватывающий все данные листа, исключая пустые строки и столбцы.
  • bookListValues — это двумерный массив, содержащий все значения ячеек в bookRange . Метод Range.getValues() генерирует этот массив, считывая данные из исходного листа.

3. Копирование данных из источника в место назначения

Следующий раздел кода копирует данные bookListValues в sheet , а затем также переименовывает лист:

  • Sheet.getRange(row, column, numRows, numColumns) используется для определения места копирования данных в sheet .
  • Методы Range.getHeight() и Range.getWidth() используются для измерения размера данных и определения целевого диапазона тех же измерений.
  • Range.setValues(values) копирует двумерный массив bookListValues в целевой диапазон, перезаписывая все уже имеющиеся там данные.

4: Отформатируйте целевой лист

Функция Sheet.setName(name) используется для изменения имени целевого листа на Book-list . В последней строке функции используется Sheet.autoResizeColumns(startColumn, numColumns) для изменения размера первых трёх столбцов целевого листа, что упрощает чтение новых данных.

Результаты

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

3c797e1e2b9fe641.gif

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

6. Обзор: очистка данных электронной таблицы

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

  1. В некоторых строках название и автор размещаются вместе в столбце «Название», соединяясь запятой или строкой «by».
  2. В некоторых строках отсутствует название книги или автор.

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

7. Добавить пункты меню

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

Выполнение

Давайте обновим onOpen() , добавив необходимые дополнительные пункты меню. Выполните следующие действия:

  1. В вашем проекте скрипта обновите код onOpen() , чтобы он соответствовал следующему: 
/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addSeparator()
    .addItem(
      'Separate title/author at first comma', 'splitAtFirstComma')
    .addItem(
      'Separate title/author at last "by"', 'splitAtLastBy')
    .addSeparator()
    .addItem(
      'Fill in blank titles and author cells', 'fillInTheBlanks')
    .addToUi();
}
  1. Сохраните свой проект сценария.
  2. В редакторе скриптов выберите onOpen в списке функций и нажмите кнопку «Выполнить» . Это запустит onOpen() для перестроения меню электронной таблицы, поэтому вам не придётся перезагружать её.

В этом новом коде метод Menu.addSeparator() создаёт горизонтальный разделитель в меню для визуальной организации групп связанных пунктов. Затем под ним добавляются новые пункты меню с метками: Separate title/author at first comma , Separate title/author at last "by" и Fill in blank titles and author cells .

Результаты

В электронной таблице щелкните меню Book-list чтобы просмотреть новые пункты меню:

580c806ce8fd4872.png

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

8. Разделение текста запятыми

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

ca91c43c4e51d6b5.png

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

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

Функция splitAtFirstComma() должна выполнять следующие шаги:

  1. Получить диапазон, представляющий текущие выбранные ячейки.
  2. Проверьте, есть ли в ячейках диапазона запятая.
  3. При наличии запятых разделите строку на две (и только две) подстроки по месту первой запятой. Для упрощения можно считать, что любая запятая указывает на шаблон строки « [авторы], [название] ». Также можно предположить, что если в ячейке встречается несколько запятых, разделение строки производится по первой запятой.
  4. Установите подстроки в качестве нового содержимого соответствующих ячеек заголовка и автора.

Выполнение

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

  1. В редакторе скриптов приложений добавьте следующую функцию в конец проекта скрипта: 
/**
 * Reformats title and author columns by splitting the title column
 * at the first comma, if present.
 */
function splitAtFirstComma(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where commas are found. Assumes the presence
  // of a comma indicates an "authors, title" pattern.
  for (var row = 0; row < titleAuthorValues.length; row++){
    var indexOfFirstComma =
        titleAuthorValues[row][0].indexOf(", ");

    if(indexOfFirstComma >= 0){
      // Found a comma, so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];

      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(indexOfFirstComma + 2);

      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(0, indexOfFirstComma);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}
  1. Сохраните свой проект сценария.

Обзор кода

Давайте рассмотрим новый код, состоящий из трех основных разделов:

1: Извлечь выделенные значения заголовков

Первые три строки устанавливают три переменные, которые ссылаются на текущие данные в листе:

  • activeRange представляет диапазон, выделенный пользователем в момент вызова функции splitAtFirstComma() . Для простоты предположим, что пользователь выделяет ячейки только в столбце A.
  • titleAuthorRange представляет собой новый диапазон, охватывающий те же ячейки, что и activeRange , но также включающий ещё один столбец справа. titleAuthorRange создаётся с помощью метода Range.offset(rowOffset, columnOffset, numRows, numColumns) . Коду нужен этот расширенный диапазон, поскольку ему нужно место для хранения всех авторов, найденных в столбце title.
  • titleAuthorValues — это двумерный массив данных, извлеченных из titleAuthorRange с помощью Range.getValues() .

2: Проверьте каждый заголовок и разделите его по первой найденной запятой-разделителю.

В следующем разделе мы проверим значения в titleAuthorValues на наличие запятых. Цикл JavaScript For используется для проверки всех значений в первом столбце titleAuthorValues . При обнаружении подстроки с запятой ( ", " ) с помощью метода JavaScript String indexOf() код выполняет следующие действия:

  1. Строковое значение ячейки копируется в переменную titlesAndAuthors .
  2. Расположение запятой определяется с помощью метода JavaScript String indexOf() .
  3. Метод JavaScript String slice() вызывается дважды для получения подстроки перед разделителем-запятой и подстроки после разделителя.
  4. Подстроки копируются обратно в двумерный массив titleAuthorValues, перезаписывая существующие значения в этой позиции. Поскольку мы используем шаблон « [authors], [title] », порядок двух подстрок меняется на обратный: заголовок помещается в первый столбец, а авторы — во второй.

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

3: Скопируйте новые значения обратно в лист.

После проверки всех значений ячеек заголовка обновленный двумерный массив titleAuthorValues копируется обратно в электронную таблицу с помощью метода Range.setValues(values) .

Результаты

Теперь вы можете увидеть результаты работы функции splitAtFirstComma() в действии. Попробуйте запустить её, выбрав пункт меню «Разделить название/автора по первой запятой» после выбора...

...одна ячейка:

a24763b60b305376.gif

...или несколько ячеек:

89c5c89b357d3713.gif

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

9. Разделить текст по разделителям «by»

Взглянув на исходные данные, можно заметить ещё одну проблему. Так же, как в некоторых ячейках форматирование заголовков и авторов в одной ячейке выглядит как «[authors], [title]» , в других ячейках форматирование автора и заголовка выглядит как «[title] by [authors]» :

41f0dd5ac63b62f4.png

Выполнение

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

  1. В редакторе скриптов приложений добавьте следующую функцию в конец проекта скрипта: 
/** 
 * Reformats title and author columns by splitting the title column
 * at the last instance of the string " by ", if present.
 */
function splitAtLastBy(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where " by " substrings are found. Assumes
  // the presence of a " by " indicates a "title by authors"
  // pattern.
  for(var row = 0; row < titleAuthorValues.length; row++){
    var indexOfLastBy =
        titleAuthorValues[row][0].lastIndexOf(" by ");
    
    if(indexOfLastBy >= 0){
      // Found a " by ", so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);
      
      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}
  1. Сохраните свой проект сценария.

Обзор кода

Между этим кодом и splitAtFirstComma() есть несколько ключевых отличий:

  1. В качестве разделителя строк используется подстрока « by » вместо « , ».
  2. Здесь используется метод JavaScript String.lastIndexOf(substring) вместо String.indexOf(substring) . Это означает, что если в исходной строке есть несколько подстрок " by ", все, кроме by , считаются частью заголовка.
  3. После разделения строки первая подстрока устанавливается как заголовок, а вторая как автор (это обратный порядок по сравнению с splitAtFirstComma() ).

Результаты

Теперь вы можете увидеть результаты работы функции splitAtLastBy() в действии. Попробуйте запустить её, выбрав пункт меню «Разделить название/автора на последнем месте по...» после выбора...

...одна ячейка:

4e6679e134145975.gif

...или несколько ячеек:

3c879c572c61e62f.gif

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

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

10. Обзор: получение данных из общедоступных API

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

af0dba8cb09d1a49.png

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

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

В этом разделе вы узнаете, как:

  • Запросить данные о книгах из внешнего источника API.
  • Извлеките информацию о названии и авторе из полученных данных и запишите ее в электронную таблицу.

11. Извлечение внешних данных с помощью UrlFetch

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

Наша вспомогательная функция fetchBookData_(ISBN) принимает в качестве параметра 13-значный номер ISBN книги и возвращает данные об этой книге. Она подключается к API Open Library и извлекает из него информацию, а затем анализирует возвращаемый JSON-объект .

Выполнение

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

  1. В редакторе скриптов приложений добавьте следующий код в конец скрипта: 
/**
 * Helper function to retrieve book data from the Open Library
 * public API.
 *
 * @param {number} ISBN - The ISBN number of the book to find.
 * @return {object} The book's data, in JSON format.
 */
function fetchBookData_(ISBN){
  // Connect to the public API.
  var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
      + ISBN + "&jscmd=details&format=json";
  var response = UrlFetchApp.fetch(
      url, {'muteHttpExceptions': true});
  
  // Make request to API and get response before this point.
  var json = response.getContentText();
  var bookData = JSON.parse(json); 
  
  // Return only the data we're interested in.
  return bookData['ISBN:' + ISBN];
}
  1. Сохраните свой проект сценария.

Обзор кода

Этот кодекс разделен на два основных раздела:

1: API-запрос

В первых двух строках fetchBookData_(ISBN) подключается к общедоступному API Open Library, используя конечную точку URL API и службу URL Fetch Service Apps Script.

Переменная url — это просто строка URL, например, веб-адрес. Она указывает на местоположение на серверах Open Library. Она также включает три параметра ( bibkeys , jscmd и format ), которые сообщают серверам Open Library, какую информацию вы запрашиваете и как структурировать ответ. В данном случае вы указываете номер ISBN книги и запрашиваете подробную информацию в формате JSON.

После формирования строки URL код отправляет запрос к указанному местоположению и получает ответ. Это делается с помощью метода UrlFetchApp.fetch(url, params) . Он отправляет запрос информации на указанный вами внешний URL и сохраняет полученный ответ в переменной response . Помимо URL, код устанавливает необязательный параметр muteHttpExceptions в true . Это означает, что ваш код не остановится, если запрос приведет к ошибке API. Вместо этого возвращается ответ с ошибкой.

Запрос возвращает объект HTTPResponse , который хранится в переменной response . HTTP-ответы включают код ответа, HTTP-заголовки и основное содержимое ответа. Интересующая нас информация — это основное содержимое JSON, поэтому код должен извлечь его, а затем проанализировать JSON, чтобы найти и вернуть нужную информацию.

2. Анализ ответа API и возврат интересующей информации

В последних трёх строках кода метод HTTPResponse.getContentText() возвращает основное содержимое ответа в виде строки. Эта строка имеет формат JSON, но API Open Library определяет точное содержимое и формат. Метод JSON.parse(jsonString) преобразует строку JSON в объект JavaScript, что позволяет легко извлекать различные фрагменты данных. Наконец, функция возвращает данные, соответствующие номеру ISBN книги.

Результаты

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

12. Запишите данные API в электронную таблицу

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

  1. Определите отсутствующие данные о названии и авторе в активном диапазоне данных.
  2. Получите недостающие данные по определенной книге, вызвав API Open Library с помощью вспомогательного метода fetchBookData_(ISBN) .
  3. Обновите отсутствующие значения заголовка или автора в соответствующих ячейках.

Выполнение

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

  1. В редакторе скриптов приложений добавьте следующий код в конец проекта скрипта: 
/**
 * Fills in missing title and author data using Open Library API
 * calls.
 */ 
function fillInTheBlanks(){
  // Constants that identify the index of the title, author,
  // and ISBN columns (in the 2D bookValues array below). 
  var TITLE_COLUMN = 0;
  var AUTHOR_COLUMN = 1;
  var ISBN_COLUMN = 2;

  // Get the existing book information in the active sheet. The data
  // is placed into a 2D array.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
  var bookValues = dataRange.getValues();

  // Examine each row of the data (excluding the header row).
  // If an ISBN is present, and a title or author is missing,
  // use the fetchBookData_(isbn) method to retrieve the
  // missing data from the Open Library API. Fill in the
  // missing titles or authors when they're found.
  for(var row = 1; row < bookValues.length; row++){   
    var isbn = bookValues[row][ISBN_COLUMN];
    var title = bookValues[row][TITLE_COLUMN];
    var author = bookValues[row][AUTHOR_COLUMN];
   
    if(isbn != "" && (title === "" || author === "") ){
      // Only call the API if you have an ISBN number and
      // either the title or author is missing.
      var bookData = fetchBookData_(isbn);

      // Sometimes the API doesn't return the information needed.
      // In those cases, don't attempt to update the row.
      if (!bookData || !bookData.details) {
        continue;
      }

      // The API might not return a title, so only fill it in
      // if the response has one and if the title is blank in
      // the sheet.
      if(title === "" && bookData.details.title){
        bookValues[row][TITLE_COLUMN] = bookData.details.title; 
      }

      // The API might not return an author name, so only fill it in
      // if the response has one and if the author is blank in
      // the sheet.
      if(author === "" && bookData.details.authors
          && bookData.details.authors[0].name){
        bookValues[row][AUTHOR_COLUMN] =
          bookData.details.authors[0].name; 
      }
    }
  }
  
  // Insert the updated book data values into the spreadsheet.
  dataRange.setValues(bookValues);
}
  1. Сохраните свой проект сценария.

Обзор кода

Этот кодекс разделен на три раздела:

1: Прочитайте существующую информацию о книге

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

2: Извлечь недостающую информацию с помощью вспомогательной функции

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

  1. Столбец ISBN в строке имеет значение.
  2. Ячейка заголовка или автора в строке пуста.

Если условия верны, код вызывает API, используя вспомогательную функцию fetchBookData_(isbn) вы реализовали ранее, и сохраняет результат в переменной bookData . Теперь в ней должна быть недостающая информация, которую вы хотите вставить в таблицу.

Осталось только добавить информацию bookData в нашу таблицу. Однако есть один нюанс. К сожалению, публичные API, такие как Open Library Book API, иногда не предоставляют запрашиваемую вами информацию, а иногда могут столкнуться с другими проблемами, препятствующими её предоставлению. Если вы предполагаете, что каждый запрос к API будет выполнен успешно, ваш код будет недостаточно надёжным для обработки непредвиденных ошибок.

Чтобы ваш код мог обрабатывать ошибки API, он должен проверять корректность ответа API перед попыткой его использования. После того, как в коде есть bookData , он выполняет простую проверку наличия bookData и bookData.details перед попыткой чтения из них. Отсутствие любого из них означает, что в API нет нужных данных. В этом случае команда continue указывает коду пропустить эту строку — вы не сможете заполнить пропущенные ячейки, но, по крайней мере, ваш скрипт не завершится сбоем.

3: Запишите обновленную информацию обратно на лист.

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

Цикл завершается после проверки всех строк в таблице. Последний шаг — запись обновлённого массива bookValues обратно в электронную таблицу с помощью Range.setValues(values) .

Результаты

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

  1. Если вы еще этого не сделали, выделите диапазон A2:A15 на листе и выберите «Список книг» > «Разделить название/автора по первой запятой», чтобы устранить проблемы с запятыми.
  2. Если вы еще этого не сделали, выделите диапазон A2:A15 на листе и выберите «Список книг» > «Разделить название/автора» по полю «by», чтобы избавиться от проблем с полем «by».
  3. Чтобы заполнить все оставшиеся ячейки, выберите Список книг > Заполнить пустые ячейки заголовков и авторов :

826675a3437adbdb.gif

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

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

Оказался ли этот практический практикум полезным?

Да Нет

Что вы узнали

  • Как импортировать данные из таблицы Google.
  • Как создать пользовательское меню в функции onOpen() .
  • Как анализировать и обрабатывать строковые значения данных.
  • Как вызывать публичные API с помощью службы URL Fetch .
  • Как проанализировать данные объекта JSON , полученные из общедоступного источника API.

Что дальше?

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

Найдите следующий CodeLab в форматировании данных .