To start, open the store lead sheet you are setting up, and open the Formula Sheet to the Reporting Script tab. (Click to open directly.)
Update the Sheet Header #
On the Store Sheet click Row 1. Click the number box on the left to select the whole row.
Go to the Formula Sheet, and in the “Reporting Script” tab, select Row 1 and Copy (Ctrl + C) the row.
Switch back over the Store Sheet and Paste (Ctrl + V) into the already selected header row.
Add A Report Tab #
In the bottom left of the Store Sheet click the plus button to add a new tab.
Double click the newly created sheet name in the bottom bar and rename it the “Report”. Naming must be exact.
Note: Alternatively you can right click the tab and click “Rename”.
With that tab open, go to the Formula Sheet and select Row 2 by clicking the box on the left with the Number 2 in it. Copy (Ctrl + C) this row.
Go to the Store Sheet and select the top leftmost cell (A1) and Paste (Ctrl + V).
Protect the Report Sheet #
Next, right click the “Report” sheet tab and click “Protect Sheet”.
Click the “Set Permissions” button in the window that pops up.
Under “Restrict who can edit this range”, click the dropdown and change it to “Only You”.
Then click “Done”.
Open Google Sheets Script Editor #
In the tool bar, click “Extensions” then “Apps Scripts” from the dropdown.
Create New Scripts #
If the “Reporting” and “Duplicate Remover” scripts are not already created, right click the “Create a New Project” and open in new tab. Then single click it. This will open two empty script projects.
Set Up Reporting Script #
In the first script project tab, click the “Untitled Project” in the top left. Rename the project to “Reporting”.
Open the Reporting Script Toggle below and triple click to select all. Then Copy (Ctrl + C) the script.
In text box of the script, click into it and Select All (Ctrl + A), then Paste (Ctrl + V) in the script from below.
Reporting Script #
// this is new script for update this sheet report and also update to central report
//
var masterReportS_ID = “1YsfrZ5Y5kQZK5K9Mlg0x2oQlRxwUVwS59GlAQFUSShQ”;
function pushReport() {
var report_tabName = “REPORT”;
var thisSS = SpreadsheetApp.getActiveSpreadsheet();
var reportTab = thisSS.getSheetByName(report_tabName);
var masterSS = SpreadsheetApp.openById(masterReportS_ID);
var masterReportTab = masterSS.getSheetByName(“Master REPORT”);
var datesRange = masterReportTab.getRange(“G1:H1”).getValues();
reportTab.getRange(“G1:H1”).setValues(datesRange);
var totalLead = 0;
var total_appt = 0;
var total_voucher = 0;
var total_resc = 0;
var tabCOunt = thisSS.getSheets().forEach(function (tab) {
var namTab = tab.getName();
if (namTab != report_tabName) {
var lastrow = tab.getLastRow() – 1;
if (lastrow < 1) { lastrow = 1 }; var hdr = tab.getRange(1, 1, 1, Math.max(tab.getLastColumn())).getValues()[0] var data = tab.getRange(2, 1, lastrow, Math.max(tab.getLastColumn(), 1)).getValues() .filter(function (p) { return p[0] != “” }) .map(function (e) { var A = Math.max(hdr.indexOf(“Timestamp”), 0) var B = Math.max(hdr.indexOf(“SALES REP”), 0) var C = Math.max(hdr.indexOf(“SOURCE”), 0) var D = Math.max(hdr.indexOf(“LEAD TYPE”), 0) var E = Math.max(hdr.indexOf(“CUSTOMER NAME”), 0) var F = Math.max(hdr.indexOf(“CUSTOMER PHONE”), 0) var G = Math.max(hdr.indexOf(“CUSTOMER EMAIL”), 0) var H = Math.max(hdr.indexOf(“ADDITIONAL INFO”), 0) var I = Math.max(hdr.indexOf(“CONFIRMED APPOINTMENT”), 0) var J = Math.max(hdr.indexOf(“DEALERSHIP NOTES”), 0) return [e[A], e[B], e[C], e[D], e[E], e[F], e[G], e[H], e[I], e[J], namTab] }); totalLead += data.filter(function (d) { return d[0] >= datesRange[0][0] && d[0] <= datesRange[0][1] && d[3] != “” }).length; total_appt += data.filter(function (d) { return d[0] >= datesRange[0][0] && d[0] <= datesRange[0][1] && d[3] == “APPOINTMENT” }).length; total_voucher += data.filter(function (d) { return d[0] >= datesRange[0][0] && d[0] <= datesRange[0][1] && d[3] == “VOUCHER” }).length; total_resc += data.filter(function (d) { return d[0] >= datesRange[0][0] && d[0] <= datesRange[0][1] && d[3] == “RESCHEDULE” }).length; } //rpt }); var reportingROW = [`=hyperlink(“https://docs.google.com/spreadsheets/d/`+thisSS.getId()+`/edit”,”`+thisSS.getName()+`”)`, totalLead, total_appt, total_voucher, total_resc, new Date()]; reportTab.getRange(2, 1, 1, reportingROW.length).setValues(([reportingROW])); updateMasterReport(reportingROW); } function updateMasterReport(dataToUpdate) { var masterSS = SpreadsheetApp.openById(masterReportS_ID); var masterReportTab = masterSS.getSheetByName(“Master REPORT”); var sheetIDS = masterReportTab.getRange(1, 1, masterReportTab.getLastRow(), 1).getFormulas().join(“$$$$$”).split(“$$$$$”); Logger.log(sheetIDS) var targetROW = sheetIDS.indexOf(dataToUpdate[0]) + 1; if (targetROW > 2) {
masterReportTab.getRange(targetROW, 1, 1, dataToUpdate.length).setValues(([dataToUpdate]));
} else {
masterReportTab.getRange(masterReportTab.getLastRow() + 1, 1, 1, dataToUpdate.length).setValues(([dataToUpdate]));
}
}
Save the document (Ctrl + S) and wait for it to save.
In the bar above the text box, click the “Run” button.
After a few seconds, a Google Authorization will pop up. Directions on how to give authorization is in the toggle below.
Google Authorization #
After a few seconds, a box will pop up. Click the “Review Permissions” button.
In the next pop up, click the admin3@fbpromocalendar.com Google account.
Then click the “Allow” button in the next window.
Set Up Reporting Time Trigger #
On the left side, click the clock icon to open the Triggers window.
In the bottom right, click the “Add Trigger” button.
In the pop up window, under the 3rd selection “Select event source”, click the dropdown and click “Time-driven”
Under the 4th selection “Select type of time based trigger”, select “Minutes timer”
Click the “Save” button in the bottom right.
Once the trigger shows in the list, close the tab.
Set Up Duplicate Remover Script #
In the first script project tab, click the “Untitled Project” in the top left. Rename the project to “Duplicate Remover”.
Open the Duplicate Remover Script Toggle below and triple click to select all. Then Copy (Ctrl + C) the script.
In text box of the script, click into it and Select All (Ctrl + A), then Paste (Ctrl + V) in the script from below.
#
/**
* @OnlyCurrentDoc
*/
function turnON_hourly2() {
var sheet = SpreadsheetApp.getActive();
var Triggers = ScriptApp.getProjectTriggers()
var k = 0;
if (Triggers != “”) {
for (var i = 0; i < Triggers.length; i++) {
if (Triggers[i].getHandlerFunction() == "checkDuplicates2") {
k++
}
}
}
if (k > 0) {
}
else {
ScriptApp.newTrigger(“checkDuplicates2”)
.timeBased()
.everyHours(1)
.create();
}
}
//
function turnOFF_hourly2() {
var Triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < Triggers.length; i++) { if (Triggers[i].getHandlerFunction() == "checkDuplicates2") { ScriptApp.deleteTrigger(Triggers[i]) } } } // What I want to have it do is if E F G matches but D has 1 version as Voucher and one as Appointment, // then we remove the Voucher version and leave the appointment. // THEN after that, if D is Appointment, then we check duplicates for D E F G I. If duplicates found remove. // Also add if I is within 5 mins of the timestamp in A, remove it. function checkDuplicates2() { var ok = replaceNow() ; for (var q = 0; q < 2; q++) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var tab = ss.getSheetByName("CUSTOMER LEADS"); tab.getRange(2, 1, tab.getLastRow() - 1, tab.getLastColumn()).sort({ column: 1, ascending: false, column: 4, ascending: true }); var data = tab.getRange(2, 1, tab.getLastRow() - 1, 9).getValues(); var rowsNeedDelete = []; var uniqueData = []; // check 1 for (var i in data) { // if (new Date(new Date(data[i][0]).getTime() + 300000) > new Date(data[i][8]) && q == 1) {
// rowsNeedDelete.push(((parseInt(i) + 2)))
// }
var row = “”;
if (q == 0) { row = data[i][3].toString().replace(“VOUCHER”, “APPOINTMENT”) + data[i][4] + data[i][5] + data[i][6]; }
if (q == 1) { row = data[i][3] + data[i][4] + data[i][5] + data[i][6] + data[i][8]; }
var duplicate = false;
for (var j in uniqueData) {
if (row == uniqueData[j]) {
duplicate = true;
}
}
if (!duplicate) {
uniqueData.push(row);
}
if (duplicate ) {
if( q== 0 && data[i][3] ==”VOUCHER”){
rowsNeedDelete.push(((parseInt(i) + 2)))
}else if(q== 1){
rowsNeedDelete.push(((parseInt(i) + 2)))
}
}
}
// end check 1
rowsNeedDelete = rowsNeedDelete.reverse();
for (k = 0; k < rowsNeedDelete.length; k++) { var deleterowNUM = Number(rowsNeedDelete[k]); tab.deleteRow(deleterowNUM); // tab.getRange(deleterowNUM, 10).setValue("DELETE") } Logger.log(rowsNeedDelete) } tab.getRange(2, 1, tab.getLastRow() - 1, tab.getLastColumn()).sort({ column: 1, ascending: true }) }[/et_pb_toggle][et_pb_text _builder_version="4.17.4" _module_preset="default" hover_enabled="0" sticky_enabled="0"]
Add The Replacer Script #
In the upper left, click the Plus button and select “Script”
Press the Enter button on your keyboard to confirm the new file.
[/et_pb_text]
Open the Replacer Script Toggle below and triple click to select all. Then Copy (Ctrl + C) the script.
In text box of the script, click into it and Select All (Ctrl + A), then Paste (Ctrl + V) in the script from below.
function replaceNow() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var tab = ss.getSheetByName(“CUSTOMER LEADS”);//CUSTOMER LEADS”
var allDataCells = tab.getRange(1,1,tab.getLastRow(), tab.getLastColumn()).getValues();
for(var r=0;r<allDataCells.length;r++){
for(var c=0;c<allDataCells[0].length;c++){ if(typeof(allDataCells[r][c])==’string’){ allDataCells[r][c]=allDataCells[r][c].replace(/{}|\[object Object\]/g,”); } } } tab.getRange(1,1,tab.getLastRow(), tab.getLastColumn()).setValues(allDataCells); tab.getRange(“I1”).setFormula(`={“CONFIRMED APPOINTMENT”;arrayformula(if($H$2:$H=””,,iferror( if(iferror(datevalue(right(REGEXREPLACE($H$2:$H,”(.*),”,”$$$”),LEN(REGEXREPLACE($H$2:$H,”(.*),”,”$$$”))-FIND(“$$$”,REGEXREPLACE($H$2:$H,”(.*),”,”$$$”),1)-2)),0)>0, right(REGEXREPLACE($H$2:$H,”(.*),”,”$$$”),LEN(REGEXREPLACE($H$2:$H,”(.*),”,”$$$”))-FIND(“$$$”,REGEXREPLACE($H$2:$H,”(.*),”,”$$$”),1)-2), if(iferror(datevalue(left(REGEXREPLACE($H$2:$H,”Appt DateTime: “,””),find(“-“,REGEXREPLACE($H$2:$H,”Appt DateTime: “,””),1)-2)),0)>0, left(REGEXREPLACE($H$2:$H,”Appt DateTime: “,””),find(“-“,REGEXREPLACE($H$2:$H,”Appt DateTime: “,””),1)-2),)))))}`);
tab.getRange(“D1”).setFormula(`={“LEAD TYPE”;ArrayFormula(IF(indirect(“$A$2:$A”)=””,,if(indirect(“c2:c”)=”Facebook”,”LEAD GEN”,if(iferror(DATEVALUE(indirect(“$I$2:$I”)),”VOUCHER”)<>”VOUCHER”,”APPOINTMENT”,”VOUCHER”))))}`);
tab.getRangeList([“D2:D”,”I2:I”]).clearContent();
}
On the left side, click “Code.gs”
In the bar above the text box, click the “Run” button.
After a few seconds, a Google Authorization will pop up. Directions on how to give authorization is in the toggle below.
Google Authorization #
After a few seconds, a box will pop up. Click the “Review Permissions” button.
In the next pop up, click the admin3@fbpromocalendar.com Google account.
Then click the “Allow” button in the next window.
After a few seconds and the script has run, close the window.
That is everything needed.
Troubleshooting #
If the Reporting script can not run, check the Store sheet and see if there are any empty tabs. There cannot be any empty tabs. Every tab must have at least the standard header in the tab for the reporting to work.
