Google Apps Script предоставляет мощные инструменты для автоматизации работы с Google Sheets, включая возможность скрытия и отображения столбцов. Это позволяет адаптировать представление данных для разных пользователей или задач, упрощая анализ и повышая удобство работы.
Зачем скрывать столбцы? Примеры использования
Скрытие столбцов может быть полезно во многих ситуациях:
Сокрытие конфиденциальной информации: Например, личные данные клиентов, зарплаты сотрудников и т.д.
Упрощение интерфейса: Удаление ненужных столбцов для конкретного пользователя или задачи.
Автоматизация отчетности: Отображение только релевантных данных в отчетах.
A/B тестирование: Скрытие группы столбцов для тестирования различных представлений данных.
Необходимые условия: доступ к Google Sheets и базовые знания Apps Script
Для работы с примерами в этой статье вам потребуется:
Доступ к Google Sheets.
Базовые знания Google Apps Script (открытие редактора скриптов, сохранение скриптов, запуск функций).
Понимание основных объектов Google Sheets API (Spreadsheet, Sheet, Range).
Основные методы скрытия столбцов
Использование `hideColumn()` для скрытия одного столбца
Метод hideColumn() объекта Sheet позволяет скрыть указанный столбец.
Использование `hideColumns()` для скрытия нескольких столбцов
Метод hideColumns() позволяет скрыть диапазон столбцов. Он принимает начальный столбец и количество столбцов для скрытия.
Примеры кода с пояснениями
/**
* Скрывает указанный столбец на листе.
*
* @param {string} sheetName - Имя листа.
* @param {number} columnIndex - Индекс столбца для скрытия (начиная с 1).
*/
function hideSingleColumn(sheetName: string, columnIndex: number): void {
try {
// Получаем активную таблицу.
const spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Получаем лист по имени.
const sheet: GoogleAppsScript.Spreadsheet.Sheet | null = spreadsheet.getSheetByName(sheetName);
// Проверяем, что лист существует.
if (!sheet) {
throw new Error(`Лист с именем '${sheetName}' не найден.`);
}
// Скрываем столбец.
sheet.hideColumn(sheet.getColumn(columnIndex));
Logger.log(`Столбец ${columnIndex} успешно скрыт на листе '${sheetName}'.`);
} catch (error: any) {
Logger.log(`Ошибка при скрытии столбца: ${error.message}`);
}
}
/**
* Скрывает диапазон столбцов на листе.
*
* @param {string} sheetName - Имя листа.
* @param {number} startColumnIndex - Индекс начального столбца (начиная с 1).
* @param {number} numberOfColumns - Количество столбцов для скрытия.
*/
function hideMultipleColumns(sheetName: string, startColumnIndex: number, numberOfColumns: number): void {
try {
// Получаем активную таблицу.
const spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Получаем лист по имени.
const sheet: GoogleAppsScript.Spreadsheet.Sheet | null = spreadsheet.getSheetByName(sheetName);
// Проверяем, что лист существует.
if (!sheet) {
throw new Error(`Лист с именем '${sheetName}' не найден.`);
}
// Скрываем столбцы.
sheet.hideColumns(startColumnIndex, numberOfColumns);
Logger.log(`Столбцы с ${startColumnIndex} по ${startColumnIndex + numberOfColumns - 1} успешно скрыты на листе '${sheetName}'.`);
} catch (error: any) {
Logger.log(`Ошибка при скрытии столбцов: ${error.message}`);
}
}
// Пример использования:
// hideSingleColumn("Лист1", 3); // Скрывает третий столбец на листе "Лист1".
// hideMultipleColumns("Лист1", 5, 2); // Скрывает 5-й и 6-й столбцы на листе "Лист1".Расширенные методы и техники
Скрытие столбцов на основе условий (например, значения в ячейке)
/**
* Скрывает столбец, если значение в указанной ячейке соответствует заданному.
*
* @param {string} sheetName - Имя листа.
* @param {number} columnIndex - Индекс столбца для скрытия.
* @param {number} rowIndex - Индекс строки, в которой находится ячейка для проверки.
* @param {number} checkColumnIndex - Индекс столбца, в которой находится ячейка для проверки.
* @param {any} expectedValue - Ожидаемое значение в ячейке.
*/
function hideColumnIfCellValueEquals(sheetName: string, columnIndex: number, rowIndex: number, checkColumnIndex:number, expectedValue: any): void {
try {
// Получаем таблицу и лист.
const spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet: GoogleAppsScript.Spreadsheet.Sheet | null = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
throw new Error(`Лист с именем '${sheetName}' не найден.`);
}
// Получаем значение ячейки.
const cellValue: any = sheet.getRange(rowIndex, checkColumnIndex).getValue();
// Скрываем столбец, если значение соответствует.
if (cellValue === expectedValue) {
sheet.hideColumn(sheet.getColumn(columnIndex));
Logger.log(`Столбец ${columnIndex} скрыт, так как значение в ячейке (${rowIndex}, ${checkColumnIndex}) равно '${expectedValue}'.`);
} else {
Logger.log(`Столбец ${columnIndex} не скрыт, так как значение в ячейке (${rowIndex}, ${checkColumnIndex}) не равно '${expectedValue}'.`);
}
} catch (error: any) {
Logger.log(`Ошибка: ${error.message}`);
}
}
//Пример использования
//hideColumnIfCellValueEquals("Лист1", 3, 1, 1, "Скрыть"); //Скрывает третий столбец если значение в ячейке A1 равно "Скрыть"Скрытие столбцов в зависимости от прав пользователя
Этот сценарий требует более сложной реализации с использованием сервисов аутентификации и авторизации, а также знания структуры прав в Google Workspace. Вкратце, можно получить информацию о пользователе, который открыл таблицу, и на основе этой информации скрывать или показывать столбцы. Однако, стоит помнить, что Apps Script выполняется на сервере, и без дополнительного UI, сложно реализовать интерактивное изменение видимости столбцов пользователем.
Использование `getColumnWidth()` перед скрытием для восстановления ширины
Перед скрытием столбца можно сохранить его ширину, чтобы при отображении столбца восстановить исходную ширину. Это улучшит визуальное восприятие данных.
function hideColumnAndSaveWidth(sheetName: string, columnIndex: number): void {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
if (!sheet) return;
const column = sheet.getColumn(columnIndex);
const width = sheet.getColumnWidth(columnIndex);
PropertiesService.getDocumentProperties().setProperty(`columnWidth_${sheetName}_${columnIndex}`, String(width));
sheet.hideColumn(column);
}
function showColumnAndRestoreWidth(sheetName: string, columnIndex: number): void {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
if (!sheet) return;
const column = sheet.getColumn(columnIndex);
sheet.showColumn(column);
const width = PropertiesService.getDocumentProperties().getProperty(`columnWidth_${sheetName}_${columnIndex}`);
if (width) {
sheet.setColumnWidth(columnIndex, Number(width));
}
}Отображение скрытых столбцов
Использование `showColumn()` для отображения одного столбца
Метод showColumn() объекта Sheet позволяет отобразить ранее скрытый столбец.
Использование `showColumns()` для отображения нескольких столбцов
Метод showColumns() позволяет отобразить диапазон ранее скрытых столбцов. Он принимает начальный столбец и количество столбцов для отображения.
Отображение всех скрытых столбцов
К сожалению, в Apps Script нет встроенного метода для отображения всех скрытых столбцов одним вызовом. Необходимо итерироваться по всем столбцам и отображать их по одному. Это не самый эффективный подход, но он работает. Альтернативно, можно хранить информацию о скрытых столбцах в отдельном месте (например, в PropertiesService) и использовать эту информацию для их отображения.
/**
* Отображает указанный столбец на листе.
*
* @param {string} sheetName - Имя листа.
* @param {number} columnIndex - Индекс столбца для отображения.
*/
function showSingleColumn(sheetName: string, columnIndex: number): void {
try {
// Получаем активную таблицу.
const spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Получаем лист по имени.
const sheet: GoogleAppsScript.Spreadsheet.Sheet | null = spreadsheet.getSheetByName(sheetName);
// Проверяем, что лист существует.
if (!sheet) {
throw new Error(`Лист с именем '${sheetName}' не найден.`);
}
// Отображаем столбец.
sheet.showColumn(sheet.getColumn(columnIndex));
Logger.log(`Столбец ${columnIndex} успешно отображен на листе '${sheetName}'.`);
} catch (error: any) {
Logger.log(`Ошибка при отображении столбца: ${error.message}`);
}
}
/**
* Отображает диапазон столбцов на листе.
*
* @param {string} sheetName - Имя листа.
* @param {number} startColumnIndex - Индекс начального столбца.
* @param {number} numberOfColumns - Количество столбцов для отображения.
*/
function showMultipleColumns(sheetName: string, startColumnIndex: number, numberOfColumns: number): void {
try {
// Получаем активную таблицу.
const spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Получаем лист по имени.
const sheet: GoogleAppsScript.Spreadsheet.Sheet | null = spreadsheet.getSheetByName(sheetName);
// Проверяем, что лист существует.
if (!sheet) {
throw new Error(`Лист с именем '${sheetName}' не найден.`);
}
// Отображаем столбцы.
sheet.showColumns(startColumnIndex, numberOfColumns);
Logger.log(`Столбцы с ${startColumnIndex} по ${startColumnIndex + numberOfColumns - 1} успешно отображены на листе '${sheetName}'.`);
} catch (error: any) {
Logger.log(`Ошибка при отображении столбцов: ${error.message}`);
}
}
// Пример использования:
// showSingleColumn("Лист1", 3); // Отображает третий столбец на листе "Лист1".
// showMultipleColumns("Лист1", 5, 2); // Отображает 5-й и 6-й столбцы на листе "Лист1".
/**
* Отображает все скрытые столбцы на листе.
*
* @param {string} sheetName - Имя листа.
*/
function showAllHiddenColumns(sheetName: string): void {
try {
// Получаем таблицу и лист.
const spreadsheet: GoogleAppsScript.Spreadsheet.Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet: GoogleAppsScript.Spreadsheet.Sheet | null = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
throw new Error(`Лист с именем '${sheetName}' не найден.`);
}
// Получаем общее количество столбцов.
const lastColumn: number = sheet.getMaxColumns();
// Перебираем все столбцы и отображаем их.
for (let i = 1; i <= lastColumn; i++) {
if (!sheet.isColumnHiddenByUser(i)) continue; //Пропускаем если столбец не скрыт
sheet.showColumn(sheet.getColumn(i));
}
Logger.log(`Все скрытые столбцы успешно отображены на листе '${sheetName}'.`);
} catch (error: any) {
Logger.log(`Ошибка при отображении столбцов: ${error.message}`);
}
}
//Пример использования
//showAllHiddenColumns("Лист1");Практические примеры и сценарии
Автоматическое скрытие столбцов с конфиденциальной информацией
Реализуйте функцию, которая автоматически скрывает столбцы, содержащие персональные данные, такие как номера телефонов или адреса электронной почты. Для этого можно использовать регулярные выражения или списки ключевых слов для определения конфиденциальных столбцов. Например, если заголовок столбца содержит слово "Телефон" или "Email", то столбец автоматически скрывается.
Создание пользовательского интерфейса для управления видимостью столбцов
Создайте диалоговое окно в Google Sheets, которое позволит пользователю выбирать, какие столбцы отображать и скрывать. Это можно реализовать с помощью HTML Service и пользовательских функций в Apps Script.
Скрытие столбцов на основе выбора пользователя в диалоговом окне
Этот сценарий является расширением предыдущего. Пользователь выбирает в диалоговом окне, какие столбцы нужно скрыть, а скрипт выполняет скрытие этих столбцов на основе выбора пользователя.