summary.js

/*
 * updates 'summary' sheet
 * uses 'a/c' sheet to link characters and accounts [[header_row],[account1,char1,char2,char3],[account2,....]]
 * triggered onOpen
 * assume script timezone is set or is by default correct
 */
function summary() {
  const timezone = Session.getScriptTimeZone();
  const offsetRow = 6;
  const offsetCol = 2;
  const defaultFontColor = '#ffffff';
  // easier to start with characters and merge in accounts later
  let characters = GESI.getAuthenticatedCharacters();

  // use fn to then keep track of which response is what
  const fn = ['characters_character_location', 'characters_character_wallet', 'characters_character_skills', 'characters_character_skillqueue'];
  let rqs = [];
  Object.keys(characters).forEach(c => {
    let client = GESI.getClient(c);
    fn.forEach(f => rqs.push(client.setFunction(f).buildRequest()));
  });
  const rsp = UrlFetchApp.fetchAll(rqs);
  if(rsp.some(r => r.getResponseCode() != 200))
    throw 'esi response code != 200';

  let raw = []; // [[char1fn1httpresponse, ...], [char2fn1httpresponse, ...], ...]
  while(rsp.length > 0)
    raw.push(rsp.splice(0, fn.length));
  // characters: { .... { [fn[i]]: {response}, ... } .. }
  Object.keys(characters).forEach((c, i) => fn.forEach((f, j) => characters[c][f] = JSON.parse(raw[i][j].getContentText())));

  // small helper to avoid repetitions, no need to specify character in client
  const getMultiple = (...args) => { // [{fn, arr, key}, {fn, arr, key}, ..]
    let rqs = [];
    args.forEach(a => {
      let client = GESI.getClient().setFunction(a.fn);
      a.arr = [... new Set(a.arr)]; // uniqueness matters to limit requests
      a.arr.map(id => rqs.push(client.buildRequest({[a.key]: id})));
    });
    let rsp = UrlFetchApp.fetchAll(rqs);
    if(rsp.some(r => r.getResponseCode() != 200))
      throw 'esi response code != 200';
    let data = {}; // {[fn1]: {arr[i]: {response}, ..}, [fn2]: {..}, ..}
    args.forEach(a => {data[a.fn] = {}; a.arr.forEach(id => data[a.fn][id] = JSON.parse(rsp.shift().getContentText()))});
    return data;
  };
  
  let a = {fn: 'universe_systems_system', arr: Object.values(characters).map(e => e.characters_character_location.solar_system_id), key: 'system_id'};
  let b = {fn: 'alliances_alliance', arr: Object.values(characters).map(e => e.alliance_id).filter(Boolean), key: 'alliance_id'};
  let c = {fn: 'corporations_corporation', arr: Object.values(characters).map(e => e.corporation_id), key: 'corporation_id'};
  const { universe_systems_system, alliances_alliance, corporations_corporation } = getMultiple(a, b, c);
  
  // get an accounts obj
  const ss_ac = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('a/c');
  const ac_raw = ss_ac.getRange(2, 1, ss_ac.getDataRange().getNumRows() - 1, ss_ac.getDataRange().getNumColumns()).getValues(); // offset header row
  let accounts = {}; // { account1: { character1: {}, .. } .. }
  ac_raw.forEach(row => accounts[row[0]] = [row[1], row[2], row[3]].filter(Boolean).reduce((ac, char) => ({ ...ac, [char]: null }), {}));
  // header used as reference for lots of stuff
  const headers = [' account ', ' character ', ' corporation ', ' alliance ', ' location ', ' wallet ', ' skills ', ' skillqueue_ends_hidden ', ' skillqueue ends '];
  let data = [headers]; // 2d array to be written
  // create our rows and merge characters in accounts if authed
  for(let a in accounts) {
    for(let c in accounts[a]) { // todo: push vs concat ? push must be better for perf ? concat might be more readable
      let row = [];
      row.push(a); // account
      row.push(c); // character
      if(c in characters) { // if authed
        accounts[a][c] = characters[c];
        row.push('['+corporations_corporation[accounts[a][c].corporation_id].ticker+']'); // corporation ticker
        (accounts[a][c].alliance_id === null)
          ? row.push('')
          : row.push('['+alliances_alliance[accounts[a][c].alliance_id].ticker+']'); // alliance ticker if any
        row.push(universe_systems_system[accounts[a][c].characters_character_location.solar_system_id].name); // location as system name
        row.push(accounts[a][c].characters_character_wallet); // wallet
        row.push(accounts[a][c].characters_character_skills.total_sp); // total SP
        let lastq = accounts[a][c].characters_character_skillqueue.pop();
        (lastq == undefined || !('finish_date' in lastq)) // some queues are empty, other are paused
          ? row.push('-')
          : row.push(Utilities.formatDate(new Date(lastq.finish_date), timezone, 'MM/dd/yyyy HH:mm:ss')); // skillqueue finish date formatted (hidden)
        row.push(''); // padding for countdown
      } else { // if NOT authed
        row = row.concat(Array(headers.length - row.length).fill('-'));
      }
      data.push(row);
    }
  }

  // write data + cosmetics
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('summary');
  sheet.clear();
  
  const bckgrndRange = sheet.getRange('A1:Z100')
  bckgrndRange.setBackground('#222222');
  sheet.unhideColumn(bckgrndRange);
  
  sheet.getRange(1+offsetRow,1+offsetCol,1,data[0].length).setFontSize(11);
  const dataRange = sheet.getRange(1+offsetRow,1+offsetCol,data.length,data[0].length);
  headers.forEach((h, i) => {
    if(h.includes('hidden'))
      sheet.hideColumns(i+offsetCol+1)
  });
  dataRange.setValues(data)
    .setFontFamily('Courier New')
    .setFontWeight('bold')
    .setHorizontalAlignment('center')
    .setBorder(true, true, true, true, true, true, '#ffd666', SpreadsheetApp.BorderStyle.SOLID)
    .setNumberFormat('@STRING@'); // avoid automatic detection issues until it doesn't cause any problem
  sheet.autoResizeColumns(1+offsetCol, 1+offsetCol+data[0].length);
  
  // write countdown cells (file->spreadsheet settings->calculations->on change and every minute) differenciate done, empty and still going
  const countdown = '=iferror(if(int(R[0]C[-1]-now())<0,"done",int(R[0]C[-1]-now())&"d "&hour(mod(R[0]C[-1]-now(),1))&"h "&Minute(mod(R[0]C[-1]-now(),1))&"m"), "-")';
  sheet.getRange(2+offsetRow,data[0].length+offsetCol,data.length-1,1).setFormulaR1C1(countdown);
  
  const bandings = sheet.getDataRange().getBandings();
  (bandings.length === 0)
    ? dataRange.applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY, false, false).setFirstRowColor('#4a4a4a').setSecondRowColor('#222222')
    : bandings.forEach(b => b.setRange(dataRange));

  // font colors (using static font colors on everything except countdown cells)
  const fontColors = Array(data.length).fill(null).map(() => Array(data[0].length).fill(defaultFontColor));
  const walletRainbow = new Rainbow(); // https://github.com/anomal/RainbowVis-JS/blob/master/rainbowvis.js
  walletRainbow.setNumberRange(0, Math.max(...data.map(r => r[headers.indexOf(' wallet ')]).filter(Number.isFinite)));
  walletRainbow.setSpectrum('#da3f32', '#e46740', '#ec874b', '#f6af59', '#ffd666', '#cfcf70', '#abc978', '#87c380', '#57bb8a');
  
  data.forEach((r, i) => {
    // location colors
    Object.values(universe_systems_system).forEach(s => {
      if(s.name == r[headers.indexOf(' location ')]) {
        if(s.security_status > 0.5) {
          fontColors[i][headers.indexOf(' location ')] = '#57bb8a'; // High sec
        } else if(s.security_status < 0.5 && s.security_status >= 0.1) {
          fontColors[i][headers.indexOf(' location ')] = '#ffd666'; // Low sec
        } else if(s.security_status < 0) {
          fontColors[i][headers.indexOf(' location ')] = '#da3f32'; // Null sec
        }
      }
    });
    // wallet colors
    if(Number.isFinite(r[headers.indexOf(' wallet ')]))
      fontColors[i][headers.indexOf(' wallet ')] = '#'+walletRainbow.colourAt(r[headers.indexOf(' wallet ')]);
    // skills colors
    if(Number.isFinite(r[headers.indexOf(' skills ')])) {
      if(r[headers.indexOf(' skills ')] < 4900000)
        fontColors[i][headers.indexOf(' skills ')] = '#da3f32'; // way below alpha limits
      else if(r[headers.indexOf(' skills ')] > 4900000 && r[headers.indexOf(' skills ')] < 5000000)
        fontColors[i][headers.indexOf(' skills ')] = '#ffd666'; // close to alpha limit, near ready for SP farm
      else if(r[headers.indexOf(' skills ')] > 5000000)
        fontColors[i][headers.indexOf(' skills ')] = '#57bb8a'; // over alpha limit
    }
  });
  dataRange.setFontColors(fontColors);
  
  // conditional formatting
  const rules = [];
  const skillqueueEndsRange = sheet.getRange(offsetRow+2,offsetCol+1+headers.indexOf(' skillqueue ends '),data.length-1,1);
  // skillqueue
  rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('-').setFontColor(defaultFontColor).setRanges([skillqueueEndsRange]).build()); // empty skillqueue
  rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo('done').setFontColor('#e67c73').setRanges([skillqueueEndsRange]).build()); // skillqueue done
  rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextStartsWith('0d').setFontColor('#ffd666').setRanges([skillqueueEndsRange]).build()); // less than 24h before skillqueue ends 
  rules.push(SpreadsheetApp.newConditionalFormatRule().whenCellNotEmpty().setFontColor('#57bb8a').setRanges([skillqueueEndsRange]).build()); // more than 24h in skillqueue (CellNotEmpty due to it's position in rules[] the other cases have priority)
  sheet.setConditionalFormatRules(rules); // also clears previous rules
}