Основы скрипта приложений с Google Таблицами №2: Электронные таблицы, таблицы и диапазоны

1. Введение

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

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

  • Как электронные таблицы, листы и диапазоны представлены в Apps Script.
  • Как получить доступ, создать и переименовать активную (открытую) электронную таблицу с помощью SpreadsheetApp и класса Spreadsheet .
  • Как изменить имя листа и ориентацию столбцов/строк диапазона с помощью класса Sheet .
  • Как указать, активировать, переместить и сортировать группу ячеек или диапазон данных с помощью класса Range .

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

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

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

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

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

2. Введение в сервис электронных таблиц

Четыре класса составляют основу сервиса электронных таблиц: SpreadsheetApp , Spreadsheet , Sheet и Range . В этом разделе описываются эти классы и их назначение.

Класс SpreadsheetApp

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

Электронные таблицы, листы и их классы

В терминологии Таблиц, электронная таблица — это файл Google Таблиц (хранящийся на Google Диске), содержащий данные, упорядоченные по строкам и столбцам. Электронную таблицу иногда называют «Google Таблицей», так же, как документ называют «Google Документом».

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

f00cc1a9eb606f77.png

Лист** представляет собой отдельную страницу электронной таблицы, иногда называемую «вкладкой». Каждая электронная таблица может содержать один или несколько листов. Вы можете использовать Sheet ** класс для доступа и изменения данных и настроек на уровне листа, таких как перемещение строк или столбцов данных.

39dbb10f83e3082.png

Подводя итог, можно сказать, что класс Spreadsheet работает с коллекцией листов и определяет файл Google Таблиц в Google Диске. Класс Sheet работает с отдельными листами в электронной таблице.

Класс Range

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

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

3. Настройка

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

5376f721894b10d9.png

Копия примера таблицы для вашего использования находится в папке Google Диска и называется «Копия таблицы без названия». Используйте эту таблицу для выполнения упражнений этой лабораторной работы.

Напоминаем, что вы можете открыть редактор скриптов из Google Таблиц, нажав «Расширения» > «Скрипт приложений» .

При первом открытии проекта Apps Script в редакторе скриптов редактор скриптов создает для вас как проект скрипта, так и файл скрипта.

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

4. Доступ к электронным таблицам и их изменение

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

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

Переименовать активную электронную таблицу

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

  1. В редакторе скриптов замените блок кода myFunction() по умолчанию следующим кодом: 
function renameSpreadsheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  mySS.rename("2017 Avocado Prices in Portland, Seattle");
}
  1. Чтобы сохранить ваш сценарий, нажмите «Сохранить». сохранять .
  2. Чтобы переименовать свой проект Apps Script, нажмите «Проект без названия », введите «Цены на авокадо» в качестве нового имени проекта и нажмите «Переименовать» .
  3. Чтобы запустить скрипт, выберите renameSpreadsheet из списка функций и нажмите кнопку Запустить .
  4. Авторизуйте макрос, следуя инструкциям на экране. Если появится сообщение «Это приложение не проверено», нажмите «Дополнительно» и выберите «Перейти к ценам на авокадо (небезопасно)» . На следующем экране нажмите « Разрешить» .

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

226c7bc3c2fbf33e.png

Давайте посмотрим на введённый вами код. Метод getActiveSpreadsheet() возвращает объект, представляющий активную электронную таблицу, то есть копию созданной вами таблицы упражнений. Этот объект таблицы хранится в переменной mySS . Вызов метода rename(newName) в mySS изменяет имя файла таблицы в Google Диске на «Цены на авокадо в Портленде и Сиэтле на 2017 год».

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

Дублировать активный лист

В вашей текущей таблице только один лист. Вы можете вызвать метод Spreadsheet.duplicateActiveSheet() , чтобы создать копию листа:

  1. Добавьте следующую новую функцию под функцией renameSpreadsheet() которая уже есть в вашем проекте скрипта: 
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}
  1. Сохраните свой проект сценария.
  2. Чтобы запустить скрипт, выберите duplicateAndOrganizeActiveSheet из списка функций и нажмите кнопку Запустить .

Вернитесь в Таблицы, и вы увидите, что в вашу электронную таблицу добавлена новая вкладка листа «Копия оригинала листа_».

d24f9f4ae20bf7d4.gif

В этой новой функции метод duplicateActiveSheet() создаёт, активирует и возвращает дубликат листа в вашей электронной таблице. Результирующий лист сохраняется в duplicateSheet , но код пока не выполняет никаких действий с этой переменной.

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

