Как написать скрипт в гугл таблицах
Перейти к содержимому

Как написать скрипт в гугл таблицах

  • автор:

Как написать скрипт в гугл таблицах

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

Последнее обновление: 2021-01-22

Что такое Apps Script?

Apps Script — это платформа для быстрой разработки приложений, которая дает вам возможность автоматизировать, настраивать и расширять возможности Google Workspace. С помощью Apps Script вы можете автоматизировать и упростить обременительную или сложную работу в Google Workspace, сэкономив вам и вашей команде время и силы.

Возможности Apps Script включают следующее:

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

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

Сервис Spreadsheet Service

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

  • Создавать или изменять электронные таблицы.
  • Читать и обновлять данных ячеек, формул и форматирования.
  • Создавать собственные кнопки и меню.
  • Импортировать и экспортировать данные из других приложений Google или сторонних источников.
  • Регулировать совместное использование и изменять контроль доступа к таблицам
  • . и многое другое.

Что вы узнаете из этого сборника

Этот сборник лаб охватывает все темы, которые вам понадобятся, чтобы начать использовать Apps Script с Google Sheets:

  • Макросы и пользовательские функции
  • Работа с книгами, листами и диапазонами
  • Работа с данными
  • Форматирование данных
  • Диаграммы и представление данных на слайдах

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

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

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

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

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

Ниже показано, какие концепции и требования включает в себя эта лаба.

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

  • Как создать скрипт для Google Таблиц.
  • Как пользоваться редактором Apps Script.
  • Как создавать и обновлять макросы.
  • Как создать свою первую пользовательскую функцию для Таблиц.

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

  • Базовое знакомство с JavaScript
  • Базовое знакомство с Google Таблицами
  • Умение читать А1-нотацию табличных процессоров

Примечание. Apps Script основан на JavaScript. Обучение написанию кода JavaScript выходит за рамки данного урока. Текущая цель — научить вас комфортно использовать среду разработки Apps Script и дать вам представление о типах приложений, которые вы можете создавать с ее помощью. Вы можете получить поддержку в вопросах программирования на ресурсах, перечисленных здесь contributor.pw/contacts

Вы закончили знакомство. Перейдите в следующий раздел, чтобы начать работу с макросами!

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

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

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

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

Скопировать Таблицу данных

Копия примера Таблицы, которую вы можете использовать, будет размещена в папке Google Диска и называться «Копия Топ-10 самых кассовых фильмов (2020)».

Создание макроса

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

  1. Щелкните ячейку A1, чтобы навести курсор на эту строку. Это ваша строка заголовка.
  2. В меню выберите Меню >Инструменты >Макросы >Записать макрос.

Как только вы начнете запись, Google Таблица начнет запоминать действия, которые вы выполняете: выделение ячеек, добавление данных, переключение на разные листы, форматирование и т.д. Эти действия позже становятся «сценарием», который повторяется после сохранения и активации макроса.

  1. В окне записи макроса («макрос-бокс») выберите «Использовать относительные ссылки«.

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

Эта лаба предполагает использовать относительные ссылки для ваших макросов.

  1. Измените цвет заливки выделенной строки с белого на темно-пурпурный (3).

  1. Измените цвет текста выделенной строки с черного на белый.

  1. Сделайте текст жирным шрифтом, нажав [Ctrl]+[B] (или [Cmd]+[B] на Mac).
  2. Выберите Меню >Вид >Закрепить >1 строку, чтобы зафиксировать верхнюю строку.

  1. Нажмите кнопку «Сохранить» в окне записи макроса внизу экрана. Затем в новом диалоговом окне вас попросят назвать макрос; дайте ему имя «Заголовок» и нажмите «Сохранить«.

Используя пользовательский интерфейс Таблиц, вы создали макрос, специально предназначенный для форматирования заголовков!

Активация вашего макроса

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

  1. Щелкните слева от вкладок листов или Меню >Вставка >Новый лист, чтобы создать новый лист.
  2. На новом листе добавьте какой-нибудь текст в A1:C2 . Не стесняйтесь следовать приведенным ниже примерам:

  1. Выделите первую строку.

  1. Выберите Меню >Инструменты >Макросы >Заголовок, чтобы применить макрос к выбранной области.

  1. Авторизуйте макрос, следуя инструкциям на экране.

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

  1. Повторите шаг 4, чтобы снова запустить макрос (авторизация останавливает первое выполнение).

