Created
March 13, 2019 13:48
-
-
Save franklinokech/190ed38db43852aa37bf1e775b6e6b92 to your computer and use it in GitHub Desktop.
Google App Script To get case report from a sheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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]); | |
}); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Stack Overflow Link