Importing Baro Ki'Teer's inventory into a spreadsheet

README.md

How to use this

One-time setup:

1. Create a new Google Spreadsheet: https://docs.google.com/spreadsheets/create
2. Go to the menu Tools -> Script editor. This should open a new tab from script.google.com
3. Paste the whole content of Code.js file from this gist into the script editor, replacing whatever is there.
4. Hit Save (and enter a name for the script in the prompt that pops up).
5. Close script editor.
6. Reload the spreadsheet.
7. After a few seconds you should see a new menu called Baro appear right after Help. Click Baro -> Create template sheet. Authorize the script.
8. Done! You should be looking at a new sheet called "Template" and no red error messages.

If you're playing not on PC, go to Baro -> Change platform and select your platform. This setting should persist along with the spreadsheet.

Actually using this:

Once Baro arrives, click Baro -> New sheet with current inventory. This will make a copy of your template sheet and insert Baro's inventory in it. Then you can put a number in Qty column to specify how many of each item you want to buy and set checkboxes in Bought column after you actually spend ducats and credits.

FAQ

Can I modify the template?

Absolutely! The only constraints are:

• The sheet must be named Template.
• The code will find the first row with first 3 columns empty (skipping row number 1) and populate first 3 columns of each row from there with 1) item name, 2) ducat price, 3) credit price.

Or, you know, you can take a look at the code and modify it to do whatever the hell you want.

Why is this not an addon for Google Sheets?

Because addon publishing process is a huge pain in the ass and it's much easier for me to write this README. If you're inspired to publish it - feel free to do so, the code is published under MIT license.

Code.js

/*
Copyright 2019 Max Ignatenko

Permission is hereby granted, free of charge, to any person obtaining a copy of
this software and associated documentation files (the "Software"), to deal in
the Software without restriction, including without limitation the rights to
use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of
the Software, and to permit persons to whom the Software is furnished to do so,
subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS
FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR
COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER
IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
*/

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Baro')
    .addItem('Create template sheet', 'makeTemplate')
    .addItem('New sheet with current inventory', 'makeSheet')
    .addItem('Selected platform: ' + getPlatformName() + ' (updates only on page reload :( ))', 'noop')
    .addSubMenu(ui.createMenu('Change platform')
      .addItem(platforms.pc.name, 'selectPC')
      .addItem(platforms.xb1.name, 'selectXB1')
      .addItem(platforms.ps4.name, 'selectPS4')
      .addItem(platforms.nsw.name, 'selectNSW')
    )
    .addToUi();
}

function noop() {}

function selectPC() {
  setPlatform('pc');
}

function selectXB1() {
  setPlatform('xb1');
}

function selectPS4() {
  setPlatform('ps4');
}

function selectNSW() {
  setPlatform('nsw');
}

function getPlatform() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ms = ss.getDeveloperMetadata();
  Logger.log(ms);
  for (var i in ms) {
    var m = ms[i];
    if (m.getKey() === 'platform') {
      return m.getValue();
    }
  }
  return 'pc';
}

function setPlatform(platform) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ms = ss.getDeveloperMetadata();
  var set = false;
  for (var i in ms) {
    var m = ms[i];
    if (m.getKey() === 'platform') {
      if (set) {
        // Clean up: nuke extra entries with 'platform' key.
        m.remove();
        continue;
      }
      m.setValue(platform);
      set = true;
    }
  }
  if (!set) {
    ss.addDeveloperMetadata('platform', platform);
  }
}

function getPlatformName() {
  return platforms[getPlatform()].name;
}

function changePlatform() {
  var ui = SpreadsheetApp.getUi();
}

function makeSheet() {
  var rows = getBaroData();
  if (rows === null) {
    SpreadsheetApp.getUi().alert("Couldn't get the current Baro inventory (probably because Baro haven't arrived yet)");
    return;
  }

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var tmpl = spreadsheet.getSheetByName('Template');

  if (tmpl === null) {
    SpreadsheetApp.getUi().alert('Please create a sheet called "Template"');
    return;
  }

  spreadsheet.setActiveSheet(tmpl);
  var output = spreadsheet.duplicateActiveSheet();

  var start = findFirstEmptyRow(output, 2, 3);
  output.getRange(start, 1, rows.length, 3).setValues(rows);

  output.autoResizeColumns(1, 3);

  var now = new Date();
  output.setName(now.toISOString().slice(0, "0000-00-00".length));
}

function findFirstEmptyRow(sheet, start, columns) {
  for (var i = start; i < 1000; i++) {
    var vals = sheet.getRange(i, 1, 1, columns).getValues()[0];
    var empty = true;
    for (var j in vals) {
      if (vals[j] !== '') {
        empty = false;
        break;
      }
    }
    if (empty) {
      return i;
    }
  }
  throw "Couldn't find an empty row";
}