Вы узнали, как применять макросы в Таблицах. Поздравляю! Ваша таблица должна выглядеть так:

Макросы позволяют эффективно создавать электронные таблицы, и в следующей части этой лабы вы узнаете, как сделать ваши макросы еще более мощными! Секрет в том, что когда вы записываете макрос, на самом деле вы пишете код Apps Script. За кулисами Таблицы создают код, который соответствует макрокомандам за вас. А в следующем разделе вы узнаете, как изменить этот код напрямую с помощью редактора Apps Script в браузере.

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

Взгляд на редактор

Теперь, когда вы создали макрос, вы можете посмотреть его код. Вы можете просмотреть свой макрос-скрипт, выбрав Меню > Инструменты > Редактор скриптов, чтобы открыть редактор кода в браузере для Apps Script.

Примечание. Макросы и сценарии, которые вы создаете в этой лабе, прикреплены к файлу Таблицы, с которым они работают, и могут быть доступны в любое время из пункта Меню > Инструменты > Редактор скриптов. Скрипты, которые прикреплены к Google Таблице, называются привязанными к контейнеру.

Понимание Макросы.gs

Посмотрите текущий сценарий. Таблицы создали файл скриптов Макросы.gs, когда вы записали макрос «Заголовок«, заполнив его соответствующей функцией сценария приложений под названием «Заголовок«. Когда вы активируете макрос, Таблицы запускают эту функцию.

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

Первая строка — это аннотационный комментарий, влияющий на авторизацию:

/** @OnlyCurrentDoc */

Большинство скриптов перед запуском запрашивают у пользователя некоторый набор разрешений. Эти разрешения определяют, что пользователь разрешает делать сценарию. Если в проекте сценария присутствует комментарий @OnlyCurrentDoc , Apps Script запрашивает только разрешение на доступ и обновление текущей Таблицы. Без этого комментария Apps Script будет запрашивать разрешение на доступ и обновление всех Таблиц пользователя. Всегда полезно включать эту аннотацию, если вы планируете работать только с одним файлом Таблиц (Документов, Слайдов). Инструмент записи макросов автоматически добавляет этот комментарий.

Чтобы начать понимать, как Apps Script представляет инструкции вашего макроса, вы можете взглянуть на функцию:

function myFunction() < var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate(); spreadsheet.getActiveRangeList().setBackground('#4c1130') .setFontColor('#ffffff') .setFontWeight('bold'); spreadsheet.getActiveSheet().setFrozenRows(1); >;

Этот код запускается, когда вы активируете макрос «Заголовок«. После function отметка myFunction() определяет имя функции и ее параметры. Помните, что myFunction() не требует параметров, поскольку макрос-функции в Apps Script вызываются напрямую. В фигурных скобках всегда заключено тело функции Apps Script.

Следующие лабы из этого сборника объясняют классы и концепции, участвующие в создании макроса. Но пока вы можете посмотреть описания кода ниже, чтобы получить общее представление о его компонентах и ​​их роли в создании программы. Рассмотрим первую строку:

var spreadsheet = SpreadsheetApp.getActive();

Здесь getActive() возвращает объект, представляющий текущий активный файл Таблицы, и устанавливает его в новую переменную spreadsheet .

var sheet = spreadsheet.getActiveSheet(); sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate();

Этот код соответствует щелчку по первой строке Таблицы, чтобы выделить ее. Это называется активацией. Сначала код сохраняет текущий лист в переменной sheet , а потом получает всю первую строку с помощью метода getRange() , далее вызывает activate() для ее активации. Первая строка Таблицы указывается с использованием конкретных номеров строки и столбца. Вызов spreadsheet.getCurrentCell().getRow() возвращает номер текущей строки, а sheet.getMaxColumns() возвращает максимальное количество столбцов на листе.

spreadsheet.getActiveRangeList().setBackground('#4c1130') .setFontColor('#ffffff') .setFontWeight('bold');

