Сценарий Google Apps - Листы - Условное форматирование - X = выделение

Question

У меня есть лист с более чем 100000 ячеек (скоро будет более 300000 ячеек), используемый в качестве диаграммы Ганта. Каждая ячейка имеет формулу IF, которая возвращает букву X, если заголовок соответствующего столбца (дата) находится между датой начала и окончания строки.

Но что резко замедляет лист, так это условное форматирование. Условное форматирование говорит, что если значением ячейки является X, то измените цвет фона ячейки и цвет шрифта на зеленый. Если нет X, цвет фона должен быть белым. Поскольку условное форматирование, насколько я понимаю, пересчитывается каждый раз, когда вы вносите какие-либо изменения в лист, производительность существенно ухудшается. Поэтому моя мысль состоит в том, чтобы удалить условное форматирование и добавить его в качестве сценария с помощью кнопки меню, которую я могу щелкнуть в любое время, когда захочу, чтобы она выполнялась, в отличие от условного форматирования, запускаемого каждый раз, когда я выполняю редактирование листа.

Вот где я получил сценарий, который не работает. Я попробовал пару десятков вариантов этого, но не могу найти то, что работает - иногда я запускаю его без ошибок, иногда возникает ошибка. Мой контекст где-то не так?

function formatting() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  var range = ss.getRange("A1:A100");
  var cellValue = range.getValues();
  if (cellValue === 'X') {
    ss.range.setBackgroundColor('#000000'); }
  else {
    cellValue.setBackgroundColor('#ffffff'); }
}

Answer 1

Вот подход с использованием пакетной функции Range.setBackgrounds (). Эта функция принимает двумерный массив значений в качестве аргумента, позволяя вам установить все фоновые ячейки для диапазона в одном вызове API.

Также обратите внимание, что функция Range.getValues ​​() возвращает двумерный массив значений. Для проверки каждого отдельного значения ячейки вам нужно пройтись по массивам.

Поскольку вы имеете дело с 2D-массивом как с входом, так и с выходом, логика построения выходного массива фоновых значений отражает логику, которую вы должны использовать для проверки текущих значений ячеек. Таким образом, вы можете построить двумерный массив значений фона, пока вы просматриваете значения ячеек.

function setCellBackgrounds() {
  // The name of the sheet to process.
  var sheetName = "Sheet1";
  // The range of cells to inspect.
  var range = "A1:Z100";

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getRange(range);
  var values = range.getValues();
  var colors = [];
  for (var x = 0; x < values.length; x++) {
    colors[x] = [];
    for (var y = 0; y < values[x].length; y++) {
       if (values[x][y] == 'X') {
         colors[x][y] = '#999999';
       } else {
         colors[x][y] = '#ffffff';
       }
    }
  }
  range.setBackgrounds(colors);
}

Использование пакетных функций вместо неоднократных обращений к не пакетной версии является документированной передовой практикой скрипта приложений.

Answer 2

• cellValues является 2D массивом; каждая клетка должна быть проверена на предмет X, Запустите его в цикле, чтобы получить (x, y) тестируемого элемента.
• ни ss.range ни cellValue может быть использован для установки фона. Это должно быть ss.getRange(x, y).setBackground... используя координаты (x, y), чтобы указать X Ячейка

Не проверял, но это должно работать:

function formatting() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  var range = ss.getRange("A1:A100");
  for (var x = 0; x < range.length; x++) {
    for (var y = 0; x < range[0].length; y++) {
      if (cellValue == 'X') {
        ss.getRange(x, y).setBackgroundColor('#000000');
      } else {
        ss.getRange(x, y).setBackgroundColor('#ffffff');
      }
    }
  }
}

Хотя это может быть то, что вы ищете, есть проблемы со скоростью. Другим способом было бы просто добавить / удалить правило при желании. Может быть сделано программно с ConditionalFormatRule если это слишком много, чтобы воссоздать вручную часто.

Answer 3

Как упомянуто здесь, вы не ссылаетесь на чек правильно. Однако, используя setBackground на самом деле не поддается даже 100 клеток, а тем более 1000 или 300 000. Вы найдете это требование использовать пакетный метод Range#setBackgrounds() с "2D" массивом цветов фона, которые вы хотите применить.

Вы можете еще больше снизить потребность в массивных API-интерфейсах, используя подробные сведения о приложениях, касающиеся построения и работы электронной таблицы Ганта, чтобы уменьшить изменяемые диапазоны. Возможно, X могут появляться или исчезать только в только что отредактированной ячейке, или, возможно, они заполняются только слева направо или сверху вниз и т. Д.

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

function greenify() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Gantt");
  const HAS_X = "green", NO_X = null;
  const dr = sheet.getDataRange();
  const colors = dr.getBackgrounds();
  const VALUES = dr.getValues();

  // Inspect the value array and modify the corresponding index in colors.
  for (var r = 0, rows = VALUES.length; r < rows; ++r)
    for (var c = 0, cols = VALUES[0].length; c < cols; ++c)
      colors[r][c] = (VALUES[r][c] === "X") ? HAS_X : NO_X;

  // Write the output.
  dr.setBackgrounds(colors);
}

Если необходимо учитывать только что измененную ячейку, я рекомендую использовать "простой триггер".