Содержание
- О чем мы будем говорить
- Как подготовить лист
- Как открыть проект сценария Apps Script
- Как создать функцию для удаления пустых строк и столбцов за пределами диапазона данных
- Как создать функцию для удаления пустых строк и столбцов внутри диапазона данных
- Как создать пользовательское меню для электронной таблицы
- Заключение
В этом уроке вы узнаете, как удалить пустые строки и столбцы из Google Sheets с помощью Google Apps Script.
Некоторое время назад я написал статью [Как удалить пустые строки и столбцы из Google Sheets] (https://kcl.hashnode.dev/how-to-delete-empty-rows-and-columns-in-google-sheets).
Недавно я пересмотрел эту статью и теперь представляю вам ее обновленную версию.
У меня также есть видеоверсия этой темы, которую вы можете посмотреть ниже:
О чем мы будем говорить
Вы создадите две функции: deleteExternalEmptyRowsNColumns() и deleteInternalEmptyRowsNColumns().
Первая функция удалит из диапазона пустые строки и столбцы, которые находятся вне диапазона, возвращаемого методом getDataRange().
Вторая функция удалит пустые строки и столбцы, которые находятся внутри диапазона, возвращаемого методом getDataRange().
Мы также создадим меню, чтобы можно было выполнять эти функции из самой электронной таблицы.
Как подготовить лист
В настоящее время моя электронная таблица выглядит так, как показано на рисунке ниже:
Электронная таблица с большим количеством пустых строк и столбцов
В таблице есть несколько столбцов и строк с данными и множество пустых строк и столбцов.
Давайте приведем электронную таблицу в более презентабельный вид, как показано на следующем изображении:
Окончательная версия чистых электронных таблиц
Как открыть проект сценария Apps Script
Далее давайте откроем наш проект Apps Script на вкладке Extensions в электронной таблице:
Открытие проекта Apps Script с вкладки электронной таблицы
Как создать функцию для удаления пустых строк и столбцов за пределами диапазона данных
Мы создадим функцию с именем deleteExternalEmptyRowsNColumns().
Эта функция будет отвечать за удаление пустых строк и столбцов, которые находятся за пределами диапазона getDataRange():
/\*\*
- Удаляет пустые строки и столбцы за пределами диапазона DataRange()
\*/
function deleteExternalEmptyRowsNColumns() {
// получаем листы и данные
const ss = SpreadsheetApp.getActiveSheet();
const data = ss.getDataRange().getValues();
//console.log(data);
// определяем последнюю строку и столбец
const lastRow = data.length;
const lastCol = data[0].length;
// получаем максимальное количество строк и столбцов sss
const maxRows = ss.getMaxRows();
const maxCols = ss.getMaxColumns();
// удаляйте строки и столбцы только в том случае, если за последней строкой и столбцами есть пустые строки или столбцы
if (maxRows > lastRow) {
ss.deleteRows(lastRow + 1, maxRows - lastRow);
}
if (maxCols > lastCol) {
ss.deleteColumns(lastCol + 1, maxCols - lastCol);
}
}
Функция для удаления внешних пустых строк и столбцов
Мы используем максимальное количество строк и максимальное количество столбцов, потому что эти значения вернут последнюю строку и последний столбец электронной таблицы независимо от ее содержимого.
Это означает, что они также включают пустые строки и пустые столбцы за пределами диапазона данных.
Далее мы удаляем столбцы и строки, только если они находятся за пределами диапазона.
Это означает, что если максимальная строка больше, чем последняя, то мы удалим строки. То же самое относится и к столбцам.
Для удаления строк мы используем метод deleteRows(), который принимает два параметра.
Первый - это индекс строки, из которой нужно удалить строки, lastRow + 1 в нашем случае.
Второй параметр - количество строк, которые нужно удалить, в нашем случае это maxRows - lastRow.
Для столбцов мы будем использовать метод deleteColumns(). Этот метод работает так же, как и метод deleteRows(), но для столбцов.
Если вы запустите эту функцию, ваша электронная таблица будет выглядеть примерно так, как показано на следующем рисунке:
Электронная таблица с удаленными выходящими за границы столбцами и строками
Вы видите, что столбцы и строки, которые выходили за пределы диапазона, возвращаемого функцией getDataRange(), теперь удалены функцией.
Как создать функцию для удаления пустых строк и столбцов внутри диапазона данных
Now we will create another function: deleteInternalEmptyRowsNColumns().
Она будет отвечать за удаление пустых строк и столбцов, которые включены в метод getDataRange(), с помощью этой функции ниже:
/\*\*
- Удаляет пустые строки и столбцы внутри DataRange()
\*/
function deleteInternalEmptyRowsNColumns() {
// получаем листы и данные
const ss = SpreadsheetApp.getActiveSheet();
const data = ss.getDataRange().getValues();
const lastRow = data.length;
const lastCol = data[0].length;
// проверяем, нет ли пустых столбцов в начале, которое включено в данные
const emptyColumnIndexes = [];
for (let i = 1; i <= lastCol; i++) {
if (ss.getRange(1, i, lastRow, 1).getValues().flat().join('') === '') {
// вычитаем длину перед тем, как выталкивать значение с индексом меньше 1 от исходного
// потому что позже, когда мы будем удалять столбцы один за другим, индексы
// будут выходить за границы/неправильными из-за того, что sprd будет обновлен до новых индексов
emptyColumnIndexes.push(i - emptyColumnIndexes.length);
}
}
// удалим эти столбцы
if (emptyColumnIndexes.length > 0) {
// удаляем колонку
emptyColumnIndexes.forEach((ind) => ss.deleteColumn(ind));
}
//***************Удаление внутренних пустых строк */
// преобразуем вложенные массивы в строки и удаляем пустые строки с помощью фильтра
const newData = ss
.getDataRange()
.getValues()
.filter((arr) => arr.join('') !== '');
const newLastRow = newData.length;
const newLastCol = newData[0].length;
// очищаем предыдущие значения
ss.clearContents();
// устанавливаем новые значения
ss.getRange(1, 1, newLastRow, newLastCol).setValues(newData);
// теперь удалите пустые строки и столбцы
deleteExternalEmptyRowsNColumns();
}
Функция для удаления внутренних пустых строк и столбцов
Объясним, что делает эта функция, в следующих разделах.
Как удалить пустые столбцы
Сначала мы поработаем с пустыми столбцами. После этого мы удалим пустые строки.
const emptyColumnIndexes = [];
for (let i = 1; i <= lastCol; i++) {
if (ss.getRange(1, i, lastRow, 1).getValues().flat().join('') === '') {
// вычитаем длину перед тем, как выталкивать значение с индексом меньше 1 от исходного
// потому что позже, когда мы будем удалять столбцы один за другим, индексы
// будут выходить за границы/неправильно из-за того, что sprd будет обновлен до новых индексов
emptyColumnIndexes.push(i - emptyColumnIndexes.length);
}
}
// удалим эти столбцы
if (emptyColumnIndexes.length > 0) {
// удаляем колонку
emptyColumnIndexes.forEach((ind) => ss.deleteColumn(ind));
}
Создадим массив с именем emptyCoiumnIndexes. В нем будут храниться все индексы пустых столбцов.
Чтобы проверить, пустые столбцы или нет, мы пройдемся по каждому столбцу с помощью цикла for, начиная с первого столбца.
Далее мы будем получать значения столбцов. В каждом цикле будет возвращаться вложенный массив, который мы сплющим.
После этого мы объединим массив с пустой строкой (””).
Если объединенная строка действительно пуста, мы знаем, что это пустой столбец, поэтому мы поместим этот индекс в массив индексов пустых столбцов с помощью следующего кода: ss.getRange(1, i, lastRow, 1).getValues().flat().join("") === "" .
Но перед тем, как подставить индекс, мы каждый раз будем вычитать длину массива emptyColumnIndexes из самого индекса.
Это связано с тем, что в дальнейшем, когда мы будем удалять этот столбец, нам придется удалять каждый столбец по очереди.
При этом мы обнаружим, что при удалении первого столбца структура электронной таблицы меняется, и у столбцов, которые идут после удаленного столбца, меняется индекс.
Электронная таблица с входящими пустыми столбцами и строками
Например, из предыдущего изображения видно, что после удаления столбца “A” индекс столбца “F” изменится на “E”.
После этого, если emptyColumnIndexes не пуст, мы переберем каждое значение, используя forEach().
Затем мы удалим столбец с помощью метода deleteColumn().
Запустите эту функцию, и вы увидите результат, похожий на следующее изображение, где все пустые столбцы были удалены:
Электронная таблица с удаленными пустыми столбцами
Как удалить пустые строки
Теперь мы займемся удалением пустых строк из нашей электронной таблицы.
Для этого мы отфильтруем все непустые строки, используя тот же процесс, который мы использовали ранее, присоединив к ним пустую строку.
Если они не являются просто пустой кавычкой (””), мы вернем их как элементы массива в newData с const newData = ss.getDataRange().getValues().filter((arr) => arr.join("") !== "").
Теперь мы сохраним эти значения в нашу электронную таблицу, очистив предыдущее содержимое.
Но если запустить эту функцию жесткоТеперь вы поймете, что это не удаляет пустые столбцы, а накапливает строки в одном месте, как показано на рисунке ниже:
Электронная таблица с исходящими пустыми строками
Это не то, что нам нужно.
Поэтому, чтобы удалить лишние строки, мы просто вызовем функцию deleteExternalEmptyRowsNColumns(), которую мы создали ранее, потому что теперь эти лишние пробелы находятся за пределами диапазона getDataRange().
Давайте запустим функцию снова, и теперь мы сможем добиться того, чего хотели изначально:
Чистая электронная таблица
Как создать пользовательское меню для электронной таблицы
Наконец, мы создадим меню, чтобы можно было запускать эти функции из самой электронной таблицы.
Для этого создайте в проекте новый файл сценария с именем menu:
/\*\*
- Menu создает пользовательский интерфейс меню в электронной таблице.
\*/
function createCustomMenu() {
let menu = SpreadsheetApp.getUi().createMenu('Delete Empty Rows N Columns');
menu.addItem('Удалить внешние пустые строки и столбцы', 'deleteExternalEmptyRowsNColumns');
menu.addItem('Удалить внутренние пустые строки', 'deleteInternalEmptyRowsNColumns');
menu.addToUi();
}
/\*\*
- Триггер OnOpen, создающий меню
- @param {Dictionary} e
\*/
function onOpen() {
createCustomMenu();
}
Сохранив скрипт, перейдем в электронную таблицу и перезагрузим ее.
Через некоторое время на вкладке электронной таблицы появится меню, как показано на рисунке ниже:
Меню электронной таблицы
Поздравляем!
Теперь вам нужно просто скопировать и вставить скрипт из учебника или this GitHub repo, и вы сможете мгновенно очистить свои таблицы.
Заключение
В этом руководстве мы создали две функции: deleteExternalEmptyRowsNColumns() и deleteInternalEmptyRowsNColumns().
Мы очищали пустые строки и столбцы, которые выходили за границы, а затем удаляли пустые строки и столбцы, которые находились в границах данных.
Далее мы создали меню, которое обеспечивает легкий доступ даже для некодировщиков к выполнению вышеупомянутых функций с вкладки электронной таблицы.
Теперь осталось только поделиться этой статьей. Если вы также смотрите видеоверсию, надеюсь, вы также подпишитесь на мой канал.
Я Нибеш Кхадка, фрилансер, специализирующийся на автоматизации продуктов Google с помощью Apps Script. Свяжитесь со мной, если вам нужны мои услуги, по адресу me@nibeshkhadka.com.