Этот фрагмент кода становится более сложным. Чтобы эффективно вызывать методы с помощью переменной spreadsheet , код объединяется в три метода после getActiveRangeList() , чтобы предотвратить избыточный вызов getActiveRangeList() . По мере того, как вы все больше и больше будете писать код с помощью Apps Script, вы сможете лучше познакомиться с этим соглашением о вызове нескольких методов в одном классе (также известном как цепь вызовов). На данный момент достаточно прочитать следующие краткие объяснения каждого метода в этом блоке:

  • getActiveRangeList() возвращает список диапазонов RangeList , которые выделены в spreadsheet в текущий момент. В этом случае это просто первая строка, которую активировал наш код.
  • Оба метода setBackground(color) и setFontColor(color) изменяют атрибуты цвета ячеек в диапазоне.
  • setFontWeight(fontWeight) регулирует толщину шрифта для ячеек в диапазоне.

Наконец, последняя строка «замораживает» первую строку макроса:

spreadsheet.getActiveSheet().setFrozenRows(1);

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

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

Настройка макросов с помощью Apps Script

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

Изменение ячеек, на которые будет распространено воздействие

  1. В редакторе скриптов замените sheet.getMaxColumns() на 11 в строке 6. Это изменение изменяет диапазон ячеек в Таблице, на которые влияет макрос.
/** @OnlyCurrentDoc */ function myFunction() < var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, 11).activate(); /* sheet.getMaxColumns() заменено на 11 */ spreadsheet.getActiveRangeList().setBackground('#4c1130') .setFontColor('#ffffff') .setFontWeight('bold'); spreadsheet.getActiveSheet().setFrozenRows(1); >;
  1. Чтобы сохранить сценарий, в верхней части редактора щелкните «Сохранить проект» .
  2. Чтобы переименовать свой проект, вверху нажмите на заголовок «Проект без названия» (или «Записанные макросы (Копия Топ-10 самых кассовых фильмов (2020))», зависит от порядка записи макроса), введите «Макросы и пользовательские функции» в качестве имени нового проекта и выберите «Переименовать».
  3. В Таблице щелкните слева от вкладок листов или Меню >Вставка >Новый лист, чтобы создать новый лист.
  4. В редакторе сценариев в раскрывающемся списке функций выберите myFunction() и щелкните «Выполнить» .

На новом листе вы должны увидеть следующий результат:

Теперь, изменяя активный или целевой диапазон, ваш макрос влияет только на часть первой строки! Многие методы Apps Script принимают диапазон строку в А1-нотации в качестве параметра, чтобы указать, с какими ячейками следует оперировать.

Пришло время узнать о настройке цветов!

Изменение цветов в коде макроса

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

Эти первые несколько шагов касаются изменения цвета фона, который назначает макрос:

  1. В Таблице вернитесь к исходному листу (Лист1), содержащему данные.
  2. Щелкните первую строку, чтобы выделить ее.
  3. В редакторе сценариев замените #4c1130 на #afeeee в строке 6. Эти значения представляют разные цвета с использованием шестнадцатеричной нотации.
/** @OnlyCurrentDoc */ function myFunction() < var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, 11).activate(); /* sheet.getMaxColumns() заменено на 11 */ spreadsheet.getActiveRangeList().setBackground('#afeeee') /* #4c1130 заменено на #afeeee */ .setFontColor('#ffffff') .setFontWeight('bold'); spreadsheet.getActiveSheet().setFrozenRows(1); >;
  1. В Таблице щелкните слева от вкладок листов или Меню >Вставка >Новый лист, чтобы создать новый лист.
  2. В редакторе сценариев в раскрывающемся списке функций выберите myFunction() и щелкните «Выполнить» .

В Таблице фоновая заливка первых 11 столбцов в первой строке будут перекрашены в индивидуальный бирюзовый цвет, как показано здесь:

Переключив шестнадцатеричное значение цвета в параметрах setBackground(color) с #4c1130 (темно-пурпурный 3) на #afeeee (бледно-бирюзовый, параметр недоступный в меню цветов Таблиц по умолчанию), вы изменяете атрибут цвета фона для вашего макроса.

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

  1. В Таблицах щелкните первую строку, чтобы убедиться, что она все еще выделена.
  2. В редакторе сценариев замените #ffffff на #191970 в строке 8. Это заставит макрос установить цвет шрифта в темно-синий.
