Google Apps Script: копирование данных из одной таблицы в другую — полное руководство

  •   
  •   16766
  •   8 мин
  •   117

Введение в Google Apps Script и таблицы

Что такое Google Apps Script?

Google Apps Script — это облачный язык сценариев, разработанный Google для автоматизации задач в Google Workspace (ранее G Suite). Он позволяет расширять функциональность Google Sheets, Docs, Slides, Forms и других приложений. Apps Script основан на JavaScript и предоставляет доступ к различным сервисам Google, включая Sheets API, что делает его мощным инструментом для работы с данными.

Основы работы с Google Sheets API через Apps Script

Для взаимодействия с Google Sheets через Apps Script используется Sheets API. Этот API предоставляет объекты и методы для чтения, записи и изменения данных в таблицах. Основные объекты, с которыми предстоит работать, включают Spreadsheet, Sheet и Range. Spreadsheet представляет собой всю таблицу, Sheet — отдельный лист внутри таблицы, а Range — диапазон ячеек.

Настройка окружения для работы с таблицами

Чтобы начать работу с Apps Script, откройте Google Sheets и выберите «Инструменты» > «Редактор скриптов». Откроется редактор кода Apps Script, где можно писать и запускать скрипты. Убедитесь, что у вас есть доступ к таблицам, с которыми вы планируете работать. Для доступа к данным Apps Script запросит соответствующие разрешения.

Основные методы для копирования данных

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

Для получения данных из исходной таблицы используются методы объекта Range: getValues() и getDisplayValues(). getValues() возвращает данные в исходном формате (числа, даты, строки), а getDisplayValues() возвращает данные в том виде, в котором они отображаются в таблице (с учетом форматирования).

/**
 * Получает данные из указанного диапазона.
 * @param {string} spreadsheetId ID таблицы.
 * @param {string} sheetName Название листа.
 * @param {string} range Строка, представляющая диапазон ячеек (например, "A1:B10").
 * @return {Array<Array<any>>} Двумерный массив значений.
 */
function getDataFromRange(spreadsheetId, sheetName, range) {
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const sheet = spreadsheet.getSheetByName(sheetName);
  const dataRange = sheet.getRange(range);
  const values = dataRange.getValues();
  return values;
}

Определение целевой таблицы

Для определения целевой таблицы также используется объект Spreadsheet и метод openById(). Необходимо знать ID таблицы, в которую будут скопированы данные.

/**
 * Возвращает объект Sheet целевой таблицы.
 * @param {string} spreadsheetId ID целевой таблицы.
 * @param {string} sheetName Название целевого листа.
 * @return {GoogleAppsScript.Spreadsheet.Sheet} Целевой лист.
 */
function getTargetSheet(spreadsheetId, sheetName) {
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const sheet = spreadsheet.getSheetByName(sheetName);
  return sheet;
}

Запись данных в целевую таблицу

Для записи данных в целевую таблицу используется метод setValues() объекта Range. Этот метод принимает двумерный массив данных и записывает его в указанный диапазон.

/**
 * Записывает данные в указанный диапазон целевого листа.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet Целевой лист.
 * @param {number} row Начальная строка для записи.
 * @param {number} column Начальный столбец для записи.
 * @param {Array<Array<any>>} data Двумерный массив данных.
 */
function writeDataToSheet(sheet, row, column, data) {
  const numRows = data.length;
  const numColumns = data[0].length;
  const range = sheet.getRange(row, column, numRows, numColumns);
  range.setValues(data);
}

Простой скрипт для копирования данных

Объяснение кода построчно

Пример простого скрипта для копирования данных из одной таблицы в другую:

/**
 * Копирует данные из одной таблицы в другую.
 */
function copyData() {
  // ID исходной таблицы и листа
  const sourceSpreadsheetId = 'YOUR_SOURCE_SPREADSHEET_ID';
  const sourceSheetName = 'Sheet1';
  const sourceRange = 'A1:C10';

  // ID целевой таблицы и листа
  const targetSpreadsheetId = 'YOUR_TARGET_SPREADSHEET_ID';
  const targetSheetName = 'Sheet1';
  const targetStartRow = 1; // Начальная строка для записи
  const targetStartColumn = 1; // Начальный столбец для записи

  // Получение данных из исходной таблицы
  const data = getDataFromRange(sourceSpreadsheetId, sourceSheetName, sourceRange);

  // Получение целевого листа
  const targetSheet = getTargetSheet(targetSpreadsheetId, targetSheetName);

  // Запись данных в целевой лист
  writeDataToSheet(targetSheet, targetStartRow, targetStartColumn, data);

  Logger.log('Данные успешно скопированы!');
}

