如何更改运行功能的单元格的格式 - Google表格脚本编辑器 [英] How to change format of cell that runs function - Google Sheets Script Editor

问题描述

如果我有以下用户定义的函数，我可以返回文本foo...
$ b $ pre $ 函数myFunction（input ）{
return你写道：+ input;
}


如何通过代码为单元格分配红色背景？尽管这是一个简单的例子，但我的脚本更加复杂，我想通过代码分配格式（我不想使用格式>条件格式...选项;我需要将所有内容都配置在单个脚本中。 / p>

有没有什么可以工作的？

 函数myFunction（输入）{
 setBackground（'red'）; 
 return您写道：+ input; 
} 
   
 $ b 
 getActiveCell不适用于我，因为它会触发点击（或激活）的单元格，而不一定是包含公式的单元格。
 
 $ $ b $ p 
解决方案
如果我正确地遵循，则使用自定义公式语法调用该函数，通过将 = myFunction（）放置在工作表中的单元格中。
 
 
 
在这种情况下，没有脚本基于格式的方法将格式应用于包含公式的单元格。不仅是你您也没有权限调用 set 方法（例如 Range.setBackground（） 
 
 
 
在使用Apps脚本服务标题下的文档中可以看到这一点：
 
 
电子表格：只读（可以使用大多数get *（）方法，但不能设置*（））。无法打开
其他电子表格（SpreadsheetApp.openById（）或
 SpreadsheetApp.openByUrl（））。 
 
 < href =https://developers.google.com/apps-script/guides/sheets/functions#advanced =nofollow noreferrer> 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.

Thanks in advance!
解决方案
If I'm following correctly you are calling the function using Custom 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 formula call.

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 formula can do is return either a string or array, which will then be displayed in the sheet.

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