/** @OnlyCurrentDoc */ function myFunction() < var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getActiveSheet(); sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, 11).activate(); /* sheet.getMaxColumns() заменено на 11 */ spreadsheet.getActiveRangeList().setBackground('#afeeee') /* #4c1130 заменено на #afeeee */ .setFontColor('#191970') /* #ffffff заменено на #191970 */ .setFontWeight('bold'); spreadsheet.getActiveSheet().setFrozenRows(1); >;
  1. В Таблице щелкните слева от вкладок листов или Меню >Вставка >Новый лист, чтобы создать новый лист.
  2. В редакторе сценариев в раскрывающемся списке функций выберите myFunction() и щелкните «Выполнить» .

Вернитесь в Таблицу. Обратите внимание, что цвет текста в строке заголовка теперь темно-синий!

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

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

В этом разделе показано, как создать пользовательскую функцию в Apps Script, которая выполняет валютную конвертацию.

Создание нового файла сценария

Используя ту же таблицу и проект скриптов, что и в разделе выше с макросами, вы можете следовать инструкциям ниже, чтобы узнать, как создать новый сценарий (который в конечном итоге можно использовать для создания своей первой настраиваемой функции!):

  1. Чтобы создать новый файл Apps Script в текущем проекте, в левой части редактора рядом с полем «Файлы» нажмите «Добавить файл» >Скрипт.
  2. Назовите новый файл сценария «Пользовательские функции» (Apps Script автоматически добавляет расширение «.gs» к имени).

В редакторе появится новая вкладка с именем «Пользовательские функции.gs«.

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

Перевод долларов США в российские рубли

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

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

  1. В Таблице щелкните правой кнопкой мыши на столбец I .
  2. В появившемся меню нажмите Вставить справа: 1.

  1. Добавьте текст «Мировой прокат, рубли» в ячейку J1 .

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

  1. В Пользовательские функции.gs замените код пустой функции myFunction() на следующий:
/** * Конвертирует доллары в рубли. * * @param dollars Значение суммы в долларах. * @return rubles Конвертированное значение в рублях. * @customfunction */ function USDTORUB(dollars)

Это код, который конвертирует доллары в рубли. Ниже идет инструкция, как запустить пользовательскую функцию в вашей Таблице.

  1. Чтобы сохранить сценарий, в верхней части редактора щелкните «Сохранить проект» .
  2. На листе с данными выберите ячейку J2 .
  3. В поле функций введите =USDTORUB(I2)

Чтобы применить формулу к остальным ячейкам столбца:

  1. Переместите курсор в нижний правый угол ячейки J2 и выберите маленькую синий квадратик (ваш курсор должен трансформироваться в при наведении на синий квадратик в ).
  2. Щелкните и перетащите синий прямоугольник вниз, чтобы выделить J3:J11 .

В столбце J теперь указана конвертированная стоимость в рублях!

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

Анализ функции USDTORUB()

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

/** * Конвертирует доллары в рубли. * * @param dollars Значение суммы в долларах. * @return rubles Конвертированное значение в рублях. * @customfunction */

Подобные блоки комментариев часто используются в программировании, чтобы объяснить, что делают функции.

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

Apps Script использует JSDoc для аннотаций, чтобы помочь вам документировать и создавать подсказки автозаполнения для вашего кода. Вы можете прочитать ниже, как каждая аннотация, используемая в USDTORUB() , помогает в разработке Apps Script:

  • @param : вы можете использовать аннотацию @param для описания каждого параметра, передаваемого в функцию.
  • @return : вы можете использовать аннотацию @return , чтобы описать, что возвращает функция.
  • @customfunction : вы всегда должны добавлять @customfunction в комментарий к пользовательской функции. Эта аннотация уведомляет Таблицу о возможности автозаполнения вашей пользовательской функции, так же как Таблицы автоматически заполняют встроенные формулы, когда вы вводите их имена в ячейку, как показано ниже:

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

Далее сфокусируемся на коде функции USDTORUB() :

function USDTORUB(dollars)

