Building A Report

Original Question:

I need a script to count each agent email address (overall and based on client). I am struggling with arrays. I am new on scripting so still learning. So, on the Reports tab I need to know how many calls an agent made, then how many calls he made for Client 1, Client 2 and so on, how many appointments made - overall and for each client. I don't want to use =COUNTIFS() as this will be dynamic and in time there will be new agents.

I will make a script to get unique emails and paste it into a table and from there I need this script to count.

In the shared file I have my script, but I stuck there.

Thank you!

This is what the spreadsheet looked like.

This is what the questioner wanted the report to look like:

The Code

function reportSummary()//This is final output

{

var br='<br />';

var agentA=uniqueItemArray(4);

var clientA=uniqueItemArray(5);

var clientAgents=emailAddressAssociatedWithEachClient();

var agentClients=clientsAssociatedWithEachEmail();

var callsByAgent=callsMadeByAgentTotal();

var callsByAgentToClient=callsMadeByAgentToClient();

var apptsByAgent=apptsBookedByAgentTotal();

var apptsByAgentForClient=apptsBookedByAgentForClient();

var ss=SpreadsheetApp.getActive();

var rptSh=ss.getSheetByName('Reports');

var rgA=["A2:C6","A8:C12","A14:C18"];

for(var i=0;i<rgA.length;i++)

{

var rg1=rptSh.getRange(rgA[i]);

var vA1=rg1.getValues();

vA1[2][1]=callsByAgentToClient[vA1[2][0]][vA1[0][0]];

vA1[3][1]=callsByAgentToClient[vA1[3][0]][vA1[0][0]];

vA1[4][1]=callsByAgentToClient[vA1[4][0]][vA1[0][0]];

vA1[2][2]=apptsByAgentForClient[vA1[2][0]][vA1[0][0]];

vA1[3][2]=apptsByAgentForClient[vA1[3][0]][vA1[0][0]];

vA1[4][2]=apptsByAgentForClient[vA1[4][0]][vA1[0][0]];

rg1.setValues(vA1);

}

var rg2=rptSh.getRange('E2:H6');

var vA2=rg2.getValues();

vA2[2][1]=callsByAgent[vA2[2][0]];

vA2[3][1]=callsByAgent[vA2[3][0]];

vA2[4][1]=callsByAgent[vA2[4][0]];

vA2[2][2]=apptsByAgent[vA2[2][0]];

vA2[3][2]=apptsByAgent[vA2[3][0]];

vA2[4][2]=apptsByAgent[vA2[4][0]];

rg2.setValues(vA2);

/*

var s='<strong>Clients Contacted by Agent:</strong>';

for(var i=0;i<agentA.length;i++)

{

s+=br + '<span style="color:#ff0000">' + agentA[i] + '</span>';

for(var j=0;j<agentClients[agentA[i]].length;j++)

{

s+=br + agentClients[agentA[i]][j];

}

}

s+=br + br + '<strong>Agents who Contacted Client:</strong>';

for(var i=0;i<clientA.length;i++)

{

s+=br + '<span style="color:#ff0000">' + clientA[i] + '</span>';

for(var j=0;j<clientAgents[clientA[i]].length;j++)

{

s+=br + clientAgents[clientA[i]][j];

}

}

s+=br + br + '<strong>Calls made by Agents:</strong>';

for(var i=0;i<agentA.length;i++)

{

s+=br + Utilities.formatString('<br />%s = %s',agentA[i],callsByAgent[agentA[i]]);

}

s+=br + br + '<strong>Calls made by Agents to Client:</strong>';

for(var i=0;i<agentA.length;i++)

{

for(var j=0;j<clientA.length;j++)

{

s+=br + Utilities.formatString('<br />%s<br />%s<br />%s',agentA[i],clientA[j],callsByAgentToClient[agentA[i]][clientA[j]])

}

}

var userInterface=HtmlService.createHtmlOutput(s);

SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Report Summary');

*/

}

function uniqueItemArray(column)

{

var ss=SpreadsheetApp.getActive();

var cntSh=ss.getSheetByName('Count');

var cntRg=cntSh.getDataRange();

var vA=cntRg.getValues();

var uiA=[];

for(var i=2;i<vA.length;i++)

{

if(uiA.indexOf(String(vA[i][column-1]).trim())==-1)

{

uiA.push(vA[i][column-1]);

}

}

return uiA;

}

function emailAddressAssociatedWithEachClient()

{

var agentEmailsA=uniqueItemArray(4);

var clientA=uniqueItemArray(5);

var ss=SpreadsheetApp.getActive();

var cntSh=ss.getSheetByName('Count');

var cntRg=cntSh.getDataRange();

var vA=cntRg.getValues();

var cEO=[];

for(var i=0;i<clientA.length;i++)

{

var ceA=[];

for(var j=2;j<vA.length;j++)

{

if(ceA.indexOf(String(vA[j][3]).trim())==-1 && String(vA[j][4]).trim()==clientA[i])

{

ceA.push(vA[j][3]);

}

}

cEO[clientA[i]]=ceA;

}

/*

for(var n=0;n<clientA.length;n++)

{

Logger.log(clientA[n] + '\n' + cEO[clientA[n]]);

}

*/

return cEO;

}

