Google Apps Script to massage Blockstack App Mining results

gistfile1.txt

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
  }
}