Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- var months = ["March 2019 w/ Digital Rights Reviewer", "March 2019", "February 2019", "January 2019", "December 2018"]
- function all() {
- var overview = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Overview")
- overview.clear();
- var appRankings = {};
- perMonth("March 2019 w/ Digital Rights Reviewer", appRankings, 0, 1, 36, 10, 26, 21, 32)
- perMonth("March 2019", appRankings, 0, 1, 30, 10, 26, 21, -1)
- perMonth("February 2019", appRankings, 0, 1, 29, 16, 8, 26, -1 )
- perMonth("January 2019", appRankings, 1, 0, 29, 10, 16, 26, -1)
- perMonth("December 2018", appRankings, -1, 0, 19, 18, 7, -1, -1)
- overview.clear();
- overview.appendRow(contact(["id", "name", "score"],months))
- for (app in appRankings) {
- overview.appendRow(contact([app, appRankings[app]["name"], "Total"], appRankingValues(appRankings, app, "Total")));
- overview.appendRow(contact([app, appRankings[app]["name"], "DE"], appRankingValues(appRankings, app, "DE")));
- overview.appendRow(contact([app, appRankings[app]["name"], "PH"], appRankingValues(appRankings, app, "PH")));
- overview.appendRow(contact([app, appRankings[app]["name"], "TMYUI"], appRankingValues(appRankings, app, "TMYUI")));
- overview.appendRow(contact([app, appRankings[app]["name"], "DR"], appRankingValues(appRankings, app, "DR")));
- };
- }
- function contact(arr1, arr2) {
- for (index in arr2) {
- arr1.push(arr2[index])
- }
- return arr1
- }
- function appRankingValues(appRankings, app, score) {
- var values = []
- for (i in months) {
- if (appRankings[app][score]) {
- value = appRankings[app][score][months[i]];
- } else {
- value = appRankings[app][score]
- }
- values[i] = value
- }
- return values
- }
- function findAppId(appRankings, appName) {
- for (app in appRankings) {
- if (appRankings[app]["name"].toLowerCase() == appName.toLowerCase()) {
- return app
- }
- }
- return appName
- }
- function perMonth(sheetName, appRankings, idColumn, nameColumn, totalScore, deScore, phScore, tmyuiScore, drScore) {
- var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
- var sheet = spreadsheet.getSheetByName(sheetName);
- sheet.setFrozenColumns(0);
- sheet.setFrozenRows(1);
- var rangeData = sheet.getDataRange();
- perMonthPerScore(appRankings, rangeData, idColumn, nameColumn, totalScore, sheetName, "Total")
- perMonthPerScore(appRankings, rangeData, idColumn, nameColumn, deScore, sheetName, "DE")
- perMonthPerScore(appRankings, rangeData, idColumn, nameColumn, phScore, sheetName, "PH")
- perMonthPerScore(appRankings, rangeData, idColumn, nameColumn, tmyuiScore, sheetName, "TMYUI")
- perMonthPerScore(appRankings, rangeData, idColumn, nameColumn, drScore, sheetName, "DR")
- }
- function perMonthPerScore(appRankings, rangeData, idColumn, nameColumn, sortColumn, month, score) {
- if (sortColumn == -1) {
- return
- }
- Logger.log(month, rangeData.getCell(1, sortColumn).getValue())
- rangeData.sort({column:sortColumn, ascending:false})
- var lastColumn = rangeData.getLastColumn();
- var lastRow = rangeData.getLastRow();
- var rangeValues = rangeData.getValues();
- var lastValue = 1000
- var lastRank = 1
- var rank
- for ( j = 1 ; j < lastRow; j++){
- var name = rangeValues[j][nameColumn]
- var value = rangeValues[j][sortColumn -1] // sort columns start with index 1 !!
- if (value < lastValue) {
- rank = j
- lastRank = rank
- lastValue = value
- } else {
- rank = lastRank
- }
- var appId
- if (idColumn == -1) {
- appId = findAppId(appRankings, name)
- } else {
- appId = rangeValues[j][idColumn]
- }
- if (!appRankings[appId]) {
- appRankings[appId] = {}
- }
- if (!appRankings[appId][score]) {
- appRankings[appId][score] = {}
- }
- appRankings[appId][score][month] = rank
- appRankings[appId]["name"] = name
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement