Как открыть редактор скриптов в гугл таблицах

Как открыть редактор скриптов в гугл таблицах

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

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

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

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

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

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

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

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

• «Макросы и пользовательские функции»
• «Таблицы, Листы и Диапазоны»

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

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

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

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

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

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

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

В этой лабе вы можете несколько раз увидеть различные запросы на разрешение. Следуйте инструкциям на экране, чтобы продолжить авторизацию кода. Вы можете узнать больше об этом процессе в «Руководстве по авторизации скриптов приложений».

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

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

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

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

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

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

Ключевая идея: Простые триггеры легко настроить и использовать, но они имеют ряд ограничений. Из-за этих ограничений onOpen() обычно используется только для создания пунктов меню.

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

Реализация

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

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

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

Обзор кода

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

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

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

Результат

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

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

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

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

Важное примечание: из-за отсутствия на русском языке достойных открытых API сервисов, далее в примерах будет использоваться англоязычный список книг. Если вы хотите поддержать русский язык, то вы можете начать с перевода сервиса 2021 Q2 Internationalization: Making OpenLibrary Multilingual · Issue #791 · internetarchive/openlibrary

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

В настоящее время в меню «Список книг» есть один пункт «Загрузить список книг». Однако функция, которую вызывается при выборе этого пункта, 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: Идентификация источника данных

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

• bookSS сохраняет ссылку на Таблицу, из которой считываются данные. Код находит Таблицу по ее идентификатору (или ID). В этом примере мы дали идентификатор определенной Таблицы для чтения и открыли ее с помощью метода SpreadsheetApp.openById(id ) .
• bookSheet сохраняет ссылку на лист в bookSS , содержащий нужные данные. Код идентифицирует лист, с которого нужно прочитать по его названию «contributor.pw-лабы-список-книг».
• bookRange сохраняет ссылку на диапазон данных в bookSheet . Метод Sheet.getDataRange() возвращает диапазон, содержащий все непустые ячейки на вкладке. Это удобный способ убедиться, что вы получаете диапазон, охватывающий все данные на листе, без большого количества пустых строк и столбцов.
• bookListValues ​​- это двумерный массив, содержащий все значения, взятые из ячеек в bookRange . Метод Range.getValues​​() создает этот массив, считывая данные с исходной страницы.

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

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

• Sheet.getRange(row, column, numRows, numColumns) используется для определения того, куда следует скопировать данные на листе sheet .
• Методы Range.getHeight() и Range.getWidth() используются для измерения размера данных, чтобы потом присвоить диапазону назначения те же размеры.
• Range.setValues(values) копирует двумерный массив 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»» и «Заполнить пустые ячейки заголовков и авторов».

Примечание. Элементы меню, которые вы добавляете, отображаются в Таблицах в том же порядке, в котором вы добавляете их в код onOpen() .

Результат

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

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

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

Разделение текстовых строк на отдельные столбцы — обычная задача. 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. Получение значения заголовка из активной области

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

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

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.setValue(value) должен вызываться каждый раз, когда ячейка должна быть изменена. Однако это приведет к гораздо более медленному исполнению кода, поскольку каждый вызов Range.setValue(value) требует связи с сервером.

При обновлении данных Таблиц почти всегда лучше сначала получить значения в двумерном массиве, затем обновить массив, а затем вернуть обновленные данные с помощью одного вызова 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.
• извлечь информацию о названии и авторе из возвращенных данных и записать ее в свою Таблицу.

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

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

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

Реализация

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

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

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

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

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

Обзор кода

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

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

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

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

Примечание. Каждый общедоступный API определяет свои собственные форматы для отправки запросов и возврата результатов. Чтобы ваш код мог эффективно взаимодействовать с таким API, вам необходимо следовать форматам, которые его определяют. Для Open Library форматы запроса и ответа описаны в документации API Open LIbrary Books. Формат ответа здесь — JSON (JavaScript Object Notation), который представляет собой просто особый способ организации данных; JSON похож на HTML или XML, он содержит меньше информации о разметке данных, поэтому его легче читать.

После создания строки 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 для заполнения недостающей информации, а затем записывать эти значения обратно в Таблицу.

Примечание: расположение обрабатываемых данных отличается от того, что мы использовали для splitAtFirstComma() и splitAtLastBy() . В этих функциях код проверял только заголовки с разделителями и их последовательности. Здесь мы выполняем поиск по всему листу, используя Spreadsheet.getDataRange() .

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 для чтения, обработки и записи данных в Таблицы!

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

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

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

Ключевые концепции:

1. onOpen() — это простой триггер, используемый для создания пунктов меню. Простые триггеры имеют множество ограничений, с которыми вы можете столкнуться при создании программ на Apps Script.
2. При чтении или записи данных из ячеек Таблицы лучший способ читать или записывать сразу весь диапазон с помощью Range.getValues​​() или Range.setValues​(values) . Старайтесь не читать и не записывать в каждую ячейку, так как это сильно замедлит работу.
3. Функции Apps Script, имена которых заканчиваются на «_», считаются приватными. Эти функции не могут быть упомянуты в других сценариях, если они включены как часть библиотеки или клиентами при клиент-серверном взаимодействии. Если вы знаете, что функция будет использоваться только сценарием или хотите ограничить ее вызов пользователем из IDE, обычно рекомендуется закончить имя функции символом «_».
4. Если вы используете данные, возвращаемые внешними API, убедитесь, что возвращенные данные действительны, прежде чем пытаться их использовать. Ваш код с меньшей вероятностью будет иметь ошибки, если он будет обрабатывать неожиданные результаты API.

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

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

Что дальше

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

Google Apps Script: переносим расписание из таблицы в календарь

Это можно сделать, перейдя на страницу своего Google Drive.

Рис. 1. Правый клик мышью по рабочему пространству открывает контекстное меню, где нужно выбрать «Google Таблицы» — «Создать пустую таблицу»

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

