Google Apps Script: Как проверить и удалить данные?

/**
 * Получает все данные с указанного листа.
 * @param {string} sheetName Имя листа.
 * @returns {string[][]} Двумерный массив данных.
 * @throws {Error} Если лист с указанным именем не найден.
 */
function getDataFromSheet(sheetName: string): string[][] {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    throw new Error(`Лист с именем "${sheetName}" не найден.`);
  }
  return sheet.getDataRange().getValues();
}

/**
 * Проверяет корректность URL в указанном столбце.
 * @param {string} sheetName Имя листа.
 * @param {number} urlColumnIndex Индекс столбца с URL (начиная с 0).
 */
function validateUrls(sheetName: string, urlColumnIndex: number): void {
  const data = getDataFromSheet(sheetName);
  const urlRegex = /^(https?:\/\/)?([\da-z\.-]+)\.([a-z\.]{2,6})([\/\w \.-]*)*\/?$/i;

  // Пропускаем заголовок
  for (let i = 1; i < data.length; i++) {
    const url = data[i][urlColumnIndex];
    if (typeof url !== 'string' || !urlRegex.test(url)) {
      Logger.log(`Некорректный URL в строке ${i + 1}: ${url}`);
      // Опционально: пометить ячейку
      // const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
      // sheet?.getRange(i + 1, urlColumnIndex + 1).setBackground('yellow');
    }
  }
  SpreadsheetApp.flush(); // Применяем изменения (если были, например, setBackground)
}

// Пример вызова
// validateUrls('CampaignData', 3); // Проверить URL в 4-м столбце листа 'CampaignData'

/**
 * Удаляет строки-дубликаты на основе значений в указанном столбце.
 * @param {string} sheetName Имя листа.
 * @param {number} keyColumnIndex Индекс ключевого столбца для проверки дубликатов (начиная с 0).
 */
function removeDuplicateRows(sheetName: string, keyColumnIndex: number): void {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    Logger.log(`Лист "${sheetName}" не найден.`);
    return;
  }

  const data = sheet.getDataRange().getValues();
  const uniqueKeys = new Set();
  const rowsToDelete: number[] = [];

  // Начинаем с конца, чтобы индексы удаляемых строк не сбивались
  for (let i = data.length - 1; i >= 0; i--) {
    const key = data[i][keyColumnIndex]?.toString(); // Приводим к строке для универсальности
    if (key === undefined || key === null) continue; // Пропускаем, если ключ пуст

    if (uniqueKeys.has(key)) {
      rowsToDelete.push(i + 1); // Сохраняем номер строки (1-based)
    } else {
      uniqueKeys.add(key);
    }
  }

  // Удаляем строки одним пакетом (эффективнее)
  if (rowsToDelete.length > 0) {
    // Сортируем индексы для корректного удаления пачками, если необходимо (хотя deleteRow работает и так)
    rowsToDelete.sort((a, b) => a - b);
    Logger.log(`Найдено дубликатов для удаления: ${rowsToDelete.length}`);

    // Удаляем по одной строке с конца, чтобы избежать проблем с индексацией
    for (let i = rowsToDelete.length - 1; i >= 0; i--) {
      sheet.deleteRow(rowsToDelete[i]);
    }
    SpreadsheetApp.flush(); // Применяем изменения
  }
}

// Пример вызова
// removeDuplicateRows('Leads', 1); // Удалить дубликаты по 2-му столбцу (Email) на листе 'Leads'

/**
 * Удаляет полностью пустые строки с листа.
 * @param {string} sheetName Имя листа.
 */
function deleteEmptyRows(sheetName: string): void {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    Logger.log(`Лист "${sheetName}" не найден.`);
    return;
  }
  const maxRows = sheet.getMaxRows();
  const data = sheet.getDataRange().getValues();
  const rowsToDelete: number[] = [];

  // Идем с конца
  for (let i = data.length - 1; i >= 0; i--) {
    // Проверяем, все ли ячейки в строке пустые
    const isEmpty = data[i].every(cell => cell === '');
    if (isEmpty) {
      rowsToDelete.push(i + 1); // Номер строки (1-based)
    }
  }

  if (rowsToDelete.length > 0) {
    Logger.log(`Найдено пустых строк для удаления: ${rowsToDelete.length}`);
    for (let i = 0; i < rowsToDelete.length; i++) {
      // Удаляем строку с учетом ее текущего положения (т.к. предыдущие уже удалены)
      // Если удаляем с конца, индекс всегда корректен
      sheet.deleteRow(rowsToDelete[i]);
    }
    SpreadsheetApp.flush();
  }
}

// Пример вызова
// deleteEmptyRows('ReportData');

/**
 * Проверяет email в указанном столбце и удаляет строки с некорректными адресами.
 * @param {string} sheetName Имя листа.
 * @param {number} emailColumnIndex Индекс столбца с email (начиная с 0).
 */
function validateAndRemoveInvalidEmails(sheetName: string, emailColumnIndex: number): void {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    Logger.log(`Лист "${sheetName}" не найден.`);
    return;
  }

  const data = sheet.getDataRange().getValues();
  const emailRegex = /^[^
\s@]+@[^
\s@]+\.[^
\s@]+$/i;
  const rowsToDelete: number[] = [];

  // Идем с конца (пропускаем заголовок, если он есть - i > 0)
  for (let i = data.length - 1; i > 0; i--) {
    const email = data[i][emailColumnIndex];
    // Считаем строку некорректной, если email не строка или не соответствует формату
    if (typeof email !== 'string' || !emailRegex.test(email)) {
      Logger.log(`Обнаружен некорректный email в строке ${i + 1}: "${email}". Строка будет удалена.`);
      rowsToDelete.push(i + 1);
    }
  }

  if (rowsToDelete.length > 0) {
    Logger.log(`Удаление ${rowsToDelete.length} строк с некорректными email.`);
    // Удаляем по одной строке с конца
    for (const rowIndex of rowsToDelete) {
      sheet.deleteRow(rowIndex);
    }
    SpreadsheetApp.flush();
  }
}

// Пример вызова
// validateAndRemoveInvalidEmails('Subscribers', 2); // Проверить и удалить из 3-го столбца

/**
 * Удаляет строки, в которых ячейка в указанном столбце пуста.
 * @param {string} sheetName Имя листа.
 * @param {number} checkColumnIndex Индекс проверяемого столбца (начиная с 0).
 */
function deleteRowsWithEmptyCell(sheetName: string, checkColumnIndex: number): void {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    Logger.log(`Лист "${sheetName}" не найден.`);
    return;
  }

  const data = sheet.getDataRange().getValues();
  const rowsToDelete: number[] = [];

  // Идем с конца (пропускаем заголовок, если нужно)
  for (let i = data.length - 1; i >= 1; i--) { // i >= 1 чтобы пропустить заголовок
    const cellValue = data[i][checkColumnIndex];
    if (cellValue === null || cellValue === undefined || cellValue === '') {
      rowsToDelete.push(i + 1);
    }
  }

  if (rowsToDelete.length > 0) {
    Logger.log(`Удаление ${rowsToDelete.length} строк с пустым значением в столбце ${checkColumnIndex + 1}.`);
    for (const rowIndex of rowsToDelete) {
      sheet.deleteRow(rowIndex);
    }
    SpreadsheetApp.flush();
  }
}

// Пример вызова
// deleteRowsWithEmptyCell('Transactions', 0); // Удалить строки, где первый столбец (ID) пуст