function makeTemplate() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var tmpl = spreadsheet.getSheetByName('Template');

  if (tmpl !== null) {
    SpreadsheetApp.getUi().alert('A sheet called "Template" already exists. Please delete or rename it.');
    return;
  }

  var sheet = spreadsheet.insertSheet('Template');
  sheet.setFrozenRows(3);
  var set = function(cell, value) {
    sheet.getRange(cell).setValue(value);
  };
  sheet.getRange("E1:G1").setValues([
    ['Ducats', 'Credits', 'Bought']
  ]);
  sheet.getRange("E2:H3").setValues([
    ['=SUMIF($G$4:$G; "=TRUE"; E$4:E)', '=SUMIF($G$4:$G; "=TRUE"; F$4:F)', '', '<- Spent'],
    ['=SUMIF($G$4:$G; "<>TRUE"; E$4:E)', '=SUMIF($G$4:$G; "<>TRUE"; F$4:F)', '', '<- Need']
  ]);
  sheet.getRange("A3:D3").setValues([
    ['Item', 'Ducats', 'Credits', 'Qty']
  ]);
  sheet.getRange("B2:C2").merge().setValue('Price').setHorizontalAlignment('center');
  sheet.getRange("E4:F4").setValues([
    [
      '=ARRAY_CONSTRAIN(ARRAYFORMULA(B4:B*D4:D); COUNTA(B4:B); 1)',
      '=ARRAY_CONSTRAIN(ARRAYFORMULA(C4:C*D4:D); COUNTA(C4:C); 1)',
    ]
  ]);
  sheet.getRange("G4:G30").setDataValidation(
    SpreadsheetApp.newDataValidation().requireCheckbox().build()
  );

  sheet.getRange("A4:C4").setValues([
    ['Dummy', 0, 0]
  ]);
  sheet.hideRows(4);

  sheet.autoResizeColumns(2, 7);
}

function getBaroData() {
  var ws = fetchWorldState(getPlatform());
  var s = ws['VoidTraders'][0];
  var d = Baro.getData(s);
  if (!d.hasOwnProperty('items')) return null;

  var rows = [];
  d.items.forEach(function(item) {
    var r = [];
    if (item.hasOwnProperty('url')) {
      r.push('=HYPERLINK("' + item.url + '"; "' + item.name + '")')
    } else {
      r.push(item.name);
    }
    r.push(item.ducats);
    r.push(item.credits);
    rows.push(r);
  })
  return rows;
}

var Baro = {
  getWikiLink: function(item) {
    var url = 'https://warframe.fandom.com/wiki/' + item.replace(/ /g, '_');
    Logger.log(url);
    var resp = UrlFetchApp.fetch(url, {
      muteHttpExceptions: true
    });
    Logger.log(resp.getResponseCode());
    if (resp.getResponseCode() == 200) {
      return url;
    }
    return null;
  },
  getData: function(rawData) {
    var d = {
      location: rawData['Node'],
      activation: parseInt(rawData['Activation']['$date']['$numberLong'], 10) / 1000,
      expiry: parseInt(rawData['Expiry']['$date']['$numberLong'], 10) / 1000,
    };

    if (rawData.hasOwnProperty('Manifest')) {
      var resp = UrlFetchApp.fetch('https://raw.githubusercontent.com/WFCD/warframe-worldstate-data/master/data/languages.json');
      if (resp.getResponseCode() != 200) {
        throw resp;
      }
      var itemData = JSON.parse(resp.getContentText());

      d.items = rawData['Manifest'].map(function(item) {
        var r = {
          ducats: parseInt(item['PrimePrice'], 10),
          credits: parseInt(item['RegularPrice'], 10),
        };

        var desc = itemData[item['ItemType'].toLowerCase()];
        Logger.log(desc);
        if (desc) {
          r.name = desc.value;
        } else {
          var p = item['ItemType'].split('/');
          r.name = p[p.length - 1];
        }
        var url = Baro.getWikiLink(r.name);
        if (url !== null) {
          r.url = url;
        }
        return r;
      });
    }

    return d;
  },
};

var platforms = {
  pc: {
    url: 'http://content.warframe.com/dynamic/worldState.php',
    name: 'PC'
  },
  xb1: {
    url: 'http://content.xb1.warframe.com/dynamic/worldState.php',
    name: 'Xbox One'
  },
  ps4: {
    url: 'http://content.ps4.warframe.com/dynamic/worldState.php',
    name: 'PlayStation 4'
  },
  nsw: {
    url: 'http://content.swi.warframe.com/dynamic/worldState.php',
    name: 'Nintendo Switch'
  },
};


function fetchWorldState(platform) {
  if (!platforms.hasOwnProperty(platform)) {
    throw 'Unknown platform: ' + platform;
  }

  var resp = UrlFetchApp.fetch(platforms[platform].url);

  if (resp.getResponseCode() != 200) {
    throw resp;
  }

  return JSON.parse(resp.getContentText());
}