Фильтровать или скрыть строку

Question

[Назад история] У меня есть Google Sheet, который я использую для рассылки расписаний субподрядчикам. У каждого субподрядчика есть свой собственный лист, и у меня также есть последний лист, который называется MasterSchedule.

В основном графике используются ссылки на все отдельные листы, так что все графики субподрядчиков являются видимыми. Хотя это слишком долго. Поэтому я включил вспомогательный столбец во все листы, который просто возвращает значение true или false, указывающее, должна ли строка отображаться на главном экране.

Тем не менее, строка по-прежнему отображается на главном сервере, но вспомогательный столбец просто говорит ложь. Поэтому я использовал Автофильтр, чтобы скрыть его.

TLDR:

Проблема: Google Apps Script не имеет API для автоматического фильтра. Как VBA, используя критерии и т. Д. Так что единственный вариант, который я вижу, это скрыть строки. Но это очень медленно. Я знаю, что идея состоит в том, чтобы уменьшить количество обращений к службам Google, и Google предлагает создать массив, а затем исключить вызов в массиве. Я понятия не имею, как это сделать.

Мне нужен эффективный скрипт / функция для просмотра столбца и каждой ячейки, которая читает false, функция скроет всю строку и покажет все остальные строки.

Самый быстрый метод без использования сценариев - использовать версию автофильтра Google Sheets и просто снять флажок false.

Я попытался создать цикл for, который читает каждую ячейку в столбце и на каждой итерации скрывает строку, если значение ячейки равно false. Это невероятно медленно.

Увидеть:

function MasterFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var maxRows = sheet.getMaxRows();

  //show all the rows
  sheet.showRows(1, maxRows);

  //get data from column B
  var data = sheet.getRange('B:B').getValues();

  //iterate over all rows
  for(var i=5; i< data.length; i++){
    if(sheet.getRange(i,2).getValue() == false){
      sheet.hideRow(i);
    }
  }
}

Answer 1

Мне пришлось изменить одну строку вашего исходного кода, чтобы заставить его работать:

  sheet.hideRow(sheet.getRange(i,2));  // Operates on a range

После этого я рассчитал время в тестовой таблице, ~200 строк с равномерным распределением true / false клетки. Отчет о выполнении отчета [4.656 seconds total runtime],

Как вы и подозревали, вы можете уменьшить количество вызовов Службы, используя массив с набором данных (в данном случае это весь столбец B). Вот метод "hide", использующий массив для проверки видимости:

function MasterFilter() {
  var headers = 4; // # rows to skip
  var sheet = SpreadsheetApp.getActiveSheet();
  var maxRows = sheet.getMaxRows();

  //show all the rows
  sheet.showRows(1, maxRows);

  //get data from column B
  var data = sheet.getRange('B:B').getValues();

  //iterate over all rows
  for(var i=headers; i< data.length; i++){
    if(data[i][0] == false){
      sheet.hideRow(sheet.getRange(i+1,1));
    }
  }
}

Согласно протоколу исполнения, это заняло [0.106 seconds total runtime] для тестового листа. Наблюдая за экраном, к моменту обновления оно выглядело как 3-4 секунды.

Вот еще один способ сделать это. В этом, мы читаем все данные на мастер-листе, а затем используем Array.filter() чтобы уменьшить двумерный массив только строки true в столбце B. Этот меньший набор затем записывается в основной лист после того, как мы очистим все предыдущее содержимое. Это сократило время выполнения в два раза, [0.059 seconds total runtime] согласно стенограмме исполнения. Еще раз, задержки обновления просматриваемой копии сделали ее похожей на пару секунд.

// Evaluation function for Array.filter()
function isVisible(row) {
  return (row[1] === true);  // Check column B
}

function MasterFilter2() {
  var headers = 4; // # rows to skip
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headerData = data.splice(0,headers); // Skip header rows
  var filteredData = data.filter( isVisible );
  var outputData = headerData.concat(filteredData);  // Put headers back

  sheet.clearContents();  // Clear content, keep format

  // Save filtered values
  sheet.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData); 
}