function clientsAssociatedWithEachEmail()

{

var agentEmailsA=uniqueItemArray(4);

var clientA=uniqueItemArray(5);

var ss=SpreadsheetApp.getActive();

var cntSh=ss.getSheetByName('Count');

var cntRg=cntSh.getDataRange();

var vA=cntRg.getValues();

var cEO=[];

for(var i=0;i<agentEmailsA.length;i++)

{

var ceA=[];

for(var j=2;j<vA.length;j++)

{

if(ceA.indexOf(String(vA[j][4]).trim())==-1 && String(vA[j][3]).trim()==agentEmailsA[i])

{

ceA.push(vA[j][4]);

}

}

cEO[agentEmailsA[i]]=ceA;

}

/*

for(var n=0;n<agentEmailsA.length;n++)

{

Logger.log(agentEmailsA[n] + '\n' + cEO[agentEmailsA[n]]);

}

*/

return cEO;

}

function callsMadeByAgentTotal()

{

var agentA=uniqueItemArray(4);

var clientA=uniqueItemArray(5);

var ss=SpreadsheetApp.getActive();

var cntSh=ss.getSheetByName('Count');

var cntRg=cntSh.getDataRange();

var vA=cntRg.getValues();

var callsByAgent=[];

for(var i=0;i<agentA.length;i++)

{

var cnt=0;

for(var j=2;j<vA.length;j++)

{

if(vA[j][3]==agentA[i])

{

cnt++;

}

}

callsByAgent[agentA[i]]=cnt;

}

/*

for(var i=0;i<agentA.length;i++)

{

Logger.log('\n%s\n%s',agentA[i],callsByAgent[agentA[i]]);

}

var s=Logger.getLog().replace(/^.+:/gm,'').replace(/\n/g,'<br />');

var userInterface=HtmlService.createHtmlOutput(s);

SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Calls By Agent')

*/

return callsByAgent;

}

function callsMadeByAgentToClient()

{

var agentA=uniqueItemArray(4);

var clientA=uniqueItemArray(5);

var ss=SpreadsheetApp.getActive();

var cntSh=ss.getSheetByName('Count');

var cntRg=cntSh.getDataRange();

var vA=cntRg.getValues();

var callsByAgentToClient=[];

for(var i=0;i<agentA.length;i++)

{

callsByAgentToClient[agentA[i]]=[];

for(var k=0;k<clientA.length;k++)

{

var cnt=0;

for(var j=2;j<vA.length;j++)

{

if(vA[j][3]==agentA[i] && vA[j][4]==clientA[k])

{

cnt++;

}

}

callsByAgentToClient[agentA[i]][clientA[k]]=cnt;

}

}

/*

for(var i=0;i<agentA.length;i++)

{

for(var j=0;j<clientA.length;j++)

{

Logger.log('\n%s\n%s\n%s',agentA[i],clientA[j],callsByAgentToClient[agentA[i]][clientA[j]]);

}

}

var s=Logger.getLog().replace(/^.+:/gm,'').replace(/\n/g,'<br />');

var userInterface=HtmlService.createHtmlOutput(s);

SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Calls By Agent To Client')

*/

return callsByAgentToClient;

}

function apptsBookedByAgentTotal()

{

var agentA=uniqueItemArray(4);

var clientA=uniqueItemArray(5);

var ss=SpreadsheetApp.getActive();

var cntSh=ss.getSheetByName('Count');

var cntRg=cntSh.getDataRange();

var vA=cntRg.getValues();

var apptsByAgent=[];

for(var i=0;i<agentA.length;i++)

{

var cnt=0;

for(var j=2;j<vA.length;j++)

{

if(vA[j][3]==agentA[i] && vA[j][5])

{

cnt++;

}

}

apptsByAgent[agentA[i]]=cnt;

}

/*

for(var i=0;i<agentA.length;i++)

{

Logger.log('\n%s\n%s',agentA[i],apptsByAgent[agentA[i]]);

}

var s=Logger.getLog().replace(/^.+:/gm,'').replace(/\n/g,'<br />');

var userInterface=HtmlService.createHtmlOutput(s);

SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Calls By Agent')

*/

return apptsByAgent;

}

function apptsBookedByAgentForClient()

