code.gs

/*
 * Star Trek Timelines Datasheet Updater
 *
 * Updates the datasheet generated by datacore.app directly from JSON data contained in a google drive file.
 *
 * Released under an Apache 3.0 licence
 */
 
/* Used to identify the file on Google Drive that contains data from disruptorbeam.com/player */
const GOOGLE_DRIVE_FILEID = 'INSERT_ID_HERE';

const IMPORT_FUNCS = [
  { name : 'crew', func : importCrewData_ },
  { name : 'item', func : importItemData_ },
  { name : 'voyage', func : importVoyageData_ },
  { name : 'event', func : importEventData_ }
];

const doImport_ = (index) => importData_(IMPORT_FUNCS[index], fetchData_())
const crewDataImport = () => doImport_(0); 
const itemDataImport = () => doImport_(1);
const voyageDataImport = () => doImport_(2);
const eventDataImport = () => doImport_(3);

/**
 * Adds menu items for importing data
 * @param {Event} e The onOpen event.
 */
function onOpen(e) {
  // Add a custom menu to the spreadsheet.
  let menu = SpreadsheetApp.getUi() 
      .createMenu('Import data')
      .addItem('Import all data', 'importAllData')
      .addSeparator();
      
  IMPORT_FUNCS.forEach(info => menu.addItem(`Import ${info.name} data`, `${info.name}DataImport`));
  menu.addToUi();
}

fetchData_ = () => JSON.parse(DriveApp.getFileById(GOOGLE_DRIVE_FILEID)
                                      .getBlob()
                                      .getDataAsString());

const BATCH_IMPORT_ERROR = 363;
const OCCUPIED_RANGE_NOTE = "Data sheet used";
const OCCUPIED_PROMPT = `The sheet you are using already contains data that will be deleted. 
                          Do you want to do this?`;

function importAllData() {
  let successes = 0;
  
  IMPORT_FUNCS.forEach(info => {
    try {
      let data = fetchData_();
      importData_(info, data, true);
      ++successes;
    } catch (e) {
      if (e != BATCH_IMPORT_ERROR)
        throw e;
    }   
  });
  
  let message = successes ? `Successfully imported ${successes} data sets` : 
                            'Please import data set individually before using batch update';
  SpreadsheetApp.getUi().alert(message); 
}

function importData_(info, data = null, batch = false) {
  let ss = SpreadsheetApp.getActiveSpreadsheet(); 
  let rangeName = info.name + "DataImport";
  let range = ss.getRangeByName(rangeName);
  
  if (range == null && !batch) {
    range = ss.getActiveSheet().getRange(1, 1);
      
    if (range.getSheet().getLastRow() > 0) {
      let ui = SpreadsheetApp.getUi();
      let response = ui.alert(OCCUPIED_PROMPT, ui.ButtonSet.YES_NO);
                             
      if (response != ui.Button.YES)
        return;        
    }
    
    ss.setNamedRange(rangeName, range);
  } 
    
  if (range == null)
    throw BATCH_IMPORT_ERROR;
  
  if (info[0] == 'crew' && sheet.getLastRow() > 1) {
    // Attempt to find frozen crew in sheet
    let crewNames = sheet.getSheetValues(2, 1, sheet.getLastRow()).flat(); 
    let archetypeIds = sheet.getSheetValues(2, sheet.getLastColumn(), sheet.getLastRow()).flat();
    
    for (i in parsedData.player.character.stored_immortals) {
      let immortal = parsedData.player.character.stored_immortals[i];
      let crewIndex = archetypeIds.indexOf(immortal.id);
      
      if (crewIndex >= 0) {
        // Add to active crew
        data.crew.push({
          'name' : crewNames[crewIndex],
          'frozen' : true,
          'copies' : immortal.quantity});
      }
    }
  }

  let newContent = info.func(data);
  range.getSheet().clearContents();
  range.getSheet().getRange(1, 1, newContent.length, newContent[0].length).setValues(newContent);
}

