使用图纸名称作为循环参数 [英] Use Sheet Name as a Loop Parameter
问题描述
我有一个电子表格，我称之为应用程序，它是提供给客户的服务时间表以及这些服务提供的日期。 我还有另一个名为每日活动的电子表格，我试图为每个客户创建一个汇总成本和服务。日常活动电子表格为每个客户端都有一个标签，每个客户端的标签名称都是一个代码，用于标识应用程序电子表格中的客户端。我需要收集每天和每个客户提供的服务数量，以便我可以为每个客户创建成本和服务的摘要，以便我可以更好地了解客户的客户盈利情况。 / b>
我的进度和问题：我已经成功地利用Google脚本来收集所需的信息，最大执行时间问题。我认为我的问题是我必须为每个客户端编写一个单独的脚本，因为我不知道如何将它整合到一个脚本中。
我注意到了我可以用一个像这样的函数获取所有表单的数组：
$ $ $ $ $ $ $ $ $ $ $ $ $函数循环表= SpreadsheetApp.openById（id）;
var sheets = ss.getSheets（）;
var sh = [];
for（i = 0; i< sheets.length; i ++）{
var newSh = sheets [i] .getName（）;
sh.push（newSh）;
}
返回sh;
}
但我不知道如何将它集成到我的脚本中这样我就可以为所有客户执行此过程的一个脚本。
以下是真正计数的脚本
此脚本扫描应用程序电子表格，这是一个服务时间表在给定的时间段内为每个客户提供。此脚本需要每天运行，以便将所有必需的数据记录到Daily Activity电子表格中。
函数countSheet1（）{
var ss = SpreadsheetApp.openById（ID）;
var cntSh = ss.getSheetByName（'Applications'）;
var cntRg = cntSh.getDataRange（）;
var vA = cntRg.getValues（）;
var today = new Date（）;
var toD = Utilities.formatDate（new Date（today），Session.getScriptTimeZone（），dd / MM / yyyy）;
var count = 0;
var rowA = [];
var rowB = [];
for（i = 2; i
rowB = vA [i] [1];
var rowBb = Utilities.formatDate（new Date（rowB），Session.getScriptTimeZone（），dd / MM / yyyy）;
Logger.log（rowBb）;
if（rowA ==Sheet1&&& rowBb == toD）{
count ++;
}
}
return count;
这是与上述相同的脚本，除了不同的客户端 function countSheet2（）{
var ss = SpreadsheetApp.openById（ID）;
var cntSh = ss.getSheetByName（'Applications'）;
var cntRg = cntSh.getDataRange（）;
var vA = cntRg.getValues（）;
var today = new Date（）;
var toD = Utilities.formatDate（new Date（today），Session.getScriptTimeZone（），dd / MM / yyyy）;
var count = 0;
var rowA = [];
var rowB = [];
for（i = 2; i
rowB = vA [i] [1];
var rowBb = Utilities.formatDate（new Date（rowB），Session.getScriptTimeZone（），dd / MM / yyyy）;
Logger.log（rowBb）;
if（rowA ==Sheet2&&& rowBb == toD）{
count ++;
}
}
return count;
$ / code>
这是写在每张纸上的脚本...
函数runScript（）{
函数toDateFormat（date）{
try {return date .setHours（0,0,0,0）;}
catch（e）{return;}
}
var sheet1 = SpreadsheetApp
.openById（ ID）
.getSheetByName（Sheet1）;
var sRange = sheet1.getDataRange（）;
var values = sRange.getValues（）;
var newT = new Array（）;
var nr = 0;
$ b $ for（i = 0; i< values.length; i ++）{
if（values [i] [1] =='总申请数量'）{
nr = i;
newT.push（nr）;
}
}
var client = newT [0];
var app = newT [1];
var today = toDateFormat（new Date（））;
var todaysColumn = values [3] .map（toDateFormat）.map（Number）.indexOf（+ today）;
sheet1.getRange（client + 1，todaysColumn + 1,1,1）.setValue（countSheet1（））;
// Sheet2
var sheet2 = SpreadsheetApp
.openById（ID）
.getSheetByName（Sheet2）;
var sRange = sheet2.getDataRange（）;
var values = sRange.getValues（）;
var newT = new Array（）;
var nr = 0;
for（i = 0; i< values.length; i ++）{
if（values [i] [1] =='总申请数量'）{
nr = i ;
newT.push（nr）;
}
}
var client = newT [0];
var app = newT [1];
var today = toDateFormat（new Date（））;
var todaysColumn = values [3] .map（toDateFormat）.map（Number）.indexOf（+ today）;
sheet2.getRange（client + 1，todaysColumn + 1,1,1）.setValue（countSheet2（））;
$ b countSheet1函数扫描应用程序电子表格，计算Sheet1的出现次数（列A）和今天的日期（B栏）。然后，它将该计数保存在日常活动下的工作表1选项卡中，并且必须为每个客户端添加更多脚本。
我想知道是否有可能可以使用getSheets（）数组创建一个脚本来执行整个操作。
由于数据保护，我无法访问原始文件。 >我仍然在努力提高自己的JavaScript和GAS知识（特别是我努力的数组）....
非常感谢！
LE 我添加了2个电子表格作为模板
解决方案提供的服务数量从申请表中提供
此版本可以正常工作
我使用键/值对创建了一个对象，其中的关键字是一个选项卡或工作表名称（也是客户端ID）。我对代码进行了一些更改。我认为这是行得通的，但我会让你成为法官。我在代码中留下了一些调试屏幕。我喜欢他们，因为他们比记录员更容易看到我。但是，这可能只是一个老家伙的问题。
$ b $ pre $ 函数countSheets（）
{
var ss = SpreadsheetApp.openById（ 'ID'）;
var sh = ss.getSheetByName（'Applications'）;
var rg = sh.getDataRange（）;
var vA = rg.getValues（）;
var td = Utilities.formatDate（new Date（），Session.getScriptTimeZone（），dd / MM / yyyy）;
var mbs = myBullSheets（）;
// var s ='';
for（var i = 2; i< vA.length; i ++）
{
var d = Utilities.formatDate（new Date（vA [i] [1]），Session.getScriptTimeZone （），dd / MM / yyyy）;
for（var key in mbs）
{
//Logger.log（'\\\
%s %%s&&％s ==％s'，vA [ I] [0]，键，d，TD）;
//s+=Utilities.formatString('<br />％s ==％s&&&％s ==％s'，vA [i] [0]，key，d，td ）;
if（vA [i] [0] == key&& d == td）
{
mbs [key] + = 1;
//Logger.log（'\\\
mbs[%s]=%s',key,mbs[key]）;
//s+=Utilities.formatString('\\\
mbs[%s]=%s',key,mbs[key]）;
}
}
}
// var ui = HtmlService.createHtmlOutput（s）;
//SpreadsheetApp.getUi().showModelessDialog (ui，'My Logs'）;
返回mbs;
}
函数myBullSheets（）
{
var ss = SpreadsheetApp.openById（'ID'）;
var allshts = ss.getSheets（）;
var mbs = [];
for（var i = 0; i< allshts.length; i ++）
{
mbs [allshts [i] .getName（）] = 0;
}
返回mbs;
}
我希望这可以帮助您。
感谢您的数据。这帮助了一大堆。
顺便说一句。你需要这样的东西来查看记录器中的输出。
function testcountSheets（）
{
var mbs = countSheets（）;
for（var key in mbs）
{
Logger.log（'\ nmbs [％s] =％s'，key，mbs [key]）;
$ / code $
$ h $这会将计数加载到日常活动的选项卡中电子表格
这可能是您要查找的内容。我还没有测试过。但是一旦我们能够实现它，我怀疑它会运行得更快。
function runScript（）
{
var ss = SpreadsheetApp.openById（'ID'）;
var mbs = countSheets（）;
for（var key in mbs）
{
var sh = ss.getSheetByName（key）;
var rg = sh.getDataRange（）;
var vA = rg.getValues（）;
for（var i = 0; i< vA.length; i ++）
{
if（vA [i] [1] =='总申请数量'）
{
var nr = i;
休息; //一旦我们找到一场比赛，我们应该得到改善的表现，终止。这是你不能在地图上做的事情。
if（typeof（nr）！='undefined'）//如果我们没有找到匹配，那么这个是未定义的
{
var今天= new Date（）。setHours（0,0,0,0）;
for（var i = 0; i< vA [3] .length; i ++）
{
if（vA [3] [i]）//此范围内的某些单元格没有内容
{
if（today.valueOf（）== new Date（vA [3] [i]）。valueOf（））
{
sh.getRange（nr + 1，I + 1,1,1）.setValue（编号（MBS [键]））;
}
}
}
}
}
}
哇！从超过最大执行时间到25秒...我喜欢那个
DESCRIPTION:
I have a spreadsheet I call 'Applications' which is a schedule of services provided to clients and the dates these services were provided. I also have another spreadsheet called 'Daily Activity' in which I'm trying to create a summary costs and services provided for each client. The Daily Activity Spreadsheet has a tab for each client and the tabname for each client is a code which identifies the client in the Applications spreadsheet. I need to collect the number of services provided for each day and for each client so that I can create a summary of costs and services provided for each client so that I can get a better understanding of my profitability on a client by client basis.
MY PROGRESS AND PROBLEM:
I have been successful in utilizing Google Script to collect the desired information but I keep running into the maximum execution time problem. I think the problem for me is that I have to write a separate script for each client because I don't know how to integrate it all into one script.
I noticed that I can get an Array of all sheets with a function like this:
function loopingSheets() {
var ss = SpreadsheetApp.openById("id");
var sheets = ss.getSheets();
var sh = [];
for (i=0; i<sheets.length; i++) {
var newSh = sheets[i].getName();
sh.push(newSh);
}
return sh;
}
But I don't have the knowledge of how to integrate that into my scripts so that I can have just one script that performs the process for all clients.
Here are the scripts that really count
This script scans through the Applications Spreadsheet which is a schedule of services provided for each client over a given period of time. This script needs to be run every day so that the all of the required data can be recorded into the Daily Activity spreadsheet.
function countSheet1() {
var ss=SpreadsheetApp.openById("ID");
var cntSh=ss.getSheetByName('Applications');
var cntRg=cntSh.getDataRange();
var vA=cntRg.getValues();
var today = new Date();
var toD = Utilities.formatDate(new Date(today), Session.getScriptTimeZone(), "dd/MM/yyyy");
var count=0;
var rowA = [];
var rowB = [];
for (i=2; i<vA.length; i++) {
rowA = vA[i][0];
rowB = vA[i][1];
var rowBb = Utilities.formatDate(new Date(rowB), Session.getScriptTimeZone(), "dd/MM/yyyy");
Logger.log(rowBb);
if(rowA == "Sheet1" && rowBb == toD) {
count++;
}
}
return count;
}
This is the same script as above except for a different client
function countSheet2() {
var ss=SpreadsheetApp.openById("ID");
var cntSh=ss.getSheetByName('Applications');
var cntRg=cntSh.getDataRange();
var vA=cntRg.getValues();
var today = new Date();
var toD = Utilities.formatDate(new Date(today), Session.getScriptTimeZone(), "dd/MM/yyyy");
var count=0;
var rowA = [];
var rowB = [];
for (i=2; i<vA.length; i++) {
rowA = vA[i][0];
rowB = vA[i][1];
var rowBb = Utilities.formatDate(new Date(rowB), Session.getScriptTimeZone(), "dd/MM/yyyy");
Logger.log(rowBb);
if(rowA == "Sheet2" && rowBb == toD) {
count++;
}
}
return count;
}
And this is the script that writes that count on each sheet ...
function runScript() {
function toDateFormat(date) {
try {return date.setHours(0,0,0,0);}
catch(e) {return;}
}
var sheet1 = SpreadsheetApp
.openById("id")
.getSheetByName("Sheet1");
var sRange = sheet1.getDataRange();
var values = sRange.getValues();
var newT = new Array();
var nr = 0;
for (i = 0; i < values.length; i++){
if (values[i][1]=='Total Number of Applications'){
nr = i;
newT.push(nr);
}
}
var client = newT[0];
var app = newT[1];
var today = toDateFormat(new Date());
var todaysColumn = values[3].map(toDateFormat).map(Number).indexOf(+today);
sheet1.getRange(client+1,todaysColumn+1,1,1).setValue(countSheet1());
//Sheet2
var sheet2 = SpreadsheetApp
.openById("ID")
.getSheetByName("Sheet2");
var sRange = sheet2.getDataRange();
var values = sRange.getValues();
var newT = new Array();
var nr = 0;
for (i = 0; i < values.length; i++){
if (values[i][1]=='Total Number of Applications'){
nr = i;
newT.push(nr);
}
}
var client = newT[0];
var app = newT[1];
var today = toDateFormat(new Date());
var todaysColumn = values[3].map(toDateFormat).map(Number).indexOf(+today);
sheet2.getRange(client+1,todaysColumn+1,1,1).setValue(countSheet2());
}
The countSheet1 function scans through the Applications spreadsheet counting the occurence of the Sheet1 (Column A) and todays date (Column B). It then saves that count in Daily Activity under the tab 'Sheet 1' and I have to keep adding more scripts for each client.
I wonder if there is a possibility that the getSheets() array can be used to create one script that performs the entire operation.
I won't be able to provide access to the original file due to data protection ....
I'm still trying to improve my JavaScript and GAS knowledges (especially arrays which I struggle with) ....
Many thanks !!!
L.E. I have added 2 spreadsheets as templates
解决方案 The provides count of Services Provided from Application sheet
This version works okay
I created an object with a key/value pair where the key is a tab or sheet name (also a client id). I made a few changes to the code. I think it's working but I'll let you be the judge. I left some of my debug screens in the code. I like them because they're a little easier for me to see than the logger. But that may just be an old guy problem.
function countSheets()
{
var ss=SpreadsheetApp.openById('ID');
var sh=ss.getSheetByName('Applications');
var rg=sh.getDataRange();
var vA=rg.getValues();
var td = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd/MM/yyyy");
var mbs=myBullSheets();
//var s='';
for (var i=2;i<vA.length;i++)
{
var d = Utilities.formatDate(new Date(vA[i][1]), Session.getScriptTimeZone(), "dd/MM/yyyy");
for(var key in mbs)
{
//Logger.log('\n%s==%s && %s==%s',vA[i][0],key,d,td);
//s+=Utilities.formatString('<br />%s==%s && %s==%s',vA[i][0],key,d,td);
if(vA[i][0]==key && d==td)
{
mbs[key]+=1;
//Logger.log('\nmbs[%s]=%s',key,mbs[key]);
//s+=Utilities.formatString('\nmbs[%s]=%s',key,mbs[key]);
}
}
}
//var ui=HtmlService.createHtmlOutput(s);
//SpreadsheetApp.getUi().showModelessDialog(ui, 'My Logs');
return mbs;
}
function myBullSheets()
{
var ss=SpreadsheetApp.openById('ID');
var allshts=ss.getSheets();
var mbs=[];
for(var i=0;i<allshts.length;i++)
{
mbs[allshts[i].getName()]=0;
}
return mbs;
}
I hope this helps you out.
Thanks for the data. That helped a bunch.
BTW..You'll need something like this to view the output in the logger.
function testcountSheets()
{
var mbs=countSheets();
for(var key in mbs)
{
Logger.log('\nmbs[%s]=%s',key,mbs[key]);
}
}
This loads the counts into the tabs of the Daily Activity Spreadsheet
Which is probably what your looking for. I haven't tested this at all. But once we can get it to work I suspect it will run considerably faster.
function runScript()
{
var ss=SpreadsheetApp.openById('ID');
var mbs=countSheets();
for(var key in mbs)
{
var sh=ss.getSheetByName(key);
var rg=sh.getDataRange();
var vA=rg.getValues();
for(var i=0;i<vA.length;i++)
{
if(vA[i][1]=='Total Number of Applications')
{
var nr=i;
break;//by terminating as soon as we find a match we should get improved performance. Which is something you cant do in a map.
}
}
if(typeof(nr)!='undefined')//If we don't find a match this is undefined
{
var today=new Date().setHours(0,0,0,0);
for(var i=0;i<vA[3].length;i++)
{
if(vA[3][i])//Some cells in this range have no contents
{
if(today.valueOf()==new Date(vA[3][i]).valueOf())
{
sh.getRange(nr+1,i+1,1,1).setValue(Number(mbs[key]));
}
}
}
}
}
}
Wow! From exceeds max execution time to 25 seconds...I like that
这篇关于使用图纸名称作为循环参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