{

var agentA=uniqueItemArray(4);

var clientA=uniqueItemArray(5);

var ss=SpreadsheetApp.getActive();

var cntSh=ss.getSheetByName('Count');

var cntRg=cntSh.getDataRange();

var vA=cntRg.getValues();

var apptsByAgentForClient=[];

for(var i=0;i<agentA.length;i++)

{

apptsByAgentForClient[agentA[i]]=[];

for(var k=0;k<clientA.length;k++)

{

var cnt=0;

for(var j=2;j<vA.length;j++)

{

if(vA[j][3]==agentA[i] && vA[j][4]==clientA[k] && vA[j][5])

{

cnt++;

}

}

apptsByAgentForClient[agentA[i]][clientA[k]]=cnt;

}

}

/*

for(var i=0;i<agentA.length;i++)

{

for(var j=0;j<clientA.length;j++)

{

Logger.log('\n%s\n%s\n%s',agentA[i],clientA[j],apptsByAgentForClient[agentA[i]][clientA[j]]);

}

}

var s=Logger.getLog().replace(/^.+:/gm,'').replace(/\n/g,'<br />');

var userInterface=HtmlService.createHtmlOutput(s);

SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Calls By Agent To Client')

*/

return apptsByAgentForClient;

}

Check Out reportSummaryNew() it creates a new sheet generates the report body and fills in all of the data. A separate function called buildReport() does the report building and returns an array of ranges for reportSummaryNew().

function buildReport()

{

var br='<br />';

var agentA=uniqueItemArray(4);

var clientA=uniqueItemArray(5);

var ss=SpreadsheetApp.getActive();

var sh=ss.insertSheet();

var shName=sh.getName();

sh.activate();

var rngA=[];

for(var i=0;i<=clientA.length;i++)

{

rngA[i]=sh.getRange(i*(agentA.length + 2) + 1,1,agentA.length + 2,3);

var vA=rngA[i].getValues();

if(i!=clientA.length)

{

vA[0][0]=clientA[i];

}

else

{

vA[0][0]='All';

}

vA[1][1]='Calls Made';

vA[1][2]='Appointments Booked';

for(var j=0;j<agentA.length;j++)

{

vA[j+2][0]=agentA[j];

}

rngA[i].setValues(vA);

}

return rngA;

}

function reportSummaryNew()

{

var br='<br />';

var agentA=uniqueItemArray(4);

var clientA=uniqueItemArray(5);

var clientAgents=emailAddressAssociatedWithEachClient();

var agentClients=clientsAssociatedWithEachEmail();

var callsByAgent=callsMadeByAgentTotal();

var callsByAgentToClient=callsMadeByAgentToClient();

var apptsByAgent=apptsBookedByAgentTotal();

var apptsByAgentForClient=apptsBookedByAgentForClient();

var rngA=buildReport();

var ss=SpreadsheetApp.getActive();

var rptSh=ss.getActiveSheet();

for(var i=0;i<rngA.length-1;i++)

{

var rg1=rngA[i];

var vA1=rg1.getValues();

vA1[2][1]=callsByAgentToClient[vA1[2][0]][vA1[0][0]];

vA1[3][1]=callsByAgentToClient[vA1[3][0]][vA1[0][0]];

vA1[4][1]=callsByAgentToClient[vA1[4][0]][vA1[0][0]];

vA1[2][2]=apptsByAgentForClient[vA1[2][0]][vA1[0][0]];

vA1[3][2]=apptsByAgentForClient[vA1[3][0]][vA1[0][0]];

vA1[4][2]=apptsByAgentForClient[vA1[4][0]][vA1[0][0]];

rg1.setValues(vA1);

}

var rg2=rngA[rngA.length-1];

var vA2=rg2.getValues();

vA2[2][1]=callsByAgent[vA2[2][0]];

vA2[3][1]=callsByAgent[vA2[3][0]];

vA2[4][1]=callsByAgent[vA2[4][0]];

vA2[2][2]=apptsByAgent[vA2[2][0]];

vA2[3][2]=apptsByAgent[vA2[3][0]];

vA2[4][2]=apptsByAgent[vA2[4][0]];

rg2.setValues(vA2);

}

/*

If you wish to be able to format the report you can run buildReport and take one of the sections and move it to the upper left corner A1:C5 and format it any way you like and then replace this line `var rngA=buildReport()` in reportSummaryNew() with this `var rngA=buildReport1()` yes just add a 1 between the 't' and '('. The buildReport1() function will copy the format from Format!A1:A5 so you can change the format easily any time you want just by formatting the contents of the sheet named 'Format';

*/

Here's buildReport1()

function buildReport1()

{

var br='<br />';

var agentA=uniqueItemArray(4);

var clientA=uniqueItemArray(5);

var ss=SpreadsheetApp.getActive();

var trng=ss.getSheetByName('Format').getRange("A1:C5");

var sh=ss.insertSheet();

var shName=sh.getName();

sh.activate();

var rngA=[];

for(var i=0;i<=clientA.length;i++)

{

rngA[i]=sh.getRange(i*(agentA.length + 2) + 1,1,agentA.length + 2,3);

var vA=rngA[i].getValues();

trng.copyTo(rngA[i],{formatOnly:true});

if(i!=clientA.length)

{

vA[0][0]=clientA[i];

}

else

{

vA[0][0]='All';

}

vA[1][1]='Calls Made';

vA[1][2]='Appointments Booked';

for(var j=0;j<agentA.length;j++)

{

vA[j+2][0]=agentA[j];

}

rngA[i].setValues(vA);

}

return rngA;

}

Here's what my final output looks like: