Основы скрипта приложений с Google Sheets #3: работа с данными

1. Введение

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

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

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

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

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

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

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

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

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

2. Настроить

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

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

545c02912de7d112.png

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

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

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

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

d6b694da6b8c6783.png

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

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

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

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

onOpen() — это пример простого триггера. Их легко настроить — все, что вам нужно сделать, это написать функцию скрипта приложений с именем 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() {
 /* ... */ 
}

Реализация

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

  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) создает связь между меткой пункта меню и функцией скрипта приложений, которая запускается при выборе пункта меню. В этом случае выбор пункта меню « Load Book-list приводит к тому, что Sheets попытается запустить loadBookList() (которой еще не существует).

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

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

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

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

687dfb214f2930ba.png

Нажав Book-list , вы увидите результирующее меню:

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 из списка функций и нажмите Run . Это запустится 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 Sheets предоставляет функцию 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) . Коду нужен этот расширенный диапазон, потому что ему нужно место для размещения любых авторов, которых он находит, в столбце заголовков.
  • 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, которая обрабатывает данные Sheets. Далее вы реализуете вторую функцию разделителя.

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

Глядя на исходные данные, можно увидеть еще одну проблему. Точно так же, как некоторые форматы данных заголовков и авторов в одной ячейке как «[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 открытой библиотеки .

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

Переменная 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 открытой библиотеки определяет точное содержимое и формат. Метод JSON.parse(jsonString) преобразует строку JSON в объект JavaScript, чтобы можно было легко извлечь различные части данных. Наконец, функция возвращает данные, соответствующие номеру ISBN книги.

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

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

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

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

  1. Определите отсутствующие данные заголовка и автора в активном диапазоне данных.
  2. Получите отсутствующие данные конкретной книги, вызвав Open Library API с помощью вспомогательного метода 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 на своем листе и выберите «Список книг»> «Разделить название / автора, наконец, «по», чтобы устранить проблемы «по».
  3. Чтобы заполнить все оставшиеся ячейки, выберите « Список книг» > «Заполнить пустые ячейки заголовков и авторов» :

826675a3437adbdb.gif

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

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

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

Да Нет

Что вы узнали

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

What's next

The next codelab in this playlist goes into more depth on how to format data within a spreadsheet.

Find the next codelab at Data formatting .