В Google Таблицах вы можете установить цвет ячейки, вызвав пользовательскую функцию в этой ячейке, выполнив что-то вроде:

function myFunction(input){
   this.setBackground('red');
   return "You wrote: "+ input;
}

А затем в ячейке электронной таблицы вызовите ее как пользовательскую функцию формулы:

=myFunction("some text.")

Но задать цвет ячейки с помощью такой функции невозможно по разным причинам. Одна из причин заключается в том, что не существует эквивалента this, который бы ссылался на вызывающую ячейку, что позволяет пользовательской функции естественным образом ссылаться на нее. (Однако см. внизу этой статьи странный лайфхак, который я обнаружил.) Хотя было бы неплохо сделать this.setBackground('red'). Но (внутри вызывается пользовательская функция) ячейка не имеет прав доступа для вызова сеттеров, только геттеров. Так что в любом случае вы получите Исключение: у вас нет разрешения на вызов setBackground, когда ячейка вызовет функцию. Хотя было бы разумно разрешить ячейке доступ к простому установщику форматирования, по крайней мере, для самой себя (Гугл, ты слушаешь?).

Итак, каковы наши варианты назначения форматирования с помощью пользовательской функции в скрипте Google Apps?

Вариант 1 — автоматически при открытии электронной таблицы

Вы можете сделать описанное выше, но вы не можете установить ячейку в =setBackgroundOfScriptDeclaredCell(), чтобы вызвать эту пользовательскую функцию из ячейки. Это выдаст Исключение: у вас нет разрешения на вызов setBackground. Из-за упомянутых выше ограничений привилегий доступа. того, к чему разрешен доступ пользовательской функции в ячейке (только геттеры, а не сеттеры). См.: https://developers.google.com/apps-script/guides/sheets/functions#advanced.

Вариант 2 — Когда пользователь инициирует ваш скрипт

Вариант 3 — Установка правила условного форматирования через ваш скрипт

Вы можете установить правила условного форматирования через свой скрипт, что позволит вам сохранить все настройки в одном скрипте: https://developers.google.com/apps-script/reference/spreadsheet/conditional-format-rule

Этот сценарий может выполняться автоматически при открытии электронной таблицы или когда пользователь инициирует ваш сценарий (например, через пункт меню).

Сделав это, вы можете сделать такую ​​​​функцию, как:

function foo() {
  return 1;
}

и поместите его в A1. Затем, когда foo() выполняется в A1 и возвращает 1, тогда A1 изменит цвет фона на красный. Это, конечно, предполагает, что вы знаете результат, который будет выведен foo(). Правило условного форматирования для A1 уже будет установлено сценарием, когда пользователь откроет электронную таблицу.

Преимущество подобных действий заключается в том, что это показывает, что вы можете управлять форматированием ячейки с помощью своего скрипта, даже правилами условного форматирования (наиболее продвинутые из которых называются «настраиваемые формулы»). Это выгодно, если вы хотите содержать в своем сценарии каждый бит довольно сложного кода (включая сложные пользовательские формулы). Возможно, вы хотите иметь возможность легко распространять свой сценарий. Или, может быть, вы просто хотите, чтобы все было в одном месте. Вы также избегаете разбросанной и скрытой сложной логики в графическом интерфейсе Google Sheets.

Если бы вы использовали графический интерфейс для управления своими пользовательскими формулами, используемыми для условного форматирования, то для просмотра и управления этой логикой вам нужно было бы найти и выбрать правильную ячейку или выбрать всю электронную таблицу, а затем щелкнуть правой кнопкой мыши и выбрать «Условное форматирование». », или перейдите в меню и выберите «Формат -> Условное форматирование», чтобы увидеть и отредактировать эту логику. Что иногда может быть весьма неудобно.

Выполнять пользовательские функции в пользовательской формуле, используемой для условного форматирования, как в ячейке?

К сожалению, опция «Пользовательская формула» в Google Sheets не выполняет пользовательские функции, хотя графический интерфейс говорит, что пользовательская формула может принимать «значение или формулу». Таким образом, вы не можете поставить:

=foo()=1

здесь и ожидайте, что он запустится. Но вы, конечно, можете сделать

=B2=1

а затем поместите вызов пользовательской функции =foo() в B2, который будет выполняться электронной таблицей как обычно.

Как получить ссылку this на вызывающую ячейку? Странный хак.

Если у вас есть ячейка с =test(), вы можете сделать:

function test() { 
 return SpreadsheetApp.getActiveSheet().getCurrentCell().getA1Notation();
}

Это фактически выведет собственную ссылку ячейки, поэтому в ячейке B2 будет выведено «B2». Таким образом, похоже, что getCurrentCell() можно использовать как эквивалент this в Google Таблицах.

НБ! Но это, вероятно, хакерская/подрывная версия функции getCurrentCell(), поскольку getCurrentCell() не является фактическим эквивалентом this. Таким образом, использование его таким образом может нарушить его задокументированную цель: 'Текущая ячейка — это ячейка, которая находится в фокусе в пользовательском интерфейсе Google Таблиц и выделена темной рамкой. ... Когда пользователь выбирает один или несколько диапазонов ячеек, одна из выбранных ячеек является текущей ячейкой». developers.google.com/apps-script/reference/spreadsheet/… Но для некоторых причина, по которой это работает как thisкогда пользовательская функция вызывается из ячейки. И результат остается прежним, даже если пользователь может выбирать другие диапазоны ячеек на листе. Возможно, потому что текущая ячейка рассматривается как выбранная ячейка в момент времени, когда функция была вызвана из ячейки. Если кто-то знает, почему, не стесняйтесь поделиться этим знанием в комментарии!

Небольшое примечание: getActiveCell()тоже работает в этом сценарии так же, как getCurrentCell(). Но getActiveCell() устарел, вероятно, потому что он ошибочно всегда относится к верхней левой ячейке в выборе, даже если выбор группы ячеек сделан, начиная с любого другого угла (например, из нижнего правого угла).

Ключевые слова: Google Sheets, электронные таблицы, JavaScript, скрипт Google Apps.

Альтернативные точки входа: «Как через код назначить ячейке красный фон?», «Я хочу задать форматирование через код», «Мне нужно, чтобы все было настроено в одном скрипте».

Источник вдохновения: https://stackoverflow.com/questions/48195002/how-to-change-format-of-cell-that-runs-function-google-sheets-script-editor