function createColumnUpdaters_(immortalHeaders) {
  const playerSkill = (skill, type) => {
    let name = skill +  '_skill';
    
    if (type != 'core')
      type = 'range_' + type;
    
    return (data, id) => {
      let skills = new Object(data.skills);
    
      if (!Object.keys(skills).includes(name)) 
        return 0;
    
      
      return data.skills[name][type];
    };
  };
  
  const value = val => (d, id) => val;
  const fromPlayer = path => {
    let indicies = path.split('/');
    return (data, id) => {
      let retVal = data;
    
      for (index in indicies) 
        retVal = retVal[indicies[index]];
    
      return retVal;
    };
  };
  
  const fromImmortals = name => { 
    let index = immortalHeaders[1].indexOf(name); 
    return (pd, id) => id[index];
  };
  
  const immortalSkill = (skill, type) => {
    let index = immortalHeaders[0].indexOf(skill) + immortalHeaders[1].indexOf(type) 
                  - immortalHeaders[0].indexOf("Command");

    return (pd, id) => id[index];
  };
  
  const fromImmortalsNum = name => {
    let index = immortalHeaders[1].indexOf(name);
    return (pd, id) => parseInt(id[index].slice(1));
  };
  
  let rarityIndex = immortalHeaders[1].indexOf('Stars');
  let collectionsIndex = immortalHeaders[1].indexOf('Collection');
  let rarityMap = ["", ", Common", ", Uncommon", ", Rare"];
  const collections = (pd, id) => [id[collectionsIndex], id[rarityIndex] <= 3 ? rarityMap[id[rarityIndex]] : ""].join(', ');
  
  return [[
    fromPlayer('name'), value(true), fromPlayer('short_name'),
    fromPlayer('max_rarity'), fromPlayer('rarity'), fromPlayer('level'), value(0),
    (data, id) => { let values = []; for (equip in data.equipment) {values.push(data.equipment[equip][0]); }; return values.join(' '); },
    fromPlayer('equipment_rank'), fromImmortals('Portal'), collections, 
    fromImmortalsNum('VOY Rank'), fromImmortals('GPairs'),
    playerSkill('command', 'core'), playerSkill('command', 'min'), playerSkill('command', 'max'),
    playerSkill('diplomacy', 'core'), playerSkill('diplomacy', 'min'), playerSkill('diplomacy', 'max'),
    playerSkill('engineering', 'core'), playerSkill('engineering', 'min'), playerSkill('engineering', 'max'),
    playerSkill('medicine', 'core'), playerSkill('medicine', 'min'), playerSkill('medicine', 'max'),
    playerSkill('science', 'core'), playerSkill('science', 'min'), playerSkill('science', 'max'),
    playerSkill('security', 'core'), playerSkill('security', 'min'), playerSkill('security', 'max'),
    fromImmortals('Traits'), fromPlayer('archetype_id')
    // Not using ship stats for now
    //fromPlayer('action/name'), 
    //(data, id) => ['Attack','Evasion','Accuracy'][data.action.boost_type],
    //fromPlayer('action/bonus_amount'), fromPlayer('action/initial_cooldown'), fromPlayer('action/duration'), fromPlayer('action/cooldown'),
    //value(''), value(''), value(''), value(''), value(''), value(''), value(''),
  ],
  [
    fromImmortals("Name"), fromPlayer('frozen'), fromImmortals("Variant"),
    fromImmortals("Stars"), fromImmortals("Stars"), value(100), fromPlayer('copies'),
    value(''), value(9), fromImmortals('Portal'), collections,
    fromImmortalsNum('VOY Rank'), fromImmortals('GPairs'),
    immortalSkill('Command', 'Base'), immortalSkill('Command', 'Min'), immortalSkill('Command', 'Max'),
    immortalSkill('Diplomacy', 'Base'), immortalSkill('Diplomacy', 'Min'), immortalSkill('Diplomacy', 'Max'),
    immortalSkill('Engineering', 'Base'), immortalSkill('Engineering', 'Min'), immortalSkill('Engineering', 'Max'),
    immortalSkill('Medicine', 'Base'), immortalSkill('Medicine', 'Min'), immortalSkill('Medicine', 'Max'),
    immortalSkill('Science', 'Base'), immortalSkill('Science', 'Min'), immortalSkill('Science', 'Max'),
    immortalSkill('Security', 'Base'), immortalSkill('Security', 'Min'), immortalSkill('Security', 'Max'),
    fromImmortals('Traits'), fromPlayer('archetype_id', '')
  ]];
}

class CrewMap extends Map {
  add(character) {
      if (this.has(character.name)) 
        character.duplicate = this.get(character.name);
      else
        character.duplicate = false;
        
      this.set(character.name, character);
  }
}