Построчное объяснение:

  1. sourceSpreadsheetId, sourceSheetName, sourceRange: Определяют исходную таблицу, лист и диапазон для копирования.
  2. targetSpreadsheetId, targetSheetName: Определяют целевую таблицу и лист.
  3. targetStartRow, targetStartColumn: Определяют начальную позицию для записи данных в целевом листе.
  4. getDataFromRange(): Функция, описанная выше, получает данные из исходного диапазона.
  5. getTargetSheet(): Функция, описанная выше, получает целевой лист.
  6. writeDataToSheet(): Функция, описанная выше, записывает данные в целевой лист.
  7. Logger.log(): Выводит сообщение в журнал выполнения скрипта.

Настройка триггеров для автоматического копирования

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

Для настройки триггера:

  1. Откройте редактор скриптов.
  2. Выберите «Триггеры» (значок будильника) на панели слева.
  3. Нажмите «Добавить триггер».
  4. Выберите функцию для запуска (copyData в нашем случае).
  5. Выберите источник события (например, «При изменении таблицы»).
  6. Выберите тип события (например, «При изменении»).
  7. Сохраните триггер.

Пример кода: Полный скрипт копирования данных

/**
 * Копирует данные из одной таблицы в другую.
 */
function copyData() {
  // ID исходной таблицы и листа
  const sourceSpreadsheetId = 'YOUR_SOURCE_SPREADSHEET_ID';
  const sourceSheetName = 'Sheet1';
  const sourceRange = 'A1:C10';

  // ID целевой таблицы и листа
  const targetSpreadsheetId = 'YOUR_TARGET_SPREADSHEET_ID';
  const targetSheetName = 'Sheet1';
  const targetStartRow = 1; // Начальная строка для записи
  const targetStartColumn = 1; // Начальный столбец для записи

  try {
    // Получение данных из исходной таблицы
    const data = getDataFromRange(sourceSpreadsheetId, sourceSheetName, sourceRange);

    // Получение целевого листа
    const targetSheet = getTargetSheet(targetSpreadsheetId, targetSheetName);

    // Запись данных в целевой лист
    writeDataToSheet(targetSheet, targetStartRow, targetStartColumn, data);

    Logger.log('Данные успешно скопированы!');
  } catch (e) {
    Logger.log('Ошибка при копировании данных: ' + e);
  }
}

/**
 * Получает данные из указанного диапазона.
 * @param {string} spreadsheetId ID таблицы.
 * @param {string} sheetName Название листа.
 * @param {string} range Строка, представляющая диапазон ячеек (например, "A1:B10").
 * @return {Array<Array<any>>} Двумерный массив значений.
 */
function getDataFromRange(spreadsheetId, sheetName, range) {
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const sheet = spreadsheet.getSheetByName(sheetName);
  const dataRange = sheet.getRange(range);
  const values = dataRange.getValues();
  return values;
}

/**
 * Возвращает объект Sheet целевой таблицы.
 * @param {string} spreadsheetId ID целевой таблицы.
 * @param {string} sheetName Название целевого листа.
 * @return {GoogleAppsScript.Spreadsheet.Sheet} Целевой лист.
 */
function getTargetSheet(spreadsheetId, sheetName) {
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const sheet = spreadsheet.getSheetByName(sheetName);
  return sheet;
}

/**
 * Записывает данные в указанный диапазон целевого листа.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet Целевой лист.
 * @param {number} row Начальная строка для записи.
 * @param {number} column Начальный столбец для записи.
 * @param {Array<Array<any>>} data Двумерный массив данных.
 */
function writeDataToSheet(sheet, row, column, data) {
  const numRows = data.length;
  const numColumns = data[0].length;
  const range = sheet.getRange(row, column, numRows, numColumns);
  range.setValues(data);
}

Продвинутые методы копирования данных

Копирование с фильтрацией данных

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

