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

问题描述

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

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

如何通过代码为单元格分配红色背景?即使这是一个简单的示例，我的脚本也更加复杂，我想通过代码分配格式(我不想使用Format> Conditional Formatting ...选项;我需要在单个脚本中配置所有内容./p>

在这方面是否有可行的方法?

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

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

解决方案

如果我正确遵循的要求，则可以通过将=myFunction()放在工作表的单元格中，使用公式语法来调用apps脚本函数.

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

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

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

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

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

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 doesn't work for me because that triggers the clicked (or activated) cell and not necessarily the cell that contains the formula.

解决方案

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

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.

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

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屋！