/** Imports crew data from an JSON file on */
function importCrewData_(data) {
  // Data from Timelines Backend Stats Super Hyper Edition
  let immortalSheet = SpreadsheetApp.openById('1czPEYhyNszsx7BqzYsbicAHKK15w4hjTmYiSp_jKsgg')
                                    .getSheetByName('Data');
  let immortalHeaders = immortalSheet.getSheetValues(2, 1, 2, immortalSheet.getLastColumn());
  let immortalData = immortalSheet.getSheetValues(4, 1, immortalSheet.getLastRow() - 4, immortalSheet.getLastColumn());
  let [playerUpdaters, immortalUpdaters] = createColumnUpdaters_(immortalHeaders);
  let crewMap = new CrewMap();
  
  // Setup map of active crew
  for (i in data.player.character.crew) {
    let character = data.player.character.crew[i];
    
    if (!character.in_buy_back_state) {
      character.frozen = false;
      character.copies = 1;
      crewMap.add(character);
    }
  }
    
  let outputData = new Array();

  const headers = [
    'Name',	'Have', 'Short name', 
    'Max rarity', 'Rarity', 'Level', 'Frozen', 
    'Equipment', 'Tier', 'In portal', 'Collections',
    'Voyage Rank', 'Gauntlet Pairs',
    'Command Core', 'Command Min', 'Command Max',
    'Diplomacy Core', 'Diplomacy Min', 'Diplomacy Max', 
    'Engineering Core', 'Engineering Min', 'Engineering Max',
    'Medicine Core', 'Medicine Min', 'Medicine Max',
    'Science Core', 'Science Min', 'Science Max',
    'Security Core', 'Security Min', 'Security Max',
    'Traits', /* 'Action name', 'Boosts Amount',
    'Initialize	Duration', 'Cooldown', 'Bonus Ability',
    'Trigger Uses per Battle (Not Used)', 'Handicap Type (Not Used)','Handicap Amount (Not Used)',
    'Accuracy', 'Crit Bonus', 'Crit Rating', 'Evasion', 'Charge Phases (Not Used)', */
    'Character ID'];
  outputData.push(headers);
  
  let crewOwned = 0;
  let crewFrozen = 0;
  
  for (let row = 2; row < immortalData.length; ++row) {
    let crewName = immortalData[row][0];
    //console.info('Looking at ' + characterName);
    if (crewName == '')
      continue;
      
    let crew = crewMap.has(crewName) ? crewMap.get(crewName) : { name : crewName, frozen : false, copies : 0 };
          
    while (crew) {
      let outputRow = [];
      let updaters = !crew.frozen && crew.copies ? playerUpdaters : immortalUpdaters;
      
      for (let column = 0; column < headers.length; ++column)  
        outputRow.push(updaters[column](crew, immortalData[row]));
            if (crew.frozen) 
        crewFrozen += crew.copies;
      else
        crewOwned += crew.copies;
      
      outputData.push(outputRow);
      crew = crew.duplicate;
    }
  }
  
  //app.getUi().alert(`You own ${crewOwned} crew and have ${crewFrozen} in the freezer.`);
  return outputData;
}

/** Import Name, Rarity and quantity owned of every item into active sheet */
function importItemData_(data) {
  let items = data.player.character.items;
  let outputMap = new CrewMap();
  let output = [];
  
  output.push(['Name', 'Basic', 'Common', 'Uncommon', 'Rare', 'Super rare', 'Legendary']);
  
  for (i in items) {
    let item = items[i];
    if (!outputMap.has(item.name)) 
      outputMap.set(item.name, [item.name, 0, 0, 0, 0, 0, 0]);
    
    outputMap.get(item.name)[item.rarity + 1] = item.quantity;
  }
  
  let sortedKeys = Array.from(outputMap.keys()).sort();
  
  for (key in sortedKeys)
    output.push(outputMap.get(sortedKeys[key]));
    
  return output;
}

/** Import Voyage data into active sheet */
function importVoyageData_(data) {
  let output = [];
  let voyageData = data.player.character.voyage_descriptions[0];
  
  let skillTypeMap = new Map();
  skillTypeMap.set(voyageData.skills.primary_skill, 'Primary');
  skillTypeMap.set(voyageData.skills.secondary_skill, 'Secondary');
  
  const parseTrait = trait => trait.replace('_', ' ')
                                   .replace('doctor', 'Physician')
                                   .replace(/\b(\w)/g, c => c.toUpperCase());
  
  let current = data.player.character.voyage[0];
  let voyager = current ? slot => current.crew_slots[slot].crew.name : slot => "None";
  
  output.push(["Voyage Status:", "", current ? current.state : "Not out", "", "", ""]);
  output.push(['', '', '', '', '', '']);
  output.push(['Skill', 'Type', 'Trait 1', 'Voyager 1', 'Trait 2', 'Voyager 2']);
  
  let skillRows = [];
  for (let i = 0; i < voyageData.crew_slots.length; i += 2) {
    let slot1 = voyageData.crew_slots[i];
    let slot2 = voyageData.crew_slots[i+1];
    let skill = slot1.skill;
    
    skillRows.push([skill.charAt(0).toUpperCase() + skill.slice(1, -6), 
                    skillTypeMap.get(skill), 
                    parseTrait(slot1.trait), 
                    voyager(i),
                    parseTrait(slot2.trait),
                    voyager(i+1)]);
  }
  
  skillRows.sort((a, b) => a[0].localeCompare(b[0]));
  return [...output, ...skillRows];
}

function importEventData_(data, sheet) {
}