• Название мероприятия
• Дата мероприятия
• Время начала
• Время конца или длительность (впрочем, это необязательно — в итоге вы сможете установить какое-нибудь общее дефолтное значение длительности мероприятия, например 1 час, или вовсе сделать мероприятие, длящимся целый день)

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

Рис. 2. Таблица с расписанием

2. Создаем скрипт

2.1. Переходим в редактор скриптов

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

Рис. 3. Скрипт создается, через меню «Инструменты», в котором нужно выбрать пункт «Редактор скриптов»

Скрипты разрабатываются на языке JavaScript. Только что созданный скрипт содержит одну пустую функцию myFunction. Писать код можно в ней, но я предпочту дать ей более осмысленное имя SetCalendar.

Рис. 4. Созданный скрипт, дадим функции осмысленное имя — SetCalendar

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

2.2. Разбираемся с отладкой

Часто для отладки скриптов на JavaScript мы выводим различные значения в консоль:

console.log("Hello, world!")

В Google Apps Script логировать значения следует немного иначе. Вместо объекта console следует использовать глобальный объект Logger:

function SetCalendar()

После запуска скрипта, все выведенные в лог значения можно посмотреть из меню «Вид», выбрав пункт «Журналы» или же по сочетанию клавиш Ctrl+Enter:

Рис. 5. Модальное окно с логами последнего запуска скрипта

2.3. Извлекаем информацию о мероприятиях из таблицы

Сразу небольшой кусок кода

function SetCalendar() < //Индексы первой строки и первого столбца в таблице с данными const rowStart = 1; const colStart = 1; //Количество строк и столбцов в расписании const colsCount = 5; const rowsCount = 67; //Получаем объект страницы var sheet = SpreadsheetApp.getActiveSheet(); //Извлекаем данные таблицы в указанных диапазонах var range = sheet.getRange(rowStart, colStart, rowsCount, colsCount) var data = range.getDisplayValues(); >

Рассмотрим строку 11.

Глобальный объект SpreadsheetApp — глобальный объект, содержащий методы, представляющие собой интерфейс для взаимодействия скрипта с приложением Google Sheets.

Первое, что нужно сделать — получить объект, представляющий активную (открытую в данный момент) страницу документа. Для этого необходимо вызвать метод getActiveSheet.

Метод возвращает объект sheet.

Далее, нужно получить объект range — он представляет собой диапазон ячеек таблицы, из которых мы будем извлекать данные. Это делается путем вызова метода getRange. В качестве параметров передаются:

• Индекс первой строки диапазона
• Индекс первого столбца диапазона
• Количество извлекаемых строк
• Количество извлекаемых столбцов

Обратите внимание! Индексация строк и столбцов начинается с 1.

Строка 15: мы получаем из выбранного выше диапазона ячеек непосредственно данные путем вызова метода getDisplayValues.

Обратите внимание! Метод getDisplayValues() отдает данные в том виде, в каком он отображается в ячейках таблицы. Например, если ячейка содержит дату, то указанный метод вернет не какое-то внутреннее представление записанной в ячейку даты, а ее представление, которое видит пользователь в ячейке.

2.4. Извлекаем данные из ячеек

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

//Константы-имена для индексов столбцов const dateCol = 0; const timeCol = 1; const typeCol = 2; const nameCol = 3; const teacherCol = 4;

Вы можете посмотреть какие столбцы стоят в таблице под данными номерами на рисунке 2.

Объект data представляет набор ячеек с данными, которые были получены. Теперь нужно получить данные из отдельных ячеек.

 for (var i in data) < let row = data[i]; let classDate = row[dateCol]; let classPeriod = row[timeCol]; let classType = row[typeCol]; let className = row[nameCol]; let classTeacher = row[teacherCol]; Logger.log("[DATE] " + classDate); Logger.log("[PERIOD] " + classPeriod); Logger.log("[TYPE] " + classType); Logger.log("[NAME] " + className); Logger.log("[TEACHER] " + classTeacher); Logger.log("=========================================== 25-rabota-s-obedinennymi-yacheykami">2.5. Работа с объединенными ячейками 
В результате выполнения предыдущих шагов, мы получаем следующий скрипт
 
function SetCalendar() < //Индексы первой строки и первого столбца в таблице с данными const rowStart = 1; const colStart = 1; //Количество строк и столбцов в расписании const colsCount = 5; const rowsCount = 8; //Получаем объект страницы var sheet = SpreadsheetApp.getActiveSheet(); //Извлекаем данные таблицы в указанных диапазонах var range = sheet.getRange(rowStart, colStart, rowsCount, colsCount) var data = range.getDisplayValues(); //Константы-имена для индексов столбцов const dateCol = 0; const timeCol = 1; const typeCol = 2; const nameCol = 3; const teacherCol = 4; for (var i in data) < let row = data[i]; let classDate = row[dateCol]; let classPeriod = row[timeCol]; let classType = row[typeCol]; let className = row[nameCol]; let classTeacher = row[teacherCol]; Logger.log("[DATE] " + classDate); Logger.log("[PERIOD] " + classPeriod); Logger.log("[TYPE] " + classType); Logger.log("[NAME] " + className); Logger.log("[TEACHER] " + classTeacher); Logger.log("=========================================== https://habrastorage.org/r/w1560/webt/at/ja/p0/atjap0smhmy37otnfzgzpx30_pw.png" data-src="https://habrastorage.org/webt/at/ja/p0/atjap0smhmy37otnfzgzpx30_pw.png"/> 
Рис. 6. Не все даты были правильно извлечены из таблицы
 
Из лога видно, что из первой строки данные были извлечены, а в следующих строках, которые описывают мероприятия того же дня, поле с датой оказалось пустым. Почему так происходит?
 
Еще раз взглянем на таблицу:
 
 
Рис. 7. Даты вписаны в объединенные ячейки
 
При создании таблицы, дата вписывалась только в первую строчку расписания на конкретную дату. После чего, все ячейки из столбца, относящиеся к одной дате были объединены.
 
Когда мы двигаемся по строкам таблицы, мы получаем дату не из всех ячеек, которые были объединены в одну, а только из первой.
 
Немного изменим алгоритм, чтобы корректно извлекать дату из объединенных ячеек:
 
 let savedDate = ""; for (var i in data) < let row = data[i]; let classDate = row[dateCol]; //. if (classDate.trim() == "") < classDate = savedDate; >else < savedDate = classDate; >Logger.log("[DATE] " + classDate); //. Logger.log("=========================================== 26-parsim-datu-i-vremya-iz-strok">2.6. Парсим дату и время из строк 
Как было отмечено выше — если мы используем метод getDisplayValues(), то значения из таблицы мы получаем в виде строк и ровно так, как они отображаются в ячейках, никаких внутренних представлений данных мы не получаем.
 
Как будет показано далее, чтобы создать событие в Google Calendar, нужно предварительно создать два объекта класса Date: дату-время начала события, дату-время окончания события.
 
В рассматриваемой таблице (см. рисунок 2), мы получаем дату занятия в формате dd.mm.yyyy, и временной промежуток, когда занятие длится, в формате hh:mm-hh.mm.
 
Из этих двух строк, нам нужно получить два объекта Date. Такая задача не касается Google Apps Script, с этим по идее должен справиться хоть сколько-нибудь опытный программист. Поэтому я не буду подробно останавливаться на алгоритме, а лишь приведу код двух JS-функций, которые парсят дату время и возвращают дату-время начала и дату-время окончания занятия.
 
Поехали:
 
function extractTime(timeStr, dateStr) < let sepIdx = timeStr.indexOf(":"); let hoursStr = timeStr.substring(0, sepIdx); let minsStr = timeStr.substring(sepIdx + 1); sepIdx = dateStr.indexOf("."); let dayStr = dateStr.substring(0, sepIdx); let monthStr = dateStr.substring(sepIdx + 1, sepIdx + 3); sepIdx = dateStr.indexOf(".", sepIdx + 1); let yearStr = dateStr.substring(sepIdx + 1); let t = new Date(); t.setHours(parseInt(hoursStr), parseInt(minsStr)); t.setYear(parseInt(yearStr)); t.setMonth(parseInt(monthStr) - 1, parseInt(dayStr)); return t; >function extractPeriod(periodStr, dateStr) < let sepIdx = periodStr.indexOf("-"); let fromStr = periodStr.substring(0, sepIdx); let toStr = periodStr.substring(sepIdx + 1); fromStr = fromStr.trim(); toStr = toStr.trim(); return < from: extractTime(fromStr, dateStr), to: extractTime(toStr, dateStr) >>
Чтобы получить дату-время начала и окончания занятия, нужно вызывать функцию extractPeriod и передать две строки — дата и временной период, в который длится занятие.
2.7. Создание события в Google Calendar
Сразу небольшой листинг, пояснения будут дальше
let classTimeInfo = extractPeriod(classPeriod, classDate); let classStartTime = classTimeInfo.from; let classEndTime = classTimeInfo.to; let info = "Преподаватель: " + classTeacher + "\nТип занятия: " + classType; var event = (CalendarApp.getCalendarsByName("Учеба"))[0].createEvent ( className, classStartTime, classEndTime, < description: info >); Utilities.sleep(50);
Строки 1-6: получаем дату-время начала и окончания занятия, а также склеиваем в одну строку всю дополнительную информацию, которая будет записана в описание мероприятия в календаре.
Далее, к интерфейсу сервиса Google Calendar мы обращаемся через методы глобального объекта CalendarApp.
Посредством метода getCalendarsByName мы получаем массив календарей с указанным именем.
Получить объект-календарь можно также посредством метода getDefaultCalendar, тогда событие будет создано в основном календаре. Однако, я советую зайти в веб-версию Google Calendar и создать новый календарь, в который вы будете экспортировать мероприятия. Во-первых, вы получаете возможность скрывать события этого календаря, выделять события отдельным цветом.
Но, что более важно, в случае, если вы при написании скрипта где-то ошиблись, и экспортировали события как-то неправильно, то вместо того, чтобы удалять все многочисленные неправильные события вручную, вы можете просто удалить календарь, после чего создать новый, пустой, и попробовать запустить новый, исправленный скрипт.
В данном случае я создал календарь "Учеба", и собираюсь создавать события в нем. Поэтому в коде вызывается метод getCalendarsByName и в качестве параметра передается срока "Учеба".
Метод возвращает массив календарей с указанным названием, поэтому нужно взять из этого массива один элемент, в нашем случае нулевой:
(CalendarApp.getCalendarsByName("Учеба"))[0]
У объекта-календаря необходимо вызывать метод createEvent. В качестве параметров нужно передать следующее:

Название мероприятия (в данном случае название дисциплины)
Объект Data — дата-время начала мероприятия
Объект Data — дата-время окончания мероприятия
Необязательный параметр — объект с дополнительными опциями (в данном случае я заполняю одно поле — description — это описание мероприятия)

Последняя строка в скрипте — задержка на 50 миллисекунд. Во время разработки скрипта и отладки я обнаружил, что иногда при выполнении кода выбрасывается исключение, суть которого в том, что Google Calendar не нравятся частые обращения к сервису, и в описании исключения рекомендуется вставить задержку между вызовами методов API календаря.
3. Итоговый скрипт
Ниже приведен полный скрипт, который получился
Обратите внимание! Данный скрипт заточен под структуру таблицы, которая приведена на рисунке 2. Если вы хотите использовать этот скрипт для себя как есть, убедитесь, что ваша таблица с расписанием имеет такую же структуру, или же измените скрипт под свою таблицу.
function extractTime(timeStr, dateStr) < let sepIdx = timeStr.indexOf(":"); let hoursStr = timeStr.substring(0, sepIdx); let minsStr = timeStr.substring(sepIdx + 1); sepIdx = dateStr.indexOf("."); let dayStr = dateStr.substring(0, sepIdx); let monthStr = dateStr.substring(sepIdx + 1, sepIdx + 3); sepIdx = dateStr.indexOf(".", sepIdx + 1); let yearStr = dateStr.substring(sepIdx + 1); let t = new Date(); t.setHours(parseInt(hoursStr), parseInt(minsStr)); t.setYear(parseInt(yearStr)); t.setMonth(parseInt(monthStr) - 1, parseInt(dayStr)); return t; >function extractPeriod(periodStr, dateStr) < let sepIdx = periodStr.indexOf("-"); let fromStr = periodStr.substring(0, sepIdx); let toStr = periodStr.substring(sepIdx + 1); fromStr = fromStr.trim(); toStr = toStr.trim(); return < from: extractTime(fromStr, dateStr), to: extractTime(toStr, dateStr) >> function SetCalendar() < //Индексы первой строки и первого столбца в таблице с данными const rowStart = 1; const colStart = 1; //Количество строк и столбцов в расписании const colsCount = 5; const rowsCount = 8; //Получаем объект страницы var sheet = SpreadsheetApp.getActiveSheet(); //Извлекаем данные таблицы в указанных диапазонах var range = sheet.getRange(rowStart, colStart, rowsCount, colsCount) var data = range.getDisplayValues(); //Константы-имена для индексов столбцов const dateCol = 0; const timeCol = 1; const typeCol = 2; const nameCol = 3; const teacherCol = 4; let savedDate = ""; for (var i in data) < let row = data[i]; let classDate = row[dateCol]; let classPeriod = row[timeCol]; let classType = row[typeCol]; let className = row[nameCol]; let classTeacher = row[teacherCol]; if (classDate.trim() == "") < classDate = savedDate; >else < savedDate = classDate; >let classTimeInfo = extractPeriod(classPeriod, classDate); let classStartTime = classTimeInfo.from; let classEndTime = classTimeInfo.to; let info = "Преподаватель: " + classTeacher + "\nТип занятия: " + classType; var event = (CalendarApp.getCalendarsByName("Учеба"))[0].createEvent ( className, classStartTime, classEndTime, < description: info >); Utilities.sleep(50); > >
4. Немножко скриншотов


Что можно улучшить

Некоторые значения в коде я просто захардкодил — например, индексы начала таблицы, размеры таблицы, количество строк (количество мероприятий). Теоретически, можно покопаться в API Google Sheets для Google Apps Script и придумать, как выцеплять нужные координаты ячеек автоматически;
Я никогда плотно не изучал JavaScript, могут быть различные стилистические и прочие недостатки в коде, возможно тот же парсинг даты-времени можно сделать красивее и элегантнее.

Открыт к конструктивной критике, буду рад, если укажете на недостатки, посоветуете что можно улучшить, а что изменить в статье.
Что читать дальше

Apps Script Overview
Apps Script: Calendar
Apps Script: Spreadsheet


javascript
google apps

Google Apps Script — О редакторе сценариев.
Главная » Блог » Google Apps Script — О редакторе сценариев.
Великие дела надо совершать не раздумывая, чтобы мысль об опасности не ослабляла отвагу и быстроту.

Гай Юлий Цезарь Древнеримский государственный и политический деятель, полководец, писатель…
Мудрый был! Не поспоришь! До сих пор рассказывают, что он с легкостью выполнял несколько дел одновременно. В смысле, он мог не только с книжкой в руках суп хлебать, нет, тут все серьезно. Гай Юлий был способен спокойно надиктовывать двум-трем писцам послания государственной важности (на секундочку!), при этом находясь верхом на лошади, на марше, а в те времена не использовали стремян и удержаться в седле было совсем непросто. Неплохо, правда? Если б каждый так мог… Но не спешите вешать нос, у Юлия не было Google Apps Script.
Постановка задачи и инфа к размышлению

Более-менее ознакомиться с функционалом редактора скриптов от гугл.
И попутно узнать и научиться чему-нибудь новому, что в будущем пригодиться.

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

Автономные сценарии — Standalone Scripts.
Скрипты, связанные с контейнером — Container-bound Scripts.
Веб-приложения — Web Apps

Автономные сценарии
Это отдельные файлы GAS, несвязанные с другими файлами приложений G Suite. Они отображаются системой и их без труда можно открыть прямо с Google Диска.
Конечно, при необходимости скрипт реально развернуть как веб-приложение или опубликовать его как add-on (при создании которых, кстати, google рекомендует как раз использование автономных скриптов).
Скрипты, связанные с контейнером
К примеру, есть у вас файл Таблицы или того же Документа, или Формы, или Слайдов, или Google Сайт, так вот, связанный скрипт прикручен к нему и работает как бы в его обеспечении. Файл к которому привязан скрипт называют родительским или контейнером.Такой сценарий не найти на Диске Google.
Редактировать его возможно через открытый файл-контейнер по пути Инструменты > Редактор скриптов.

Довольно продолжительное время это был единственный способ.
Однако не так давно, Google породил менеджер скиптов ( script.google.com ), который позволяет запускать любой доступный проект и вносить необходимые правки.
Данный тип сценариев позволяет существенно расширить функционал базовых приложений. Главным образом с помощью добавления самодельных менюшек, диалоговых окошек и боковых панелей.
К связанным скриптам относят и пользовательские функции. Я думаю здесь как раз все понятно. Не нашли нужной формулы среди стандартных в Таблицах Google? Не беда! Напишем свою, собственную, которая будет работать по тому же принципу. Вызывать ее надо прямо в ячейке =myFunctionName() в точности как и стандартную.
Говоря о связанных скриптах нельзя не упомянуть надстройки (дополнения) — Add-ons for G Suite. Любой скрипт, будь он связанный или автономный можно опубликовать как дополнение. Так сказать, явить его миру. После чего скрипт станет доступен для установки всем желающим. Зайдя в хранилище аддонов гугл, вы обнаружите великое множество таких сценариев.


И наконец последний тип.
Веб-приложения
Любой скрипт можно развернуть как веб приложение. Главное чтобы скрипт содержал функцию doGet(e) или doPost(e). Проще простого. Фактически можно создать веб-сервер с помощью одной строки (Шутка!)
function doGet()
Веб-приложения открывают широчайшие возможности.
Если взяться перечислять способы применения данного типа программ, то на это может уйти весь день.
Лучше привести хороший пример. Перейдя по ссылке вы попадете на стартовую страницу очень любопытного проекта. Он еще пока в разработке, доступна только б-версия, но уже невозможно не оценить безграничный потенциал этого приложения.
Данный проект, Web-приложение от stomaks — это посредник между пользователем и Google Apps Script. Приложение дает шанс в полной мере насладиться возможностями продвинутой платформы от Google.
Среди доступных программ-модулей приложения уже доступны такие как:

Ведение домашней бухгалтерии (Google +)
Боты для социальных сетей Facebook, Telegram, LinkedIn, Twitter, Pinterest и др.
Автоматическая e-mail рассылка, с поддержкой отчетов
Парсер, синтаксический анализатор (Google +)
и другие.

Этот список не закончен. Проект на начальном этапе развития — это заготовка и добавить туда можно все что угодно. Хоть прямо сейчас свяжитесь с автором и предложите свой модуль или функцию.
Тема веб-приложений заслуживает отдельного внимания и мы к ней обязательно вернемся в последующих статьях.
Бесспорно, что каждый тип требует гораздо более подробных разъяснений и примеров. С другой стороны не переводить же все гайды* подряд…
(* от. англ. guide — руководство, мануал, справочник)
По ходу углубления знаний и навыков работы с этой гугловской платформой многие вопросы отпадут сами собой.
А поэтому давайте-ка приступим к практике.
Таблицы и редактор скриптов
По ходу изучения возможностей редактора предлагаю сделать что-нибудь полезное.
К примеру, научимся находить таблицу на Google Диске, доставать из нее значения и выводить их в журнал (по мере изучения GAS с полученными данными будем совершать более сложные манипуляции, а пока так…).
Предположим, что на вашем диске есть такая таблица (если нет, то надо бы ее создать или кликни сюда чтобы скопировать ее к себе на Google Диск) :
Самый простой способ получить доступ к содержимому таблицы — это знать ее уникальный идентификатор (ID).
Как получить id таблицы?
Можно, его просто посмотреть, он указывается всегда в одном и том же месте в адресной строке https://…/d/ /edit 

(  и есть id таблицы, стоит отметить что принцип получения id документа сохраняется и для других сервисах гугл ).
можно его получить и с помощью скрипта.
/* * Функция getSpreadsheetID() возвращает идентификатор текущей таблицы * * @return spreadsheet_id - Идентификатор (id) текущей таблицы */ function getSpreadsheetID ()
Данный код работает только в скрипте связанным с таблицей.
И возвращает id-шник таблицы с которой связан.
SpreadsheetApp.getActive() возвращает ссылку на текущую таблицу, а затем уже получаем нужный нам идентификатор директивой — getId().
Или другими словами.
Cлужба SpreadsheetApp позволяет скриптам создавать, получать доступ и изменять файлы Google Таблиц.
Функция getActive() возвращает текущую активную электронную таблицу или null, если ее нет.
А функция getId() возвращает уникальный идентификатор (id) для этой таблицы..
Собственно, надо бы сказать и пару слов о редакторе….
Как вы уже убедились ранее, наш сценарий заботливо подсвечивается различными цветами (это уже общепринятая норма, не то что раньше…), тем самым делая код значительно более читабельным, кроме того, наличествуют и подсказки к коду, которые существенно ускоряют процесс написания скрипта.
Только мы начнем вводить имя класса или какой-нить функции, как нам тут же предлагают его закончить…

Правда, надо не забыть нажать CTRL + Пробел. Кстати, табуляцию и отступы редактор тоже помогает расставлять, конечно, по мере своих сил.
Вернемся к коду, стало быть таблица у нас есть, ее id мы получили, так давайте получим и все остальное…
Пример 1. Как получить данные из активной таблицы активного листа?
Код ниже позволяет получить все данные из активной таблицы, активного листа.
Обратите внимание что это пример для скрипта, связанного с таблицей.
/* * Функция getValuesByActiveSheet() получает и возвращает все данные из активной * таблицы активного листа. * * @return sheet_values - Данные в виде двумерного массива */ function getValuesByActiveSheet ()
Строки 1-6. Уже знакомая нам конструкция комментария /* */. Кстати говоря, такой формат оформления комментария общепринят для описания к функций в языке JavaScript.
В строке 7 тоже знакомая нам директива function, которая создает новую функцию с названием getValuesByActiveSheet. Параметров в функцию не передаем, а значит в круглых скобках ничего не указываем () ну и  что значит что следующий код, это код данной функции вплоть до строки 15, где >, который символизирует о конце функции.

Строки 8-10. Нас встречает директива var что значит начало объявления переменных (и это мы тоже уже знаем).
С базовыми вещами разобрались теперь по сути кода.

Переменная spreadsheet_link в которую сразу же заносим ссылку на активную таблицу следующим кодом SpreadsheetApp.getActive(),
В переменную sheet_link заносим ссылку на активный лист getActiveSheet(),
и в переменную sheet_values занесем все данные с активного листа, getDataRange() так получаем ссылку на весь диапазон данных, а после берем значения из этого диапазона функцией getValues().

В строке 12 отправляем данные в журнал (это не обязательно).
Ну и в строке 14 возвращаем данные.
Допустим, вы захотите, снова получить эти данные. Чтобы каждый раз заново не писать код, вы можите вызывать уже раз написанную функцию (вызывать функцию можно сколько угодно раз и в любом, почти, месте вашего кода).
Старайтесь разбивать ваш код на множество мелких и универсальных функций.
Это делает разработку более гибкой и комфортной.
Так можно вызвать любую функцию:
// . ваш код getValuesByActiveSheet(); // Вызываем функцию с именем getValuesByActiveSheet // ваш код .
А если функция возвращает данные, как наша, мы эти данные можем записать в переменную так:
// . ваш код var data = getValuesByActiveSheet(); // Вызываем функцию getValuesByActiveSheet, а результат ее работы заносим в переменную data // ваш код .
Результат работы скрипта (журнал):

Код выше работает в сценариях связанных с таблицами. А следующий код может работать как в автономных скриптах, так и в связанных с контейнером.
Пример 2. Как получить все данные из листа зная id таблицы?
Код ниже позволяет получить все данные из листа «Июнь» зная id таблицы.
/* * Функция getValuesBySheet() получает и возвращает все данные из таблицы * по ее id и листа с именем "Июнь". * * @return sheet_values - Данные в виде двумерного массива */ function getValuesByActiveSheet ()
Разберем подробнее код выше, при этом пропустим описание аналогичных частей кода.
В строке 9 мы используем функцию openById(), которая получает ссылку на таблицу по ее id. При этом, как вы уже могли заметить, вызывая функцию, в круглых скобках, мы передаем id таблицы с которой хотим работать.
А в строке 12 мы используем функцию getSheetByName(), которая получает ссылку на лист по его имени, соответственно передав имя листа в функцию.
Результат работы скрипта такой же, как и в примере 1.
Пример 3. Как получить данные только из указанного диапазона?
Код ниже демонстрирует три способа получения данные только из указанного диапазона.
/* * Функция getValuesByRanges() получает данные из указанных диапазов. * * @return false - Ничего не возвращаем */ function getValuesByRanges ()
В предыдущих примерах мы брали данные из диапазона getDataRange(), который охватывал все ячейки с данными.
А в этом примере мы явно укажем диапазон ячеек с которыми хотим работать. И делается это функцией getRange().
Данная функция может принимать разный набор параметров (входящих данных, диапазонов).
Первый вариант, строка 11. «A3:E» привычный вариант указания диапазона, при условии что у вас есть опыт написания формул для таблицы. Данный способ возвращает диапазон, указанный в нотации A1 или нотации R1C1.
Второй вариант, строка 12. номер строки, номер столбца возвращает диапазон с верхней левой ячейки в заданных координатах.
Третий вариант, строка 13. номер строки, номер столбца, количество строк, количество столбцов возвращает диапазон с верхней левой ячейки в заданных координатах с заданным количеством строк и столбцов.
Есть еще один вариант, который мы не приминили в коде, это номер строки, номер столбца, количество строк, такой способ возвращает диапазон с верхней левой ячейки в заданных координатах и с заданным количеством строк. При этом берет только один столбец.
Также, как вы могли заметить, при получении данных, в одном случае мы используем getValue(), а в другом getValues(). Разница в том что:

getValue() — возвращает значение ячейки верхнего левого диапазона. Значение может быть типа Number, Boolean, Date или String в зависимости от значения ячейки. Пустые ячейки возвращают пустую строку.
getValues() — Возвращает прямоугольную сетку значений для этого диапазона.Возвращает двумерный массив значений, индексированный по строке, а затем по столбцу. Значения могут иметь тип Number, Boolean, Date или String, в зависимости от значения ячейки. Пустые ячейки представлены пустой строкой в массиве. Помните, что, когда индекс диапазона начинается с 1, 1, массив JavaScript индексируется из [0] [0].

Подробнее о массивах и других типах данных, а также об обработке данных мы поговорим в следующих статьях.
Не забываем заглядывать сюда, где скрываются ответы практически на все вопросы.
Результат работы скрипта (журнал):

Как наклацаете код и приметесь его тестировать, вспомните о комментариях. А точнее, о такой замечательной вещи как CTRL + /. Таким макаром очень удобно и главное быстро временно отключать участки кода в процессе его отладки.
Logger.log( sheet_range1_values ); // Logger.log( sheet_range2_values );
История версий скрипта
Также придется к месту полезная штука, которая прячется по пути Файл > Смотреть историю версий.


В случае, когда в скрипт закрался bug размером с носорога или по каким-то причинам требуется откатить немного назад (а CTRL + Z уже не помогает) — этот инструмент просто незаменим.
А знаете, что в этом самое замечательное? Это то, что эти бэкапы делаются автоматически! Конечно можно это сделать и вручную, вот только кто об этом вспоминает? На мой взгляд, просто чудесная функция, в критическую минуту сбережет ваши нервы, а возможно и спасет вашу технику от полета в окно…
Вернемся к нашему сценарию.
Предположим, что код написан, также предположим, что вы не забыли как его запустить и как посмотреть в Журналах ( CTRL + Enter ).
Предположим даже, что вам все понятно… ��
Если это так, то вы можете смело себя поздравить. Вы совершили еще один уверенный шаг к освоению Google Apps Script.
Только представьте, сколько всего можно автоматизировать и оптимизировать когда под рукой такой инструмент. 
Конечно, еще многое предстоит узнать и попробовать, во многом придется разобраться, но это не беда.
Говорят, Гай Юлий Цезарь мог делать несколько дел одновременно…
Взять упорство плюс гугл аппс скрипт
и…что там несколько…
СОТНИ дел одновременно не хотите?
На этом пока все.
В следующей статье продолжим знакомство с редактором и пристальнее поглядим на работу с таблицами…
Итоги
А теперь, коротко, подведем итоги:

В этой статье мы узнали, что бывают три типа скриптов автономные скрипты, скрипты, связанные с контейнером и веб-приложения.
Узнали, что при помощи скриптов можно существенно расширить функционал базовых приложений. С помощью добавления самодельных менюшек, диалоговых окошек, боковых панелей, дополнений и пользовательских функций.
Научились получать id таблицы, да и не только таблиц, с помощью скрипта. И находить id в ссылке.
Разобрали некоторые возможности редактора.
Научились выводить в журнал нужные нам данные.
Выяснили, как можно откатить версию скрипта.
Написали несколько полезных скриптов.
Узнали, как можно получить данные из таблицы при помощи скрипта.

Полезные ссылки и источники

Официальная документация по всем методам класса Приложение для электронных таблиц (на английском).
Документация, тоже официальная, по всем методам класса Лист (на английском).
Ну и еще одна официальная документация по всем методам класса Диапазон (на английском).

Бонус (расширение для редактора скриптов)
Специально для вас, я разработал расширение, которое добавляет в редактор несколько полезных инструментов и делает интерфейс более удобным.
Подробнее о расширении смотри тут.
Как парсить данные с сайтов прямо в Google Таблицы

Чтобы извлечь данные лишь с нескольких десятков (пусть даже сотни-другой страниц), вам вполне хватит Google Таблиц. Давайте разберемся, какие для этого есть функции, на каких языках нужно писать запросы и как расширить функционал с помощью скриптов.
Примеры и шаблоны будут лежать в этой таблице. Чтобы что-то редактировать, заберите документ себе через «Файл» → «Создать копию». При запуске скриптов Google будет ворчать — нужно продолжать несмотря ни на что 😉
Важно! Для больших объемов данных этот способ не сработает: чтобы спарсить аккаунты соцсетей, сотни тысяч страниц какого-нибудь Авито или просто крупного сайта, потребуется платный софт или отличное знание Python.
Специализированные функции Google Таблиц для парсинга данных: IMPORTHTML, IMPORTFEED, IMPORTDATA и IMPORTXML
IMPORTHTML

Ссылку (с указанием протокола, например, https).
Запрос (только два варианта — «table» для таблиц и «list» для списков).
Индекс (порядковый номер элемента).

Например: =IMPORTHTML («https://moscowclinic.ru/price/diagnostika/mrt/golova»;"table";1)
В данном случае прайс-лист на странице оформлен с помощью тега «table» — это видно, если посмотреть исходный код. Поэтому все подтягивается нормально.

А вот на соседней странице этого же сайта, где общий прайс-лист, для верстки использовали тег «div». И IMPORTHTML с параметром «table» уже не справляется. А перебор «list» с разным индексом только вытягивает разные элементы меню.

Как видно из этого примера, на практике нужные данные не всегда лежат в таблицах и списках → IMPORTHTML редко позволяет решить задачу. Идем дальше.
IMPORTFEED
Импортирует ленту RSS или Atom в гугл-таблицу. Эта функция более интересная, особенно для тех, кто занимается контент-маркетингом.

Ссылка на фид.
Запрос — указываем, какие именно данные нужно загрузить.

(Например, можно прописать «items url» для выгрузки списка ссылок на новые статьи. Или «items title» — для их заголовков, соответственно. А с помощью запроса «items created» можно выгрузить даты публикаций.)

Заголовки — тут нужно указать TRUE (1) или FALSE (0). Если TRUE, то первой строкой будут заголовки столбцов фида: например, «URL» или «Title».
Число объектов — указываем цифру. К примеру, можно вывести в таблицу только последние десять материалов из фида.

Пример: =IMPORTFEED («https://www.unisender.com/ru/blog/feed/»;"items url";1;10)

Такая формула выводит ссылки на последние 10 статей, которые опубликовали в блоге Unisender. (В соседнем столбце с этих URL уже можно спарсить количество просмотров, чтобы оценить популярность — это рассмотрим позже, в разделе про IMPORTXML и xPath.)
IMPORTDATA
Импортирует данные в форматах CSV и TSV (с XLS или XLSX, к сожалению, не работает). Синтаксис простой, аргумент один — ссылка на файл.
Пример: =IMPORTDATA («https://rssexport.rbc.ru/publisher/main_main/motion-design.csv»)

В данном случае, видимо, какой-то фид РБК в формате CSV — так он выглядит при загрузке в таблицу.
Эта функция тоже на практике используется гораздо реже, чем IMPORTXML, которую разберем следующей.
IMPORTXML
Эта функция может импортировать в самых разных форматах (XML, HTML, CSV, TSV, RSS, ATOM XML). Кроме того, язык запросов xPath очень функциональный и гибкий. Он позволяет точно найти нужный элемент на странице и извлечь из него все необходимые данные (процесс чем-то напоминает ориентирование в файлах и папках на компьютере). Поэтому разберем ее подробней.
Гид по мощной IMPORTXML и языку xPath
Сама функция IMPORTXML работает со следующими параметрами:

Полный адрес веб-страницы — с указанием протокола, как и в других функциях Google Таблиц, которые начинаются с IMPORT. (Плюс, как и в других функциях, можно брать значения из ячеек.)
Сам запрос xPath — передается текстовой строкой.
Специальный код для указания языка и региона, но это необязательный параметр, по умолчанию Sheets возьмут настройки самого документа.

Простой пример: =IMPORTXML («https://vc.ru»;"//title")

Функция находит тег «title», добывает SEO-заголовок страницы. Другой пример с парсингом метатегов: =IMPORTXML («https://rb.ru/»;"//meta[@name='description']/@content")

Почему запрос стал сложнее? Если открыть исходный код, то хорошо видно — в отличие от «title», тегов «meta» много. Поэтому мы указываем дополнительное условие — чтобы аргумент «name» был равен «description». А после мы еще прописываем, что данные нужно взять из другого атрибута этого же тега — «content». А с «title» проще, потому что там нужные данные лежат прямо внутри этого тега.

Теперь вернемся к идее из раздела про IMPORTFEED — дополнить список последних статей данными по просмотрам. Чтобы правильно составить запрос xPath, нужно открыть исходный код одной из них. Видно, что цифры лежат в теге «div» с атрибутом «class», равным «views».

Кстати, прямо в инструментах разработчика Chrome можно нажать «Ctrl + F» и проверить, работает соответствующий запрос xPath или нет. Вводим //div[@class='views'] — найден новый элемент и в единственном числе. То, что нужно.

Вставляем этот запрос в формулу Google Spreadsheets — работает, но не очень красиво. Данные попадают на соседний столбец, все из-за лишних символов в теге.

Для таких случаев в xPath есть дополнительные функции. Если переписать запрос так — normalize-space (//div[@class='views']) — все работает и выглядит нормально.


Google Таблицы не позволяют пользоваться функциями вроде IMPORTXML бесконечно — есть определенные квоты.
Поэтому все начинает тормозить, если подобных формул будет много. Таким образом можно потерять и уже собранные данные, так как по умолчанию все формулы пересчитываются при любом изменении в таблице.
Как расширить возможности парсинга с помощью пользовательских функций Google Таблиц (Google Apps Script)
Есть два надежных способа расширить лимиты с помощью Google Apps Script.
GAS — это скриптовый язык на основе JavaScript, который позволяет создавать дополнительные функции, автоматизировать работу с продуктами Google: Таблицами, Документами, Презентациями, Формами.
Best practice IMPORTXML
Эксперты телеграм-канала «Google Таблицы» пошерили таблицу, которая позволяет увеличить стандартные возможности IMPORTXML.
Скрипт (запускается при клике на инонку ракеты) получает данные и сразу заменяет формулы на значения, что позволяет уменьшить количество запросов → стандартных квот хватит дольше.
К тому же прошлые данные сохраняются, так как функция просто создает новые строки с новыми результатами. Отличить старые данные от новых позволяют дата и время.

Чтобы что-то редактировать, нужно забрать документ себе через «Файл» → «Создать копию». При запуске скриптов Google будет ворчать, что скрипт непроверенный — нужно не поддаваться и продолжать 😉
Если не хочется связываться с Google Apps Script, можно действовать похожим образом и вручную. То есть протягивать формулу → получать результаты → сразу заменять формулы на значения. Функции не будут собирать много данных — квоты закончатся нескоро. Но ручной способ, конечно, требует больше времени и сил.
IMPORTREGEX (IMPORTXML «на стероидах» и с регулярными выражениями)
Один из пользователей Stack Exchange опубликовал замечательный скрипт, который использует метод UrlFetchApp — у него тоже есть лимиты, но они значительно превышают квоты обычных функций Google Таблиц вроде IMPORTXML.
Еще ключевое отличие в том, что эта пользовательская функция работает с регулярными выражениями, а не xPath. Иногда нужные данные проще спарсить через xPath, иногда через регулярки.
Пользовательская IMPORTREGEX принимает два аргумента: ссылку на страницу и само регулярное выражение в формате текстовой строки. Например: =IMPORTREGEX (A1;" \D*(\d*)")

Решаем ту же задачу, что и в разделе про IMPORTXML и xPath — получаем количество просмотров у статьи Unisender. Только запрос теперь выглядит совсем по-другому. Но принцип работы похожий: нужно покопаться в исходном коде, найти уникальный элемент и правильно спарсить эти данные.
Разберем подробнее этот же пример. Как мы помним, искомые цифры находятся внутри тега «div» с классом «views». Но по коду видно, что их разделяет еще много тегов и данных. С xPath нас это не волновало, а регулярные выражения работают иначе — по сути это шаблоны для поиска текста. И чтобы запрос не занял несколько страниц, нужно брать фрагмент кода поближе.

Непосредственно рядом с нужными данными есть закрывающие теги «g» и «svg». Если поискать в исходном коде этот кусок , получится только одно совпадение. Фрагмент уникальный — значит, подходит.

Слеш — один из служебных символов языка. Чтобы находить такой символ в тексте, нужно использовать экранирование с помощью обратного слеша. Поэтому все начинается с конструкции  .
В коде видно, что цифры идут не сразу после закрывающего тега «svg», а там еще пробелы и перенос строки. То есть нам нужно проигнорировать вот эти пробелы и энтеры, а взять только цифры, которые идут после. Это можно решить благодаря так называемым предопределенным классам. /D ищет любые символы, которые не являются цифрами, а /d — только цифры соответственно. Дальше с помощью * показываем, что такой символ будет не один, а скобками — группу символов, которую как раз нужно будет спарсить. Так и получается  \D*(\d*)
Чтобы IMPORTREGEX действовала в вашей таблице, можно скопировать наш шаблон и разрешить запускать скрипты. Или:
1. Открыть свой файл, затем выбрать в главном меню «Расширения → Apps Script».
2. Вставить следующий код:
function importRegex (url, regexInput) var output = ''; 
var fetchedUrl = UrlFetchApp. fetch (url, ); 
if (fetchedUrl) var html = fetchedUrl. getContentText (); 
if (html.length && regexInput. length) output = html. match (new RegExp (regexInput, 'i'))[1]; 
>
>
// Grace period to not overload
Utilities.sleep (1000);
return unescapeHTML (output);
>
function unescapeHTML (str) return str. replace (/\&([^;]+);/g, function (entity, entityCode) var match;
if (entityCode in htmlEntities) return htmlEntities[entityCode]; 
> else if (match = entityCode. match (/^#x ([\da-fA-F]+)$/)) return String. fromCharCode (parseInt (match[1], 16)); 
> else if (match = entityCode. match (/^#(\d+)$/)) return String. fromCharCode (~~match[1]); 
> else return entity; 
> 
>);
>;
 
3. Нажать на иконку «Сохранить проект».
4. Теперь в таблицах можно вызывать IMPORTREGEX как и любые другие стандартные функции.
Якоря:
^EdTech — поиск строки, которая начинается с «EdTech».
продаж.$ — ищем текстовый фрагмент, который заканчивается на «продаж.».
Квантификаторы:
o+ — если находится один или более символов «o».
10* — соответствует тексту, где после 1 идет 0 или более символов «0».
0 — ищем фрагмент, где подряд идут 6 нулей или больше.
Символьные классы:
\d — любая цифра.
\D — любой символ, который не является цифрой.
\s — любой пробел, табуляция или прерывание строки.
[А-Яа-я] — любой символ кириллического алфавита без учета регистра.
Операторы:
Телеграм[у|е] — находит строки «Телеграму» и «Телеграме».
\$\d+ — берем только знаки доллара и цифры после них (знак доллара нужно экранировать обратным слешем, чтобы найти в тексте, потому что это один из операторов).
 \D*(\d*) — ищем совпадения с тегами и символами, кроме цифр, но берем только цифры (обозначаем нужную группу скобками).

Для углубленного изучения языка:
Бесплатный интерактивный курс на Scrimba
regex101.com — мощный онлайн-сервис для обучения, тренировки и отладки чужих выражений.