/**
 * Копирует данные с фильтрацией по заданному условию.
 * @param {string} sourceSpreadsheetId ID исходной таблицы.
 * @param {string} sourceSheetName Название исходного листа.
 * @param {string} sourceRange Диапазон для копирования.
 * @param {string} targetSpreadsheetId ID целевой таблицы.
 * @param {string} targetSheetName Название целевого листа.
 * @param {function(Array<any>): boolean} filterFunction Функция фильтрации. Принимает строку данных и возвращает true, если строка должна быть скопирована.
 */
function copyDataWithFilter(sourceSpreadsheetId, sourceSheetName, sourceRange, targetSpreadsheetId, targetSheetName, filterFunction) {
  const data = getDataFromRange(sourceSpreadsheetId, sourceSheetName, sourceRange);
  const filteredData = data.filter(filterFunction);
  const targetSheet = getTargetSheet(targetSpreadsheetId, targetSheetName);
  writeDataToSheet(targetSheet, 1, 1, filteredData);
}

// Пример использования:
// Скопировать только строки, где значение в первом столбце больше 10
// copyDataWithFilter('sourceId', 'Sheet1', 'A1:C10', 'targetId', 'Sheet1', row => row[0] > 10);

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

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

/**
 * Копирует данные, выбирая только определенные столбцы.
 * @param {string} sourceSpreadsheetId ID исходной таблицы.
 * @param {string} sourceSheetName Название исходного листа.
 * @param {string} sourceRange Диапазон для копирования.
 * @param {string} targetSpreadsheetId ID целевой таблицы.
 * @param {string} targetSheetName Название целевого листа.
 * @param {Array<number>} columnIndices Массив индексов столбцов для копирования (начиная с 0).
 */
function copySpecificColumns(sourceSpreadsheetId, sourceSheetName, sourceRange, targetSpreadsheetId, targetSheetName, columnIndices) {
  const data = getDataFromRange(sourceSpreadsheetId, sourceSheetName, sourceRange);
  const transformedData = data.map(row => {
    return columnIndices.map(columnIndex => row[columnIndex]);
  });
  const targetSheet = getTargetSheet(targetSpreadsheetId, targetSheetName);
  writeDataToSheet(targetSheet, 1, 1, transformedData);
}

// Пример использования:
// Скопировать только первый и третий столбцы
// copySpecificColumns('sourceId', 'Sheet1', 'A1:C10', 'targetId', 'Sheet1', [0, 2]);

Трансформация данных во время копирования

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

/**
 * Копирует данные с трансформацией.
 * @param {string} sourceSpreadsheetId ID исходной таблицы.
 * @param {string} sourceSheetName Название исходного листа.
 * @param {string} sourceRange Диапазон для копирования.
 * @param {string} targetSpreadsheetId ID целевой таблицы.
 * @param {string} targetSheetName Название целевого листа.
 * @param {function(Array<any>): Array<any>} transformFunction Функция трансформации. Принимает строку данных и возвращает преобразованную строку.
 */
function copyDataWithTransformation(sourceSpreadsheetId, sourceSheetName, sourceRange, targetSpreadsheetId, targetSheetName, transformFunction) {
  const data = getDataFromRange(sourceSpreadsheetId, sourceSheetName, sourceRange);
  const transformedData = data.map(transformFunction);
  const targetSheet = getTargetSheet(targetSpreadsheetId, targetSheetName);
  writeDataToSheet(targetSheet, 1, 1, transformedData);
}

// Пример использования:
// Умножить значение во втором столбце на 2
// copyDataWithTransformation('sourceId', 'Sheet1', 'A1:C10', 'targetId', 'Sheet1', row => {
//   row[1] = row[1] * 2;
//   return row;
// });

Копирование с учетом форматирования

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

/**
 * Копирует диапазон с форматированием в другую таблицу.
 * @param {string} sourceSpreadsheetId ID исходной таблицы.
 * @param {string} sourceSheetName Название исходного листа.
 * @param {string} sourceRange Диапазон для копирования.
 * @param {string} targetSpreadsheetId ID целевой таблицы.
 * @param {string} targetSheetName Название целевого листа.
 * @param {number} targetRow Начальная строка для вставки.
 * @param {number} targetColumn Начальный столбец для вставки.
 */
