Prosperous Universe Apps Script vs IMPORTDATA

notes.md

If you're new to sheets/prun or just unfamiliar with apps script, a lot of you are probably just using something like =IMPORTDATA("https://rest.fnar.net/csv/prices") As you may have noticed, sometimes this can error out and cause your sheet to be broken. I'd like to tell you there's another option. You can use google apps script with a timer to query the same exact data, but only update the sheet if it was successful.

To get to apps script, open a spreadsheet and then go to Extensions then Apps Script. if you've never used it before on this sheet, you'll be greeted with an empty function. You can simply replace the entire file with the prunscript.js file.

In the script, I have commented out the burn and inventory endpoints. If you want to use these, you'll need to uncomment them and:

1. Create an appropriate sheet and name it
2. Update the name on lines 27 and 33
3. Update your API key and username on lines 24 and 30

Once you've updated your sheet with the apps script, you'll want to run the importCSVFromUrl function. This will prompt you to authorize the script. After you run it you should see "updating prices" and "updated prices" in the log, and updated data on the "prices" sheet. Next you can run the createTrigger function and that will automatically create a timer that runs once an hour to update the sheet. You can click on the clock in the left bar to adjust how often the timer runs.

It's important to note that the script is written in such a way that any data on the managed sheets will be blown away. It will actually look at rows and columns 10x bigger than the data, just in case FIO data gets deleted between invocations.

I have created an example sheet here: https://docs.google.com/spreadsheets/d/1nSNnOlS11-ctW8Ky98O3kisieDu3GKyIRA2LLxn7xSI/edit?gid=0#gid=0

prunscript.js

function writeSheet(sheet, data, sort) {
  console.log("updating", sheet);
  var ss = SpreadsheetApp.getActive();
  sheet = ss.getSheetByName(sheet)
  var range = sheet.getRange(1, 1, data.length, data[0].length);
  var bigrange = sheet.getRange(1, 1, data.length*10, data[0].length*10);
  bigrange.clearContent();
  range.setValues(data);
  if (sort != '') {
    range = sheet.getRange(2, 1, data.length, data[0].length);
    range.sort({column: sort, ascending: false});
  }
  return sheet.getName();
}

function importCSVFromUrl() {
  var pricesUrl = "https://rest.fnar.net/csv/prices";
  var pricesContents = Utilities.parseCsv(UrlFetchApp.fetch(pricesUrl));
  if (pricesContents[0][0] == "Ticker") {
    var name = writeSheet("prices", pricesContents, '');
    console.log("updated", name);

  }
  // var burnUrl = "https://rest.fnar.net/csv/burnrate?apikey=APIKEY&username=USERNAME"
  // var burnContents = Utilities.parseCsv(UrlFetchApp.fetch(burnUrl));
  // if (burnContents[0][0] == "Username") {
  //   var name = writeSheet("burn rate", burnContents, 6);
  //   console.log("updated", name);
  // }
  // var inventoryUrl = 'https://rest.fnar.net/csv/inventory?apikey=APIKEY&username=USERNAME'
  // var inventoryContents = Utilities.parseCsv(UrlFetchApp.fetch(inventoryUrl));
  // if (inventoryContents[0][0] == "Username") {
  //   var name = writeSheet("inventory", inventoryContents, '');
  //   console.log("updated", name);
  // }
}

function createTrigger() {
  ScriptApp.newTrigger('importCSVFromUrl')
      .timeBased()
      .everyHours(1)
      .create();
}