Как изменить формат ячейки, в которой выполняется функция - Редактор скриптов Google Sheets

Question

Если у меня есть следующая пользовательская функция, я могу вернуть текст "foo"...

function myFunction(input){
   return "You wrote: "+ input;
}

Как я могу назначить красный фон для ячейки через код? Несмотря на то, что это простой пример, мой сценарий более сложный, и я хочу назначить форматирование с помощью кода (я не хочу использовать опцию "Формат"> "Условное форматирование..."; мне нужно настроить все в одном сценарии.

Есть ли что-то в этом, что может сработать?

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

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

Заранее спасибо!

Answer 1

Если я правильно понимаю, вы вызываете функцию, используя синтаксис пользовательской формулы, поставив =myFunction() в клетке на вашем листе.

В этом случае не существует метода на основе сценария для применения форматирования к ячейке, содержащей формулу. Вам не только автоматически не дают ссылку на родительскую ячейку в вашей функции, вы также не имеете разрешения на вызов set методы (такие как Range.setBackground()) из пользовательского вызова формулы.

Это отмечено в документации под заголовком "Использование скриптов приложений":

Электронная таблица: только для чтения (может использовать большинство методов get*(), но не set*()). Не удается открыть другие электронные таблицы (SpreadsheetApp.openById() или SpreadsheetApp.openByUrl ()).

https://developers.google.com/apps-script/guides/sheets/functions

Единственное, что может сделать ваша пользовательская формула, - это вернуть строку или массив, которые затем будут отображены на листе.

Answer 2

Если у вас есть запускаемый пользователем сценарий, вы можете назначить цвет ячейки с помощью кода, сохранив при этом все в сценарии.

Как форматировать ячейки с помощью кода в Google Таблицах

Во-первых: это правильно, как говорит @Cameron Roberts, что пользовательская функция, выполняемая автоматически как формула в ячейке, не имеет прав доступа для изменения форматирования каких-либо ячеек (включая ее собственную) или доступа к листу для изменения условного форматирования правила (поскольку Лист находится на более высоком уровне).

Я сосредоточился на ответах на выделенные здесь части:

Как с помощью кода назначить ячейке красный фон? Несмотря на то, что это простой пример, мой сценарий более сложен, и я хочу назначить форматирование с помощью кода (я не хочу использовать параметр Формат> Условное форматирование...; мне нужно, чтобы все было настроено в одном сценарии.

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

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

Вариант 1 - автоматически при открытии таблицы:

// This does not set the color of a user-selected cell like using getActiveCell does,
// but it necessarily relies on getActiveSheet(), since ranges are defined as referencing cells within sheets.
function setBackgroundOfScriptDeclaredCell() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A1");
  range.setBackground("red");
}

// Will run when user opens/refreshes spreadsheet.
function onOpen() {
  setBackgroundOfScriptDeclaredCell();
}

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

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

// Add custom menu to spreadsheet
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Run custom script')
      .addItem('Set background of a script-declared cell (A1)', 'setBackgroundOfScriptDeclaredCell')
      .addToUi();
}

// This does not set the color of a user-selected cell like using getActiveCell does, but it necessarily relies on getActiveSheet(), since ranges are defined as referencing cells within sheets.
function setBackgroundOfScriptDeclaredCell() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A1");
  range.setBackground("red");
}

Вариант 3 - установка правила условного форматирования с помощью скрипта.

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

function yourScript() {
  // ...
  var cellsInA1Notation = "A1"; // could also have been e.g. "C3:D4"
  setRangeToRedBackground(cellsInA1Notation);
  // ...
}

// This is a custom convenience function I made which is not provided directly by the Google Sheets API.
function addConditionalFormatRule(sheet, rule) {
  var rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules)
}

// Adds a conditional format rule to a sheet that causes a cell to have red background color if it contains a 1.
// To illustrate that conditional formatting rules do not need to be spread out across (and hidden) in the spreadsheet GUI,
// but can be manipulated entirely in your script.
function setRangeToRedBackground(cellsInA1Notation) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(cellsInA1Notation);
  var customFormulaString = "=A1=1";
  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(customFormulaString) 
    .setBackground("red")
    .setRanges([range])
    .build();
  addConditionalFormatRule(sheet, rule);
}

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

function foo() {
  return 1;
}

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

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

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

Я также написал об этом в своем блоге, где подробно остановился на двух моментах:

• Как будет выглядеть setRangeToRedBackground, если он реализован с использованием более общего SpreadsheetApp.newConditionalFormatRule().withCriteria функция, а не с использованием только специального случая .whenFormulaSatisfied?
• Возможно ли "Выполнять настраиваемые функции в настраиваемой формуле, используемой для условного форматирования, как в ячейке?"

Здесь: https://medium.com/@magne/how-to-format-cells-through-code-in-google-sheets-9d727ecc6053

Я надеюсь, что этот ответ может помочь кому-то еще, кто может наткнуться на эту проблему.

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