function copyRangeWithFormatting(sourceSpreadsheetId, sourceSheetName, sourceRange, targetSpreadsheetId, targetSheetName, targetRow, targetColumn) {
  const sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
  const sourceSheet = sourceSpreadsheet.getSheetByName(sourceSheetName);
  const sourceRangeObj = sourceSheet.getRange(sourceRange);

  const targetSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetId);
  const targetSheet = targetSpreadsheet.getSheetByName(targetSheetName);
  const targetRangeObj = targetSheet.getRange(targetRow, targetColumn, sourceRangeObj.getNumRows(), sourceRangeObj.getNumColumns());

  sourceRangeObj.copyTo(targetRangeObj, {contentsOnly: false, formatOnly: false});
}

// Пример использования:
// copyRangeWithFormatting('sourceId', 'Sheet1', 'A1:C10', 'targetId', 'Sheet1', 1, 1);

Обработка ошибок и оптимизация скриптов

Логирование ошибок

Для отладки и обработки ошибок рекомендуется использовать try...catch блоки и логирование ошибок с помощью Logger.log().

try {
  // Код, который может вызвать ошибку
} catch (e) {
  Logger.log('Произошла ошибка: ' + e);
}

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

Для оптимизации скорости выполнения скрипта рекомендуется:

  • Избегать циклов for при работе с данными. Вместо этого использовать методы map, filter и другие.
  • Минимизировать количество обращений к Sheets API. Считывать и записывать данные большими блоками.
  • Использовать SpreadsheetApp.getActiveSpreadsheet() и SpreadsheetApp.getActiveSheet() вместо SpreadsheetApp.openById() и getSheetByName(), если работаете с текущей таблицей.

Предотвращение ошибок при работе с большими объемами данных

При работе с большими объемами данных следует учитывать ограничения Google Apps Script и Sheets API (например, время выполнения скрипта, количество запросов в минуту). Разбивайте задачи на более мелкие, используйте пакетную обработку данных и избегайте избыточных операций.

Примеры использования копирования данных

Создание резервных копий данных

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

Перенос данных между разными листами и книгами

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

Автоматическое обновление данных в отчетах

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

Безопасность и ограничения Google Apps Script

Авторизация и права доступа

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

Квоты и лимиты выполнения скриптов

Google Apps Script имеет квоты и лимиты на выполнение скриптов. Они зависят от типа аккаунта (бесплатный или платный). Превышение квот может привести к временной блокировке скрипта.

Рекомендации по безопасному кодированию

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

Заключение

Полезные ресурсы и ссылки

Дальнейшее изучение Google Apps Script

Google Apps Script – мощный инструмент для автоматизации задач в Google Workspace. Изучите другие сервисы Apps Script, такие как Docs, Slides, Forms, Calendar и Gmail, чтобы расширить свои возможности. Экспериментируйте с различными API и интегрируйте Apps Script с другими сервисами Google и сторонними приложениями. Например, настройте получение данных из рекламных API (Google Ads, Yandex.Direct) и автоматическое формирование отчетов в Google Sheets, что позволит значительно сэкономить время и повысить эффективность работы с данными.

Метки:

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

Как узнать длину строки в Google Apps Script?
Что такое Google Apps Script и где он применяется Google Apps Script (GAS) — это облачная платформа разработки, позволяющая автоматизировать задачи и расширять функциональность Google Workspace, включая Google Sheets, Docs, Forms и другие приложения.
  •  0
  •   2025
Google Apps Script: Как удалить строку?
Обзор Google Apps Script и его возможностей Google Apps Script (GAS) — это облачная платформа разработки, позволяющая автоматизировать задачи и расширять функциональность Google Workspace, включая Google Sheets.
  •  12
  •   2025
Google Apps Script: Как добавить столбец в таблицу?
Введение в добавление столбцов в Google Таблицах с помощью Apps Script Что такое Google Apps Script и зачем он нужен для работы с таблицами? Google Apps Script — это облачный язык сценариев, который позволяет автоматизировать задачи в Google Workspace, включая Google Таблицы.
  •  13
  •   2025
Google Apps Script: Где найти примеры и руководство для новичков?
Введение в Google Apps Script для начинающих Что такое Google Apps Script и зачем он нужен? Google Apps Script (GAS) — это облачный язык сценариев, разработанный Google для автоматизации задач и расширения функциональности Google Workspace (ранее G Suite).
  •  16
  •   2025