Use Sheet Name as a Loop Parameter

Question

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

Daily Activity

Applications

Answer 1

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