Автоматизация. Разработка динамического прототипа курьерской службы на сервисах Google.
16.08.2024
Комментариев нет
Удаляем строки из таблицы с помощью Sheets API
- 5 апреля, 2023
- 3 Комментариев
- 6989 просмотров
Содержание
Часто бывает так, особенно, при организации переноса данных из одной таблицы в другую или же просто при очистке старых записей, что нужно удалить строки из разных мест таблицы.
Способы удаления строк
Способов решения этой задачи несколько. Можно воспользоваться штатным методом sheet.deleteRow(rowNumber), можно забрать все данные с листа в скрипт, отфильтровать их перезаписать обратно. В этой статье рассмотрим другой способ, сделаем это одним запросом и очень быстро, для удаления строк будем применять Sheets API.
Таблица с данными и скриптом
Работать будем с этой таблицей, весь описанный код можно посмотреть в редакторе скриптов. Ссылка на инструкцию как попасть в редактор скриптов будет ниже.
Вот что из себя представляет таблица, из которой мы будем удалять строки
Таблица для нас будет просто отображением результата выполнения скрипта, который мы с вами сейчас и напишем. Итак, открываем редактор и приступаем.
Функция для получения данных из таблицы
Первым делом нужно определиться с тем, какие строки удалять. Для этого пишем функцию getRowsForDelete()
/**
* функция getRowsForDelete обращается к таблице.
* получает данные и опредеяет индексы (начиная с нуля) строк,
* которые нужно удалить. Возвращает массив с индексами для удаления.
* @returns {Array<number>} rowNumbersArr - массив индексов строк, соответсвующих условию.
*/
function getRowsForDelete() {
/** Получаем инстанс таблицы */
const table = SpreadsheetApp.getActive();
/** Получаем все листы таблицы и берем первый
* Можно получить лист по имени: table.getSheetByName('имя листа')
*/
const sheet = table.getSheets()[0];
/** Обращаемся к диапазону с данными на листе и получаем данные */
const data = sheet.getDataRange().getValues();
/** Определяем массив, в который будем класть индексы нужных строк*/
const rowNumbers = [];
/** В каждой строке проверяем значение из первого столбца*/
data.forEach((row, i) => {
if (row[0] == 'удалить') {
/** И, если, значение соответсвует условию - добавляем индекс в массив*/
rowNumbers.push(i);
}
})
/** Возвращаем массив с индексами строк, которые соответсвуют условию */
return rowNumbers;
}
Используем Sheets API
Для того, чтобы удалить все строки одним махом, да и вообще, для того чтобы сделать несколько действий с таблицей за один раз – прекрасно подходит метод Sheets API Sheets.Spreadsheets.batchUpdate(). Чтобы его использовать в скрипте – нужно обязательно подключить сервис. Слева в редакторе, напротив раздела “Сервисы”, есть иконка “+”. Нажимаем, находим “Google Sheets API”, выбираем и добавляем.
Сервис вызывается через класс Sheets и представляет собой обёртку для отправки запросов.
Построение запросов на обновление данных
Метод Sheets.Spreadsheets.batchUpdate() позволяет отправить и выполнить сразу несколько запросов на изменение таблицы, в том числе и на удаление строк с листа. Он кажется сложным из-за большой вложенности этих самых запросов, поэтому, для удобства напишем функцию buildDeleteRowsRequests(rowNumbers, sheetId) для их формирования.
/**
* Функция-фабрика для построения запросов на удаление строк,
* используемых в методе Sheets.Spreadsheets.batchUpdate()
* @param {Array<number>} rowNumbers - массив с индексами строк для удаления
* @param {number} sheetId - идентификатор листа, с которого нужно удалить строки
* @returns {Array<requests>} - массив сформированных запросов
*/
function buildDeleteRowsRequests(rowNumbers, sheetId) {
/** Сортируем индексы по убыванию */
rowNumbers = rowNumbers.sort((a,b)=> b-a);
const deleteDimensionRequests = [];
for (const rowNumber of rowNumbers) {
const deleteDimensionRequest = {
range: {
dimension: 'ROWS',
sheetId,
startIndex: rowNumber,
endIndex: rowNumber + 1
}
};
deleteDimensionRequests.push({ deleteDimension: deleteDimensionRequest });
}
return deleteDimensionRequests;
}
Порядок выполнения запросов к Sheets API
Давайте объясню зачем нужно сортировать строки. Запросы в этом методе выполняются поочередно, начиная с первого. Таким образом, если в первом запросе мы удалим строку из начала таблицы (сверху), то все строки сдвинутся и индексы перестанут соответствовать тем, которые определили при получении данных. Например, мы отправляем 2 запроса на удалений строк 1 и 15 из таблицы, в которой всего 15 строк. После удаления первой строки, следующий запрос не пройдет проверку потому что в таблице будет всего 14 строк, и мы получим ошибку о том, что диапазон запроса выходит за рамки самой таблицы. Поэтому, удаление нужно начинать в обратном порядке, с конца таблицы.
Отправка запроса к Sheets API
Итак, запросы для batchUpdate на удаление строк через АПИ мы сформировали, теперь осталось отправить сам запрос к АПИ Гугл таблиц
/**
* Функция для отправки запроса к Sheets API
* для удаления строк из таблицы
*/
function deleteRowsByApi() {
/** Получаем инстанс таблицы */
const table = SpreadsheetApp.getActive();
/** Получаем все листы таблицы и берем первый
* Можно получить лист по имени: table.getSheetByName('имя листа')
*/
const sheet = table.getSheets()[0];
/** Получаем массив с индексами строк для удаления */
const rowNumbers = getRowsForDelete();
/** Формируем массив объектов запроса на удаление строк из таблицы */
const deleteRowsRequests = buildDeleteRowsRequests(rowNumbers, sheet.getSheetId())
/** Отправляем запрос к Sheets API,
* в котором передаем сформированные запросы на удаление строк
*/
const result = Sheets.Spreadsheets.batchUpdate({
requests: deleteRowsRequests,
responseIncludeGridData: true
},
table.getId()
)
}
Заключение
В этой статье мы разобрали самый быстрый способ удаления множества строк из Гугл таблицы, порядок подключения сервиса Google Sheets API и его использования, научились формировать запросы к Sheets API для метода Sheets.Spreadsheets.batchUpdate() и выполнять их. Есть и другие способы удаления строк, о которых я написал в начале этой статьи. В таблице-примере, а точнее, в редакторе скриптов можно увидеть другой способ удаления строк – через штатный метод sheet.deleteRow(), он используется в функции “deleteRows”. Можете попробовать оба метода и сравнить скорость их выполнения.
Если знаете еще какие-то способы как массово удалить строки из Гугл таблицы – будем рады, если расскажете о них в комментариях.
На связи, команда GoogleSheets.ru. А еще мы помогаем с табличками в нашем чате в Телеграм
Этот подход явно лучше, чем контроль сортировки перед удалением на стороне клиента
Доброго дня
А можно как то написать простой запрос к API через POST
Чтобы туда передать с какого по какой ряд (строку) удалить из таблицы?
Чтобы его можно было вызвать как функцию в ячейке Google Sheets (так как теперь DeleteRows можно вызывать только через нажатие кнопки), а нужно чтобы вызывалось как формула в ячейке?
Добрый день! Технически, реализовать это можно. Но следует помнить о том, что если вызывать этот процесс из ячейки как формулу, то это будет не одно выполнение и строк удалиться много больше чем Вы ожидали.