Массово удаляем временные фильтры через Google Sheets API

Статья Михаила Смирнова, написанная для канала t.me/google_sheets

Чат канала: t.me/google_spreadsheets_chat

Другие статьи Миши, написанные для нашего канала: t.me/google_sheets/1203

Здравствуйте, товарищи!

Сейчас будем наводить в таблице порядок с FilterView и немного потрогаем Google Sheets API.

Про Filter View

Есть у нас таблица с данными. Можно нажать на воронку и включить фильтрацию — можно будет фильтровать и сортировать:

Это может быть неудобно, если несколько человек пользуются таблицей: одному на один набор данных надо смотреть, другому — на другой, придётся ждать своей очереди или мешать друг другу, так как фильтр меняет сортирует и прячет/показывает оригинальные данные.

Ещё владелец таблицы может отключить фильтр и защитить лист, чтоб коллеги его данные не трогали.

В таких случаях удобно пользоваться filter view:

Пользоваться им несложно:

1. Настраиваем фильтрацию, сортировку
2. Проверяем или устанавливаем диапазон применения фильтра
3. Даём ему имя, чтобы потом знать, какой фильтр мы повторно используем
4. Фильтр с этим именем появляется в списке, можно использовать повторно, не надо будет настраивать заново

Удобство в том, что оригинальные данные никак не меняются. Изменяется только отображение для пользователя, включившего filter view, на других пользователей никак не влияет.

Если фильтр сохранять не собираетесь, то после использования неплохо бы его удалить:

Но, многие не заморачиваются, или не знают, что можно удалить, в итоге случается загромождение, filter view копятся сотнями:

Google Sheets API

Вручную удалять — долго: каждый нужно включить, кликнуть в меню на Delete. Поэтому будем автоматизировать через Google Sheets API, который дёрнем из Google App Script.

Обойтись одним только Google App Script не получится, так как в нём нет функций для работы с filter view (когда-нибудь добавят).

Открываем IDE:

Добавляем функцию со стандартным именем onOpen() (она вызывается при открытии таблицы, срабатывает simple триггер), в которой создадим пользовательское меню:

Код:

function onOpen(e) {
  const ui = SpreadsheetApp.getUi();

  ui.createMenu('✨ 🎂 ✨')
    .addItem('Удалить временные фильтры на текущем листе', 'deleteTmpFilterViews')
    .addToUi();
}

Обновляем вкладку с таблицей, появляется пользовательское меню:

Понятно, кнопка не работает, так как функция, которую мы на неё повесили, пока не существует. Пишем функцию:

function deleteTmpFilterViews() {
  // Открытая таблица
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // Id таблицы для запросов через API
  const ssId = ss.getId();
  // Id открытого листа, только на нём будем удалять
  const sheetActiveId = ss.getActiveSheet().getSheetId();

  // Достаём информацию из таблицы: Id листов, Filter Views с каждого (название и Id)
  const response = Sheets.Spreadsheets.get(ssId, {
    fields: 'sheets/properties/sheetId,sheets/filterViews/title,sheets/filterViews/filterViewId',
  });

  // Ищем лист, соответствующий открытому
  let sheet;
  for (let s of response.sheets) {
    if (s.properties.sheetId !== sheetActiveId) continue;

    sheet = s;
    break;
  }

  // Если не нашли (очень странно), выходим с ошибкой
  if (sheet == null) {
    let msg = `В списке листов, полученных через API, нет листа c Id: ${sheetActiveId}`;
    throw new Error(msg);
  }

  // Если на листе нет FilterViews, то нечего делать, выходим
  if (sheet.filterViews == null) return;

  // Регулярка, по которой проверяем фильтры. Если название соответствует, то это временный, никому не нужный
  const deletePtrn = /^(?:Фильтр|Filter|Фільтр)\s+\d+$/i;

  // Собираем Id фильтров с соответствующими названиями для дальнейшего удаления
  const filterViewIdsToDelete = [];
  for (let fv of sheet.filterViews) {
    if (deletePtrn.exec(fv.title) !== null) {
      filterViewIdsToDelete.push(fv.filterViewId);
    }
  }

  // Если на листе нет FilterViews, попадающих под наш критерий, то нечего делать, выходим
  if (filterViewIdsToDelete.length === 0) return;

  // Запросы на удаление
  const requests = [];
  for (let fvId of filterViewIdsToDelete) {
    requests.push(
      {
        deleteFilterView: {
          filterId: fvId
        }
      }
    )
  }

  // Собираем общий запрос
  const batchRequest = {
    includeSpreadsheetInResponse: false,
    requests: requests
  };

  // Засылаем запрос, в ответ, мы просили, чтоб ничего не присылалось, так что по-разгильдяйски ничего обрабатывать не будем
  Sheets.Spreadsheets.batchUpdate(batchRequest, ssId);

  // Сообщаем тихонько о проделанной работе
  ss.toast(`Я удалил ${filterViewIdsToDelete.length} временных фильтров.`, 'Братан!', 3);
}

В коде комментарии, всё должно быть понятно. Можно только пару вещей уточнить:

• Выполняя запрос к Google Sheets API (Sheets.Spreadsheets.get(ssId, ...)), мы используем параметр fields — это field mask. Без него API вернёт просто всё, что имеет по вашей таблице, и данные в том числе — это слишком много, данные все не нужны. Через fields мы говорим, что нам достаточно получить Id листов, названия и Id filter view. Такой запрос гораздо быстрее выполнится.
• Работа будет вестись только по текущему листу: мы сравниваем Id открытого листа с Id, которые вернулись из API.
• Названия filter view мы проверяем регуляркой — так решаем, удалять или нет: если название начинается на слово Filter, Фильтр или Фільтр (спасибо, @volond, за добавление), после которого идёт пробел (или несколько), а после него число, тогда в список на удаление, нет — пропускаем.
• Составляем список запросов на удаление, указывая Id отобранных filter view.
• Засылаем сразу пачку запросов через batchUpdate() — так быстрее, нежели по одному дёргать.

Сохраняем, пробуем нажать на кнопку в меню, даём соответствующие разрешения, получаем ошибку:

Как я понял, это частая проблема, поэтому явно показал. Люди забывают включить в проекте Sheets API.

Включаем:

Запускаем ещё раз, скрипт успешно отрабатывает, о чём сообщает. Правда, фильтры остались:

На самом деле они удалены, просто список не обновился. Обновить его можно, обновив вкладку с таблицей, или попробовав добавить новый filter view:

На этом всё. Спасибо за внимание.

Ссылки

Google App Script

• Simple Triggers — простые триггеры
• Custom Menus — пользовательские меню
• Advanced Google services — включение других сервисов Google в App Script

Google Sheets API

• Overview — обзор Sheets API
• Reference — справочник
• Про работу с Field Masks статья и видео, статья в Slides API (но смысл везде одинаковый), описание в protobuf
• DeleteFilterViewRequest
• batchUpdate()

Статья Михаила Смирнова, написанная для канала t.me/google_sheets

Чат канала: t.me/google_spreadsheets_chat

Другие статьи Миши, написанные для нашего канала: t.me/google_sheets/1203