Основы скрипта приложений с Google Sheets # 2: электронные таблицы, листы и диапазоны

1. Введение

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

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

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

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

Это вторая лабораторная работа в плейлисте Fundamentals of Apps Script с Google Sheets. Перед началом обязательно выполните первую кодлабу: Макросы и пользовательские функции .

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

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

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

2. Знакомство со службой электронных таблиц

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

Класс SpreadsheetApp

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

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

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

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

f00cc1a9eb606f77.png

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

39dbb10f83e3082.png

Таким образом, класс Spreadsheet работает с набором листов и определяет файл Google Sheets на 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 Sheets щелкните лист 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 Sheets дубликат листа создается и переименовывается при запуске функции:

91295f42354f62e7.gif

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

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

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

  1. В Google Sheets щелкните лист 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 Sheets дубликат листа создается, переименовывается, активируется и форматируется:

2e57c917ab157dad.gif

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

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

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

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

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

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

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

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

  1. В Google Sheets щелкните лист 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». Это одна ячейка, а не столбец. При перемещении данных вам не нужно сопоставлять размеры с целевым и конечным диапазонами. Сценарий приложений просто выравнивает первую ячейку каждого из них.

Диапазоны сортировки

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

  1. В Google Sheets щелкните лист 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 .

Что дальше

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

Следующую лабораторию кода ищите в разделе Работа с данными .