如何更改运行功能的单元格的格式 [英] How to change format of cell that runs function

问题描述

如果我有以下用户定义的函数，我可以返回文本foo"...

If I have the following user defined function I can return the text "foo"...

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

如何通过代码为单元格指定红色背景?尽管这是一个简单的示例，但我的脚本更复杂，我想通过代码分配格式(我不想使用格式 > 条件格式...选项；我需要在单个脚本中配置所有内容.

How can I assign a red background to the cell through code? Even though it's a simple example, my script is more complex and I want to assign formatting through code (I don't want to use the Format > Conditional Formatting... option; I need to have everything configured in a single script.

在这方面有什么可以奏效的吗?

Is there something in the lines of this that could work?

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

getActiveCell 对我不起作用，因为它会触发点击(或激活)的单元格，而不一定是包含公式的单元格.

getActiveCell doesn't work for me because that triggers the clicked (or activated) cell and not necessarily the cell that contains the formula.

推荐答案

如果我没看错，您就是在使用公式语法调用应用程序脚本函数，方法是将 =myFunction() 放在单元格中在您的工作表中.

If I'm following correctly you are calling the apps script function using formula syntax, by putting =myFunction() in a cell in your sheet.

在这种情况下，没有基于脚本的方法将格式应用于包含公式的单元格.您不仅不会在函数中自动获得对父单元格的引用，而且您也无权调用 set 方法(例如 Range.setBackground())来自单元格公式内的自定义函数调用.

In this case, there is no script based method to apply formatting to the cell containing the formula. Not only are you not automatically given a reference to the parent cell in your function, you also do not have permission to call set methods (such as Range.setBackground()) from within a custom function call inside a cell formula.

在使用 Apps 脚本服务"标题下的文档中对此进行了说明:

This is noted in the documentation under the Using Apps Script services heading:

电子表格:只读(可以使用大多数 get*() 方法，但不能使用 set*()).无法打开其他电子表格(SpreadsheetApp.openById() 或SpreadsheetApp.openByUrl()).

Spreadsheet: Read only (can use most get*() methods, but not set*()). Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()).

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

您的自定义函数(当作为自动执行的公式放置在单元格中时)唯一能做的就是返回一个字符串或数组，然后将其显示在工作表中.

The only thing your custom function (when placed in a cell as an automatically executed formula) can do, is return either a string or array, which will then be displayed in the sheet.

这篇关于如何更改运行功能的单元格的格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！