let sheet = workbook.getWorksheet("Report");
  let summary = workbook.getWorksheet("2022");
  let dataTable = sheet.getTable("Data");
  let range = dataTable.getRange();
  let data = range.getValues();
  let rowCount = range.getRowCount();
//This names my columns  
let providerID = 5;
  let first = 6;
  let last = 7;
  let labels = 8;
  let codeLabels = 12;
  let driveTime = 13;
  let time = 1;
 
  //adds tables
  let summaryTable = sheet.addTable("Q2:V2", true);
  summaryTable.getHeaderRowRange().setValues([["Label", "ID", "Name", "Billable", "Non-Billable", "Drive Time"]]);
  summaryTable.setPredefinedTableStyle("TableStyleLight1");
  summaryTable.setShowBandedRows(false);
  summaryTable.setShowFilterButton(true);
  summaryTable.getRange().getFormat().autofitColumns();
  let summaryTable2 = sheet.addTable("X2:AA2", true);
  summaryTable2.getHeaderRowRange().setValues([["Location", "Billable", "Non-Billable", "Drive Time"]]);
  summaryTable2.setPredefinedTableStyle("TableStyleLight2");
  summaryTable2.setShowBandedRows(true);
  summaryTable2.setShowFilterButton(false);
  summaryTable2.getRange().getFormat().autofitColumns();
  summaryTable.setName("SummaryTable");
  summaryTable2.setName("SummaryTable2");

  let issuesTable = sheet.addTable("X7", true);
  issuesTable.getHeaderRowRange().setValues([["Issues"]]);
  issuesTable.setPredefinedTableStyle("TableStyleLight3");
  issuesTable.setName("Issues");
  
let id=data[1][providerID];
let name = data[1][last];
let billableSum=0;
let nonBillableSum=0;
let label = "none";
let driveSum = 0;

//This adds the totals for full time and PR productivity people

for (let i = 1; i < rowCount; i++) {
  if(id===data[i][providerID]){
    if (data[i][codeLabels].toLowerCase().includes("productivity")) {
      //This totals the hours for the full time people and labels them
      if (data[i][labels].toLowerCase().includes("full time")) {
        label = "full time";
        driveSum += (data[i][driveTime])/60;
        if (data[i][codeLabels].toLowerCase().includes(",billable ,")) {
          billableSum += data[i][time];
        }
        if (data[i][codeLabels].toLowerCase().includes(",non")) {
          nonBillableSum += data[i][time];
        }
        //This totals the hours for the PR people and labels them
      } else if (data[i][labels].toLowerCase().includes("pr exclusion")) {
        label = "PR";
        driveSum += (data[i][driveTime]) / 60;
        if (data[i][codeLabels].toLowerCase().includes(",billable ,")) {
          billableSum += data[i][time];
        }
        if (data[i][codeLabels].toLowerCase().includes(",non")) {
          nonBillableSum += data[i][time];
        }
      }
    } 
  } else {
    //This adds everyone to the summary table
    if(label!="none"){
      summaryTable.addRow(null, [label, id, name, billableSum, nonBillableSum, driveSum]);
    }
    billableSum = 0;
    nonBillableSum = 0;
    driveSum = 0;
    id = data[i][providerID];
    name = data[i][last]
    label = "none"
    i--;
  }
}

let chicagoBill = 0;
let chicagoNon = 0;
let normalBill = 0;
let normalNon = 0;
let peoriaBill = 0;
let peoriaNon = 0;
let chicagoDrive = 0;
let peoriaDrive=0;
let normalDrive = 0;

//This totals the different regions 
for (let i =1; i<rowCount;i++){
  if (!data[i][labels].toLowerCase().includes("pr exclusion")) {
    if (data[i][codeLabels].toLowerCase().includes("productivity")) {
      //Chicagoland
      if (data[i][labels].toLowerCase().includes("chicagoland behavior")){
        chicagoDrive += (data[i][driveTime])/60
        if (data[i][codeLabels].toLowerCase().includes(",billable ,")) {
          chicagoBill += data[i][time];
        }
        if (data[i][codeLabels].toLowerCase().includes(",non")) {
          chicagoNon += data[i][time];
        }
      }
      //Normal
      if (data[i][labels].toLowerCase().includes("normal behavior")) {
        normalDrive += (data[i][driveTime]) / 60
        if (data[i][codeLabels].toLowerCase().includes(",billable ,")) {
          normalBill += data[i][time];
        }
        if (data[i][codeLabels].toLowerCase().includes(",non")) {
          normalNon += data[i][time];
        }
      }
      //Peoria
      if (data[i][labels].toLowerCase().includes("peoria behavior")) {
        peoriaDrive += (data[i][driveTime]) / 60
        if (data[i][codeLabels].toLowerCase().includes(",billable ,")) {
          peoriaBill += data[i][time];
        }
        if (data[i][codeLabels].toLowerCase().includes(",non")) {
          peoriaNon += data[i][time];
          
        }
      }
    }
  }
}
summaryTable2.addRows(null,[
  ["Chicago BT",chicagoBill,chicagoNon,chicagoDrive],
  ["Normal BTs",normalBill,normalNon,normalDrive],
  ["Peoria BTs",peoriaBill,peoriaNon,peoriaDrive]]);
summaryTable.getSort().apply([{key:0, ascending: true}]);
summaryTable.getRange().getFormat().autofitColumns();
summaryTable2.getRange().getFormat().autofitColumns();

}