Как упоминалось ранее, USDTORUB() принимает числовую переменную в долларах и возвращает это значение, преобразованное в рубли в числовой переменной rubles , умножая его на фиксированный обменный курс. Входной параметр — это значение, содержащееся в ячейке, которую вы указали при добавлении пользовательской функции в ячейку. В этом примере входные суммы в долларах поступают из столбца I . Выходное значение rubles помещается в ячейку функции (то есть в столбец J в этом примере).

Пользовательские функции могут работать с числовыми или строковыми значениями. Это будет показано далее.

Объединение строкового суффикса

Предположим, вы хотите, чтобы числовой вывод функции USDTORUB() включал суффикс рублей » руб. «. Вы можете сделать это с помощью Apps Script, используя оператор конкатенации » + «, как показано в следующем примере:

  1. В редакторе измените return rubles; в строке 10 на return rubles + ‘ руб.’ ;.

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

Оператор + добавляет строку » руб. » в конец значения, содержащегося в rubles . Теперь ваш код должен выглядеть так:

/** * Конвертирует доллары в рубли. * * @param dollars Значение суммы в долларах. * @return rubles Конвертирует переданное значение в рубли. * @customfunction */ function USDTORUB(dollars)

  1. Чтобы сохранить сценарий, в верхней части редактора щелкните «Сохранить проект» .

Значения в рублях теперь отображаются в колонке J :

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

Дополнительно: получение внешних данных

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

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

/** * Конвертирует доллары в рубли. * * @param dollars Значение суммы в долларах. * @return rubles Конвертирует переданное значение в рубли. * @customfunction */ function USDTORUB(dollars) < // Получает кэш, общий для всех пользователей скрипта. var cache = CacheService.getScriptCache(); // Доступ к ячейке памяти (rate.RUB) кеша скриптов. var rate = cache.get("rates.RUB"); // Если кэш отсутствует, // программа получает текущее значение RUB из API // и сохраняет его в кеше для дальнейшего использования. if (!rate) < var response = UrlFetchApp.fetch( "https://api.exchangeratesapi.io/latest?base=USD" ); var result = JSON.parse(response.getContentText()); rate = result.rates.RUB; cache.put("rates.RUB", rate); >// Конвертирует доллары в рубли по последнему курсу. var rubles = dollars * rate; // Возвращает значение в рублях с суффиксом. return rubles + " руб."; >

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

Рекомендации по пользовательским функциям

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

  • Не создавайте пользовательские функции, требующие авторизации пользователя. Создавайте функции для выполнения простых задач, таких как расчет выборки данных, преобразование текста и т.д. См. использование служб Apps Script.
  • Не называйте пользовательскую функцию именами встроенных формул, и не завершайте имя знаком подчеркивания. См. рекомендации по именованию.
  • Не передавайте переменные (тиковые) аргументы пользовательским функциям. В качестве аргументов пользовательским функциям можно передавать только детерминированные (фиксированные) значения. Передача переменных аргументов, таких как результат =RAND() или =NOW() , нарушит работу пользовательской функции. См. руководство по аргументам.
  • Не создавайте функции, выполнение которых занимает более 30 секунд. Если это займет больше времени, произойдет ошибка, поэтому код функции должен быть простым и ограниченным по объему. Лучше всего, чтобы вычисления, проводимые в пользовательских функциях, были как можно проще. См. рекомендации по возвращаемым значениям.

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

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

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

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

  • Основные концепции Apps Script.
  • Как ориентироваться в редакторе скриптов.
  • Как создавать и обновлять макросы и скрипты для Таблиц.
  • Как создавать собственные функции для Google Таблиц.

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

  • Apps Script: Платформа и одноименный язык программирования для быстрой разработки программ на основе JavaScript, которая позволяет быстро и легко расширять приложения Google Workspace и автоматизировать работу.
  • JSDoc: Язык разметки, используемый для написания пояснительных примечаний к исходным файлам JavaScript.
  • Script editor [Script editor]: Редактор кода для Apps Script, запускаемый в браузере.
  • Абсолютная ссылка [Absolute reference]: параметр записи макроса, который указывает, что записанные макрокоманды применяются к точным адресам ячеек, используемым в записи.
  • Авторизация [Authorization]: Процесс предоставления пользователем разрешений, позволяющих скрипту получать доступ к пользовательским данным или иным образом выполнять действия от имени пользователя.
  • Активный [Active] (статус): Указывает, что таблица, лист, диапазон или ячейка в настоящее время просматривается или выделены пользователем.
  • Диапазон [Range]: Группировка из одной или нескольких соседних ячеек Таблицы.
  • Лист [Sheet]: Одна из страниц Таблицы. Иногда используется для ссылки на файл Google Таблиц на Google Диске.
  • Макрос [Macro] (в данных лабах): Скрипт, полученный с помощью инструментов записи, которые перефразируют (или любым другим способом преобразуют) действия пользователя в программу. М. также, записанная серия действий в Google Таблицах, которые можно быстро повторить с помощью пункта меню или сочетания клавиш.
  • Относительная ссылка [Relative reference]: Параметр записи макроса, который указывает, что записанные макрокоманды применяются к ячейкам относительно текущего пользовательского выбора (активной ячейки).
  • Пользовательская функция [Custom functions]: Пользовательские формулы созданные в Apps Script, которые используются в Таблицах для простых операций.
  • Служба Spreadsheet [Spreadsheet Service]: Служба Apps Script, которая позволяет скриптам создавать файлы Google Sheets, получать к ним доступ и изменять их.
  • Скрипт, привязанный к контейнеру [Container bound script]: Любой скрипт, связанный с документом Google Workspace и созданный из него, например, Google Таблица или Google Документ.
  • Скрипт (в данных лабах): Сценарий, написанный на языке Google Apps Script или другом языке сценариев.
  • Сценарий (в данных лабах): Устойчивая (записанная или другим способом определенная) последовательность действий.
  • Таблица: Электронная таблица из приложений Google. Она же «Google Таблица«, «Google Sheet«.
  • таблица: любая информация, представленная в виде таблицы.
  • Цепь вызовов: Идиома программирования для вызова нескольких методов объекта, основанная на том факте, что каждый вызов метода возвращает сам объект.

Дополнительные сведения об Apps Script можно найти в документации.

Что дальше

Следующая лаба в этом сборнике представляет основные классы и терминологию службы Spreadsheet Service Apps Script. Эта служба позволяет вам достаточно полно контролировать значения и представления данных в Google Таблицах с помощью Apps Script.

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

Как запустить несколько скриптов в гугл таблицах?

Здравствуйте, есть два скрипта для гугл таблиц
1) Авто проставление даты, когда в другой ячейке появляется любое значение
2) Добавление выпадающего списка товаров в ячейку, когда в другой ячейке появляется определенная категория
По отдельности они работают, но когда пытаюсь их запустить вместе, то работает только второй скрипт. Пробовал в двух разных файлах прописывать скриты и в одном

Также пытался ограничить второй скрипт на один лист в 17 строчке(Иначе он работает на все листы), но скрипт просто перестает работать

В чем может быть проблема?

//Авто проставление даты function onEdit(e) < var str = e.range.getRow(); var stolb = e.range.getColumn(); if (stolb == 4 && e.source.getActiveSheet().getName() == "Order")< e.source.getActiveSheet().getRange(str,2).setValue(new Date()); >> //Зависимые выпадающие списки function onEdit() < //Попытка ограничить листы //if (ss.getActiveSheet().getName()==="Order")< let ss = SpreadsheetApp.getActiveSpreadsheet(); let sheet = ss.getSheetByName("Order"); let sheetDb = ss.getSheetByName("Range"); let ar = sheet.getActiveCell(); if(ar.getColumn()!==6)let valToFind = ar.getValue(); //Удаление записи при изменении товара //ar.offset(0, 2).clearContent().clearDataValidations(); let values = sheetDb.getRange(1,1,1,sheetDb.getLastColumn()).getValues(); let col = values[0].indexOf(valToFind)+1; console.log(col); let range = sheetDb.getRange(3, col, 30, 1); console.log(range); let validation = SpreadsheetApp.newDataValidation().requireValueInRange(range).build(); //shet.getRange(ar.getRow(), 8).setDataValidation(validation) ar.offset(0, 2).setDataValidation(validation); >
  • Вопрос задан более года назад
  • 607 просмотров

1 комментарий

Простой 1 комментарий

Как написать скрипт в гугл таблицах

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

Последнее обновление: 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.

Что дальше

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *