UDF&的绝对参考在Google表格脚本中过滤数据 [英] Absolute reference with UDF & filtering data in google sheets script

问题描述

您好，我是脚本初学者，这是代码和 googlesheet 供参考

Hi I am begineer to scripts, here is the code and googlesheet for reference

我得到的是 我要实现的目标 /** *@param quest1 Question of the note *@param quest1 Answer of the note *@customfunction*/ function DMNOTE(quest1,ans1,quest2,ans2,quest3,ans3,) { var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var result = quest1+'-'+ans1+','+quest2+'-'+ans2+','+quest3+'-'+ans3; return result; }

What i am getting what i want to achieve /** *@param quest1 Question of the note *@param quest1 Answer of the note *@customfunction*/ function DMNOTE(quest1,ans1,quest2,ans2,quest3,ans3,) { var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var result = quest1+'-'+ans1+','+quest2+'-'+ans2+','+quest3+'-'+ans3; return result; }

我想获得"quest"参数的绝对引用，并且我希望它为其余的库伦循环，直到我进入该功能的列为止.在"Formula Required"列下，我已经将formla放在了引用中，这就是我想要的方式我的UDF可以正常工作.

I want to achieve absolute reference for "quest" parameter and i want it to loop for rest of the coulmns till column where i enter the function.Also under "Formula Required" column i have put formla for reference thats how i want my UDF to work.

接下来，我需要过滤非邀约协议"，并在其下仅保留否"，然后复制并复制.粘贴以蓝色高亮显示的所有列以更新选项卡.

Follwing up i need to filter "Non-solicit agreement" and keep only "No" under it and Copy & Paste all colums highlited in blue to update tab.

function toFilter (){
// filter and retain "no" in non-solicit agreement
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Worksheet");
ss.getRange(1,1,ss.getLastRow(),ss.getLastColumn());
var createfilter = SpreadsheetApp.newFilterCriteria().setHiddenValues("Yes").build();
ss.getFilter().setColumnFilterCriteria(8, createfilter);
}

希望我有道理.任何帮助都可以申请

Hope i make sense. Any help is appriciated

推荐答案

要使用任意数量的参数，您可以在脚本中使用以下功能

To work with any number of arguments, you can have a function like below in your script

function DMNOTE(...arg){
   let result = ''
   for(let i=0;i<arg.length;i++){
     result += `${arg[i]}-${arg[i+1]},`
     i++;
   }
   return result.substring(0, result.length - 1); 
}

然后形成电子表格，您可以使用=DMNOTE(A$1,A2,B$1,B2,C$1,C2)或=DMNOTE(A$1,A2,B$1,B2,C$1,C2,D$1,D2)调用该函数，该函数将处理所有传递的参数并返回结果.

And then form your spreadsheet you can call as =DMNOTE(A$1,A2,B$1,B2,C$1,C2) or =DMNOTE(A$1,A2,B$1,B2,C$1,C2,D$1,D2) The function will process all the arguments being passed and returns the result.

这篇关于UDF&的绝对参考在Google表格脚本中过滤数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！