Function formatReport

automatereporting.js:39–106

()

}

function formatReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var rawSheet = ss.getSheetByName('RawData');
  var reportSheet = ss.getSheetByName('Report') || ss.insertSheet('Report');
  reportSheet.clear();

  var rawData = rawSheet.getDataRange().getValues();

  // Adding KPI boxes
  var data = rawData.slice(1);
  var totalRequests = data.length;
  var closedRequests = data.filter(row => row[2] === 'Closed').length;
  var openRequests = totalRequests - closedRequests;

  // Adding KPIs with professional styling
  var kpiHeaders = [['Report Metrics']];
  var kpis = [
    ['Total Requests', totalRequests],
    ['Closed Requests', closedRequests],
    ['Open Requests', openRequests]
  ];

  // Append KPI headers
  reportSheet.getRange(1, 1, 1, 1).setValues(kpiHeaders);
  var headerRange = reportSheet.getRange(1, 1, 1, 1);
  headerRange.setBackground('#26428b').setFontColor('#FFFFFF').setFontWeight('bold').setFontSize(14);
  headerRange.mergeAcross();

  // Append KPI values
  reportSheet.getRange(2, 1, kpis.length, 2).setValues(kpis);
  var kpiRange = reportSheet.getRange(2, 1, kpis.length, 2);
  kpiRange.setBackground('#f1f1f1').setFontColor('#000000').setFontWeight('bold').setFontSize(12);

  // Adding some space before the table
  reportSheet.appendRow([' ']);
  // Add data refreshed timestamp
  reportSheet.appendRow(['']);
  var timestamp = new Date();
  reportSheet.appendRow(['Data refreshed on:', timestamp]);
  var timestampRange = reportSheet.getRange(reportSheet.getLastRow(), 1, 1, 2);
  timestampRange.setBackground('#f1f1f1').setFontColor('#000000').setFontWeight('bold');
  reportSheet.appendRow(['Open Requests']);

 // Filter specific columns to include in the detailed table
  // Specify the columns you want to include by their indices
  // Filter rows based on a specific column value (e.g., only open requests)
  var filteredRows = data.filter(row => row[2] === 'Open');

  // Specify the columns you want to include by their indices
  var columnsToInclude = [1, 2, 4, 5, 6]; 

  var filteredHeaders = columnsToInclude.map(index => rawData[0][index]);
  var filteredData = filteredRows.map(row => columnsToInclude.map(index => row[index]));

  // Append filtered data headers and rows
  reportSheet.appendRow(filteredHeaders);
  reportSheet.getRange(reportSheet.getLastRow() + 1, 1, filteredData.length, filteredData[0].length).setValues(filteredData);