5. Отформатируйте лист с помощью класса Sheet.

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

Изменить имя листа

Переименование листов так же просто, как переименование электронной таблицы в renameSpreadsheet() . Требуется всего один вызов метода.

  1. В Google Таблицах нажмите на лист Sheet_Original , чтобы активировать его.
  2. В Apps Script измените функцию duplicateAndOrganizeActiveSheet() так, чтобы она соответствовала следующему: 
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
}
  1. Сохраните и запустите функцию.

В Google Таблицах дубликат листа создается и переименовывается при запуске функции:

91295f42354f62e7.gif

В добавленном коде метод setName(name) изменяет имя duplicateSheet листа, используя getSheetID() для получения его уникального идентификатора. Оператор + добавляет идентификатор листа в конец строки "Sheet_" .

Изменить столбцы и строки листа

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

  1. В Google Таблицах нажмите на лист Sheet_Original , чтобы активировать его.
  2. В Apps Script измените функцию duplicateAndOrganizeActiveSheet() так, чтобы она соответствовала следующему: 
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);
}
  1. Сохраните и запустите функцию.

В Google Таблицах дубликат листа создается, переименовывается, активируется и форматируется:

2e57c917ab157dad.gif

Добавленный вами код использует autoResizeColumns(startColumn, numColumns) для изменения размера столбцов таблицы для удобства чтения. Метод setFrozenRows(rows) фиксирует заданное количество строк (в данном случае две), что позволяет сохранять строки заголовков видимыми при прокрутке таблицы.

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

6. Переупорядочивание данных с помощью класса Range

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

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

Диапазоны перемещения

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

Давайте обновим ваш метод duplicateAndOrganizeActiveSheet() , чтобы также переместить некоторые данные:

  1. В Google Таблицах нажмите на лист Sheet_Original , чтобы активировать его.
  2. В Apps Script измените функцию duplicateAndOrganizeActiveSheet() так, чтобы она соответствовала следующему: 
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));
}
  1. Сохраните и запустите функцию.

При запуске этой функции создаётся, активируется и форматируется дубликат листа. Содержимое столбца F перемещается в столбец C:

10ea483aec52457e.gif

Новый код использует метод getRange(a1Notation) для определения диапазона данных для перемещения. Передавая нотацию A1 "F2:F" в качестве параметра метода, вы указываете столбец F (исключая F1). Если указанный диапазон существует, метод getRange(a1Notation) возвращает его экземпляр Range . Для удобства использования код сохраняет этот экземпляр в переменной myRange .

После определения диапазона метод moveTo(target) принимает содержимое myRange (как значения, так и форматирование) и перемещает его. Место назначения (столбец C) указывается с помощью обозначения A1 "C2". Это отдельная ячейка, а не столбец. При перемещении данных не требуется приводить размеры в соответствие с целевым и конечным диапазонами. Apps Script просто выравнивает первую ячейку каждого из них.

Сортировать диапазоны

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

  1. В Google Таблицах нажмите на лист Sheet_Original , чтобы активировать его.
  2. В Apps Script измените функцию duplicateAndOrganizeActiveSheet() так, чтобы она соответствовала следующему: 
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));

  // Sort all the data using column C (Price information).
  myRange = duplicateSheet.getRange("A3:D55");
  myRange.sort(3);
}
  1. Сохраните и запустите функцию.

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

a6cc9710245fae8d.png

В новом коде метод getRange(a1Notation) задаёт новый диапазон, охватывающий A3:D55 (вся таблица, за исключением заголовков столбцов). Затем код вызывает метод sort(sortSpecObj) для сортировки таблицы. Здесь параметр sortSpecObj — это номер столбца, по которому нужно сортировать. Метод сортирует диапазон таким образом, чтобы значения указанных столбцов располагались от наименьшего к наибольшему (по возрастанию). Метод sort(sortSpecObj) может выполнять более сложную сортировку, но здесь он не нужен. Вы можете ознакомиться со всеми различными способами вызова диапазонов сортировки в справочной документации метода .

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

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

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

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

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

Да Нет

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

  • Как электронные таблицы, листы и диапазоны представлены в Apps Script.
  • Некоторые основные применения классов SpreadsheetApp , Spreadsheet , Sheet и Range .

Что дальше?

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

Следующую лабораторную работу можно найти на странице Работа с данными .