Google App Script To get case report from a sheet

GenerateCaseBaseReport.gs

I wrote a test function to read ACTIVE sheet values and write different vendor reports in separate sheets. If you wanna know more about my code, let me know in comment.

function test() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  // remove header row values from array
  values.shift();

  // will be months present in data
  // { JAN: 1, FEB: 1 ... }
  var mos = {};
  // data structure
  // { A: {JAN: [value1, value2] }... }
  var ds = {};
  values.forEach(function(row) {
    var month = row.shift(); // 1st col
    var ven = row.shift(); // 2nd col
    mos[month] = 1; // set month on mos
    ds[ven] = ds[ven] || {};
    // set value on ds
    // example = ds.A.JAN = [ A+B, C+D ]
    ds[ven][month] = [parseInt(row[0]) + parseInt(row[1]), parseInt(row[2]) + parseInt(row[3])];
  });
  //  Logger.log(mos);
  var months = ['JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'];
  // filter months to get months present in sheet data in order
  months = months.filter(function(m) {
    return mos[m] == 1;
  });
  // Logger.log(months);
  // result object, for each vendor create array of rows
  // { A: [ row, row... ]... }
  var rs = {};
  Object.keys(ds).forEach(function(v) {
    rs[v] = rs[v] || [];
    months.forEach(function(m) {
      if (ds[v][m]) {
        rs[v].push([m, ds[v][m][0], ds[v][m][1]]);
      } else {
        rs[v].push([m, 'N/A', 'N/A']);
      }
    });
  });
  // Logger.log(rs);
  // write rows
  Object.keys(rs).forEach(function(v) {
    var headers = [['Vendor', v, 'Report'].join(' '), 'Conversion (A+B)', 'Conversion (C+D)'];
    // insert headers into rows for writing
    rs[v].unshift(headers);
    try {
      ss.insertSheet(headers[0]);
    } catch (e) {}
    var sh = ss.getSheetByName(headers[0]);
    sh.clear();
    sh.getRange(1, 1, rs[v].length, 3).setValues(rs[v]);
  });
}

Stack Overflow Link