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

Последнее обновление: 2021-02-24

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

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

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

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

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

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

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

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

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

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

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

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

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

onOpen() - пример простого триггера. Простые триггеры легко настроить - все, что вам нужно сделать, это написать функцию под названием onOpen(), и Apps Script будет выполнять ее каждый раз при открытии или перезагрузке связанной Таблицы:

/**
 * Специальная функция, выполняемая, когда Таблица 
 * открылась или перезагрузилась. onOpen() используется для добавления
 * пользовательских меню.
 */
function onOpen() {
 /* ... */ 
}

Реализация

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

Замените весь код в своем проекте следующим:

/**
 * Специальная функция, выполняемая, когда Таблица 
 * открылась или перезагрузилась. onOpen() используется для добавления
 * пользовательских меню.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Список книг')
    .addItem('Загрузить список книг', 'loadBookList')
    .addToUi();
}

Сохраните проект.

Обзор кода

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

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

addItem(caption, functionName) создает связь между меткой пункта меню и функцией скрипта, которая запускается при выборе пункта меню. Выбор пункта меню "Загрузить список книг" приведет к тому, что Apps Script попытается выполнить функцию loadBookList() (которая еще не существует).

Результат

Давайте запустим этот код, чтобы узнать, работает ли оно! Сделайте следующее:

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

После перезагрузки самой Таблицы в строке меню появится новое под названием "Список книг". Нажав на Меню > Список книг, вы увидите выпадающий список:

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

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

В настоящее время в меню "Список книг" есть один пункт "Загрузить список книг". Однако функция, которую вызывается при выборе этого пункта, loadBookList(), не существует в вашем проекте, поэтому Меню > Список книг > Загрузка списка книг вызывает ошибку:

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

Реализация

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

Добавьте следующий код в свой проект сразу под кодом функции onOpen():

/**
 * Специальная функция, выполняемая, когда Таблица 
 * открылась или перезагрузилась. onOpen() используется для добавления
 * пользовательских меню.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Список книг')
    .addItem('Загрузить список книг', 'loadBookList')
    .addToUi();
}

/** 
 * Создает шаблонный список книг
 * на основе предоставленного листа Таблицы 'contributor.pw-лабы-список-книг'.
 */
function loadBookList() {
  // Получает активный лист.
  var sheet = SpreadsheetApp.getActiveSheet();

  // Получает другую Таблицу на Google Диске
  // через ID этой Таблицы. 
  var bookSS = SpreadsheetApp.openById(
    "1j65xRzBQ4SVOw066Uf8WWoGUOOEI4LSdf38A9l-rnmw"
  );

  // Получает лист, диапазон данных и значения
  // Таблицы, хранящейся в bookSS.
  var bookSheet = bookSS.getSheetByName("contributor.pw-лабы-список-книг");
  var bookRange = bookSheet.getDataRange();
  var bookListValues = bookRange.getValues();

  // Добавляет эти значения в активный лист
  // Текущей Таблицы. Это действие перезаписывает все данные, какие уже
  // были до момента вызова функции. 
  sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth())
    .setValues(bookListValues);

  // Переименовывает лист назначения и изменяет размеры клолонок
  // для большей наглядности.
  sheet.setName("Список книг");
  sheet.autoResizeColumns(1, 3);
}

Сохраните проект.

Обзор кода

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

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

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

2: Идентификация источника данных

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

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

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

4: Форматирование листа назначения

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

Результат

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

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

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

  1. В некоторых строках заголовок и автор помещены вместе в столбце заголовка и связаны запятой или словом "by".
  2. В некоторых строках отсутствует информация об их названиях и авторах.

В следующих разделах мы исправим эти проблемы.

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

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

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

Реализация

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

  1. Обновите код onOpen() в вашем проекте, чтобы он соответствовал следующему:
/**
 * Специальная функция, выполняемая, когда Таблица 
 * открылась или перезагрузилась. onOpen() используется для добавления
 * пользовательских меню.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Список книг')
    .addItem('Загрузить список книг', 'loadBookList')
    .addSeparator()
    .addItem(
      'Разделить заголовок/автор по первой запятой', 'splitAtFirstComma')
    .addItem(
      'Разделить заголовок/автор по последнему "by"', 'splitAtLastBy')
    .addSeparator()
    .addItem(
      'Заполнить пустые ячейки заголовков и авторов', 'fillInTheBlanks')
    .addToUi();
}
  1. Сохраните проект.
  2. В редакторе сценариев выберите onOpen в раскрывающемся меню функций и нажмите "Выполнить". Запустится функция onOpen(), чтобы перестроить меню вашей Таблицы. Вам не придется перезагружать вкладку браузера.

В этом новом коде метод Menu.addSeparator() создает горизонтальный разделитель, чтобы можно было визуально упорядочивать группы связанных пунктов меню. Затем добавляются новые пункты меню с метками "Разделить заголовок/автор по первой запятой", "Разделить заголовок/автор по последнему "by"" и "Заполнить пустые ячейки заголовков и авторов".

Результат

Можете проверить меню списка книг в Таблице:

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

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

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

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

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

  1. Найти диапазон, который представляет текущий выбор.
  2. Проверить, есть ли в ячейках диапазона запятые.
  3. Если обнаружены запятые, разделить строку на две (и только две) части в месте расположения первой запятой. Чтобы упростить задачу, вы можете предположить, что запятая указывает на некоторый паттерн "[авторы], [название]". Вы также можете предположить, что если в ячейке появляется несколько запятых, целесообразно разделить только по первой запятой в строке.
  4. Установить эти две части как новое содержимое соответствующих ячеек заголовка и автора.

Реализация

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

  1. Добавьте следующую функцию в редактор в конец файла вашего проекта:
/**
 * Изменяет колонки заголовка и автора,
 * разделяя значение колонки заголовка по первой запятой, если таковая имеется.
 */
function splitAtFirstComma() {
  // Получает активный (в данный момент выделенный) диапазон.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Получает значения выбранных ячеек.
  // Это двумерный массив.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Обновляем значения там, где есть запятые.
  // Предполагается, что наличие запятой указывает на шаблон "авторы, название".
  for (var row = 0; row < titleAuthorValues.length; row++) {
    var indexOfFirstComma =
      titleAuthorValues[row][0].indexOf(", ");

    if (indexOfFirstComma >= 0) {
      // Найдена запятая, разделяет и обновляет значения в массиве значений.
      var titlesAndAuthors = titleAuthorValues[row][0];

      // Обновляет значение заголовка в массиве.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(indexOfFirstComma + 2);

      // Обновляет значение автора в массиве.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(0, indexOfFirstComma);
    }
  }

  // Помещает обновленные значения обратно в Таблицу.
  titleAuthorRange.setValues(titleAuthorValues);
}
  1. Сохраните проект.

Обзор кода

Посмотрим на новый код. Он состоит из трех основных разделов:

1. Получение значения заголовка из активной области

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

2. Проверка каждого заголовка и разделение запятыми.

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

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

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

3: Копирование новых значений обратно на страницу

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

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

Результат

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

... для одной ячейки:

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

Вы создали скрипт, который обрабатывает данные Google Таблиц! Далее мы реализуем вторую функцию.

Если вы снова посмотрите на исходные данные, вы можете увидеть другую проблему. Так же, как некоторые заголовки и авторы в одной ячейки в формате "[авторы], [заголовок]", другие ячейки содержат автора и заголовок как "[заголовок] by [авторы]":

Реализация

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

  1. Добавьте следующую функцию в редактор скриптов в конец вашего проекта:
/** 
 * Изменяет колонки заголовка и автора,
 * разделяя значение колонки заголовка по вхождению слова " by ", если имеется.
 */
function splitAtLastBy() {
  // Получает активный (в данный момент выделенный) диапазон.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Получает значения выбранных ячеек.
  // Это двумерный массив.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Обновляем значения там, где есть текст " by ".
  // Предполагается, что наличие запятой указывает на шаблон "название by авторы".
  for (var row = 0; row < titleAuthorValues.length; row++) {
    var indexOfLastBy =
      titleAuthorValues[row][0].lastIndexOf(" by ");

    if (indexOfLastBy >= 0) {
      // Найдена фраза " by ", разделяет и обновляет значения в массиве значений.
      var titlesAndAuthors = titleAuthorValues[row][0];

      // Обновляет значение заголовка в массиве.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);

      // Обновляет значение автора в массиве.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

  // Помещает обновленные значения обратно в Таблицу.
  titleAuthorRange.setValues(titleAuthorValues);
}
  1. Сохраните проект.

Обзор кода

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

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

Результат

Теперь вы можете увидеть функцию splitAtLastBy() в действии! Попробуйте выполнить ее, нажав в своем меню "Разделить заголовок/автор по последнему "by"" после активации нужной ячейки ...

... для одной ячейки:

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

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

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

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

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

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

Далее вы узнаете, как:

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

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

Реализация

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

  1. Добавьте следующий код в редактор скриптов в конец файла вашего проекта:
/**
 *
 * Вспомогательная функция,
 * которая извлекает данные о книге из общедоступного API Open Library.
 *
 * @param {number} ISBN - Номер ISBN книги, которую нужно найти.
 * @return {object} Данные книги в формате JSON.
 */
function fetchBookData_(ISBN){
  // Подключение к публичному API.
  var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
      + ISBN + "&jscmd=details&format=json";
  var response = UrlFetchApp.fetch(
      url, {'muteHttpExceptions': true});
  
  // Делает запрос к API и получает ответ.
  var json = response.getContentText();
  var bookData = JSON.parse(json); 
  
  // Возвращает только интересующую нас информацию. 
  return bookData['ISBN:' + ISBN];
}
  1. Сохраните проект.

Обзор кода

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

1. Создание запроса к API

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

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

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

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

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

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

Результат

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

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

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

Реализация

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

  1. Добавьте следующий код в редактор скриптов в конец файла вашего проекта:
/**
 * Заполняет отсутствующие данные заголовка и автора
 * с помощью вызовов Open Library API.
 */ 
function fillInTheBlanks(){
  // Константы, определяющие индекс столбцов заголовка, автора и ISBN
  // (в двумерном массиве bookValues ниже).
  var TITLE_COLUMN = 0;
  var AUTHOR_COLUMN = 1;
  var ISBN_COLUMN = 2;

  // Получает информацию о текущей книге на активном листе.
  // Данные помещаются в двумерный массив.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
  var bookValues = dataRange.getValues();

  // Проверяет каждую строку данных (исключая строку заголовка).
  // Если ISBN присутствует, а заголовок или автор отсутствуют,
  // используется метод fetchBookData_(ISBN)
  // для получения недостающих данных из Open Library API.
  // Заполняет недостающие названия или авторов, когда они будут найдены.
  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 === "") ){
      // Вызывает API только в том случае,
      // если у вас есть номер ISBN и отсутствует название или автор.
      var bookData = fetchBookData_(isbn);

      // Иногда API не возвращает необходимую информацию.
      // В таких случаях не пытается обновить строку дальше.
      if (!bookData || !bookData.details) {
        continue;
      }

      // API может не иметь заголовка, поэтому заполняет его,
      // только если API возвращает заголовок, а заголовок на листе пуст.
      if(title === "" && bookData.details.title){
        bookValues[row][TITLE_COLUMN] = bookData.details.title; 
      }

      // API может не иметь имени автора, поэтому заполняет его,
      // только в том случае, если API возвращает автора, а автор пуст в таблице.
      if(author === "" && bookData.details.authors
          && bookData.details.authors[0].name){
        bookValues[row][AUTHOR_COLUMN] =
          bookData.details.authors[0].name; 
      }
    }
  }
  
  // Помещает обновленные значения данных о книги обратно в Таблицу.
  dataRange.setValues(bookValues);   
}
  1. Сохраните проект.

Обзор кода

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

1. Чтение существующей информации о книге

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

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

Далее код проходит по каждой строке в bookValues ​​для поиска отсутствующих названий или авторов. Чтобы уменьшить количество вызовов API, которые должен выполнять код (что позволяет избежать потери времени), код вызывает API только в том случае, если верно следующее:

  1. Строка имеет значение в столбце ISBN (то есть известен 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. Выберите Список книг > Заполнить пустые ячейки заголовков и авторов, чтобы заполнить все оставшиеся ячейки:

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

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

1) Да 2) Нет

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

Что дальше

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

Найдите следующую лабу кода в Основы Apps Script в Google Таблицах №4: Форматирование данных!