Google Apps Script: Как получить значения столбца?

Что такое Google Apps Script и его применение для Google Таблиц

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

Получение доступа к Google Таблице и листу

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

Основные объекты: Spreadsheet, Sheet, Range

В Apps Script для работы с таблицами используются три основных объекта:

• Spreadsheet: Представляет собой всю Google Таблицу.
• Sheet: Представляет собой отдельный лист в таблице.
• Range: Представляет собой диапазон ячеек, с которым вы хотите взаимодействовать. Это может быть одна ячейка, строка, столбец или блок ячеек.

Получение значений столбца: основные методы

Использование getRange() для выбора столбца

Для получения доступа к столбцу используется метод getRange() объекта Sheet. Существует несколько способов указать столбец, например, используя номер столбца или буквенное обозначение (A, B, C и т.д.).

/**
 * Получает столбец по номеру.
 * @param columnNumber Номер столбца (начиная с 1).
 * @return Объект Range, представляющий столбец.
 */
function getColumnByNumber(columnNumber) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const lastRow = sheet.getLastRow();
  return sheet.getRange(1, columnNumber, lastRow);
}

/**
 * Получает столбец по буквенному обозначению.
 * @param columnLetter Буквенное обозначение столбца (например, "A").
 * @return Объект Range, представляющий столбец.
 */
function getColumnByLetter(columnLetter) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const columnNumber = columnLetterToNumber(columnLetter);
  return sheet.getRange(1, columnNumber, lastRow);
}

/**
 * Преобразует буквенное обозначение столбца в число.
 * @param columnLetter Буквенное обозначение столбца (например, "A").
 * @return Номер столбца (начиная с 1).
 */
function columnLetterToNumber(columnLetter) {
  let columnNumber = 0;
  for (let i = 0; i < columnLetter.length; i++) {
    columnNumber = columnNumber * 26 + (columnLetter.charCodeAt(i) - 'A'.charCodeAt(0) + 1);
  }
  return columnNumber;
}

Метод getValues(): получение значений в виде двумерного массива

После получения объекта Range, представляющего столбец, метод getValues() возвращает все значения в этом столбце в виде двумерного массива. Каждая строка массива представляет собой строку в столбце, а каждый элемент в строке – значение ячейки. В случае одного столбца, каждая строка будет содержать один элемент.

Разбор полученного массива: извлечение значений столбца

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

/**
 * Получает значения столбца.
 * @param columnNumber Номер столбца (начиная с 1).
 * @return Массив значений столбца.
 */
function getColumnValues(columnNumber) {
  const columnRange = getColumnByNumber(columnNumber);
  const values = columnRange.getValues();
  const columnValues = [];

  for (let i = 0; i < values.length; i++) {
    columnValues.push(values[i][0]);
  }
  return columnValues;
}

Альтернативные подходы и оптимизация

Получение значений столбца с помощью getLastRow()

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

Оптимизация скрипта для больших таблиц: избежание лишних операций

При работе с большими таблицами важно оптимизировать скрипт, чтобы избежать лишних операций. Например, вместо многократного вызова getRange() в цикле, лучше один раз получить весь столбец и затем работать с полученным массивом.

Использование map() для преобразования массива

Метод map() позволяет элегантно преобразовать массив. В нашем случае, он может быть использован для извлечения значений из двумерного массива, возвращаемого getValues().

/**
 * Получает значения столбца с использованием map().
 * @param columnNumber Номер столбца (начиная с 1).
 * @return Массив значений столбца.
 */
function getColumnValuesWithMap(columnNumber) {
  const columnRange = getColumnByNumber(columnNumber);
  const values = columnRange.getValues();
  return values.map(row => row[0].toString());
}

Практические примеры и сценарии использования

Пример 1: Получение списка email адресов из столбца

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

function getEmailList() {
  const emailColumnNumber = 2; // Предположим, email адреса находятся во втором столбце.
  return getColumnValuesWithMap(emailColumnNumber);
}

Пример 2: Поиск дубликатов в столбце

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

function findDuplicates(columnNumber) {
  const values = getColumnValuesWithMap(columnNumber);
  const seen = new Set();
  const duplicates = [];

  for (const value of values) {
    if (seen.has(value)) {
      duplicates.push(value);
    } else {
      seen.add(value);
    }
  }
  return duplicates;
}

Пример 3: Фильтрация данных в столбце по условию

Например, отфильтровать строки, где значение в столбце больше определенного числа.

function filterValuesGreaterThan(columnNumber, threshold) {
  const values = getColumnValuesWithMap(columnNumber);
  return values.filter(value => Number(value) > threshold);
}

Обработка ошибок и отладка

Проверка наличия данных в столбце

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

Обработка пустых ячеек и ошибок типов данных

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

Использование логирования для отладки скрипта

Функция Logger.log() позволяет выводить отладочную информацию в журнал выполнения скрипта. Это полезно для отслеживания значений переменных и выявления ошибок.