archiel.js

var FIO_URL = "https://rest.fnar.net/";
var SELF_URL = "https://docs.google.com/spreadsheets/d/1kKyHtXhu4D2Jumi06K4VTvbCN_zpBwleaxK30nhCjWg/edit#gid=0";
var POSTFIX = "?randomizer=";

var requestCount;
var spreadsheetLog;
var playerActivitySheet;
var planetTrackerSheet;
var runDate;

function RunAll() {
  runDate = Date.now();
  requestCount = Math.floor(Math.random() * 1001);
  spreadsheetLog = SpreadsheetApp.openByUrl(SELF_URL);
  playerActivitySheet = spreadsheetLog.getSheetByName("Player activity");

  // player activity sheet.
  var rows = ReadCorp();
  for(var i = 0; i < rows; i++){
    try{
      ReadIndividual(i);
      ReadBaseList(i);
    }catch (e){
      console.error(e);
    }
  }

  // Planet tracker sheet.
  // RunPlanetSiteCountAndPopUpdate();
  // RunPlanetAndOfficeUpdate();
}

function ReadCorp() {
  data = ImportJSON(FIO_URL + "user/corporation/OOG" + POSTFIX, "",  "noHeaders")

  var rows = data.length;
  var cells = playerActivitySheet.getRange(4, 2, rows, data[0].length);
  cells.setValues(data);
  return rows;
}

function ReadIndividual(row) {

  var co = playerActivitySheet.getRange(4 + row, 3).getValue();

  var COMPANY_START_ROW = 5;
  var COMPANY_UPDATE_DATE = 16;

  var corpLoadedAt = playerActivitySheet.getRange(4 + row, COMPANY_UPDATE_DATE).getValue();
  if(corpLoadedAt == ""){

    url = FIO_URL + "company/code/" + co + POSTFIX + (requestCount++);
    Logger.log('ReadIndividual row. ' + row + " result missing. Requesting: " + url);

    data = ImportJSON(url, "/CompanyName,/SubscriptionLevel,/Tier,/CreatedEpochMs,/Timestamp",  "noHeaders")
    
    var cells = playerActivitySheet.getRange(4 + row, COMPANY_START_ROW, 1, data[0].length);
    cells.setValues(data);

    var cells = playerActivitySheet.getRange(4 + row, COMPANY_UPDATE_DATE, 1, 1);
    cells.setValues( [ [ runDate ] ] );

  }else{
    Logger.log('ReadIndividual row ' + row + " ReadIndividual result already loaded at: " + corpLoadedAt);

  }
}

function ReadBaseList(row) {

  var BASE_START_ROW = 19;
  var BASE_UPDATE_DATE = 17;

  var co = playerActivitySheet.getRange(4 + row, 3).getValue();
  var baseUpdateDate = playerActivitySheet.getRange(4 + row, BASE_UPDATE_DATE).getValue();

  if(baseUpdateDate == ""){

    url = FIO_URL + "company/code/" + co + POSTFIX + (requestCount++);
    Logger.log('ReadBaseList row. ' + row + " result missing. Requesting: [" + url + "]");

    data = ImportJSON(url, "/Planets/PlanetName",  "noHeaders")
    cols = data.length;
    if(data == null || data[0] == null) return;
    
    Logger.log("rows: " + data[0].length + " cols: " + cols + " data: " + data);
    var cells = playerActivitySheet.getRange(4 + row, BASE_START_ROW, 1, cols);
    cells.setValues( [data] );

    var cells = playerActivitySheet.getRange(4 + row, BASE_UPDATE_DATE, 1, 1);
    cells.setValues( [ [ runDate ] ] );

  }else{
    Logger.log('ReadBaseList row: ' + row + " ReadBaseList result already loaded at: " + baseUpdateDate);

  }
}

function ReadOffices(row) {

  var co = playerActivitySheet.getRange(4 + row, 3).getValue();

  var loaded = playerActivitySheet.getRange(4 + row, 48).getValue();

  if(loaded == ""){

    url = FIO_URL + "company/code/" + co + POSTFIX + (requestCount++);
    Logger.log('row. ' + row + " result missing. Requesting: " + loaded + "  " + url);

    data = ImportJSON(url, "/Offices/PlanetName,/Offices/EndEpochMs",  "noHeaders")
    cols = data.length;
    Logger.log("rows: " + data[0].length + " cols: " + cols + " data: " + data);
    var cells = playerActivitySheet.getRange(4 + row, 48, 1, cols);
    cells.setValues( [data] );
  }else{
    Logger.log('row. ' + row + " ReadOffices result already loaded: " + loaded);

  }
}

function RunPlanetAndOfficeUpdate() {
  requestCount = Math.floor(Math.random() * 101);
  spreadsheetLog = SpreadsheetApp.openByUrl(SELF_URL);
  planetTrackerSheet = spreadsheetLog.getSheetByName("PlanetTracker");
  var row = 0;

  for(var i = 0; i < 102; i++){
    row = i;
    var planet = planetTrackerSheet.getRange(2 + row, 2).getValue();
    var loaded = planetTrackerSheet.getRange(4 + row, 4).getValue();

    if(loaded == "" && planet != ""){

      url = FIO_URL + "planet/" + planet + POSTFIX + (requestCount++);
      Logger.log('row. ' + row + " result missing. Requesting: " + loaded + "  " + url);

      data = ImportJSON(url, "/GoverningEntity,/FactionCode,/HasChamberOfCommerce,/HasWarehouse,/HasAdministrationCenter",  "noHeaders")
      cols = data[0].length;
      Logger.log("rows: " + data[0].length + " cols: " + cols + " data: " + data);
      var cells = planetTrackerSheet.getRange(2 + row, 4, 1, cols);
      cells.setValues( data );
    }else{
      Logger.log('row. ' + row + " RunPlanetUpdate result already loaded: " + loaded);
    }
  
    var startCol = 24;
    // var loaded2 = planetTrackerSheet.getRange(2 + row, startCol).getValue();

    // if(loaded2 == ""){
    //   var username = planetTrackerSheet.getRange(2 + row, 8).getValue();

    //   if(username != ""){
    //     url = FIO_URL + "user/" + username + POSTFIX + (requestCount++);
    //     Logger.log('row. ' + row + " result missing. Requesting: " + loaded + "  " + url);

    //     data = ImportJSON(url, "/Offices/PlanetName,/Offices/EndEpochMs",  "noHeaders")
    //     var lastDate = 0;
    //     for(var ii = 0; ii < data.length; ii++){
    //       if(data[ii][0] == planet){
    //         var newDate = data[ii][1];
    //         if(newDate > lastDate) lastDate = newDate;
    //       }
    //     }
    //     Logger.log("GovData rows: " + " cols: " + data.length);
    //     if(data.length > 0){
    //       var cells = planetTrackerSheet.getRange(2 + row, startCol, 1, 1);
    //       cells.setValues( [[lastDate / 1000]] );
    //     }
    //   }
    // }else{
    //   Logger.log('row. ' + row + " RunPlanetUpdateGov result already loaded: " + loaded);
    // }
  }
}

function RunPlanetSiteCountAndPopUpdate() {
  requestCount = Math.floor(Math.random() * 101);
  spreadsheetLog = SpreadsheetApp.openByUrl(SELF_URL);
  planetTrackerSheet = spreadsheetLog.getSheetByName("PlanetTracker");
  var row = 0;

  for(var i = 0; i < 98; i++){
    var startCol = 14;
    var row = i;
    var planet = planetTrackerSheet.getRange(2 + row, 2).getValue();
    var loaded = planetTrackerSheet.getRange(2 + row, startCol).getValue();

    if(loaded == "" && planet != ""){
      url = FIO_URL + "planet/sitescount/" + planet + POSTFIX + (requestCount++);
      Logger.log('sitecount row. ' + row + " result missing. Requesting: " + url);

      data = ImportJSON(url)
      cols = data[0].length;
      Logger.log("rows: " + data[0].length + " cols: " + cols + " data: (" + data + ")");
      var cells = planetTrackerSheet.getRange(2 + row, startCol, 1, 1);
      cells.setValues( [data[1]] );
    }else{
      Logger.log('sitecount row. ' + row + " sitecount result already loaded: " + loaded);
    }
  
    startCol = 17;
    loaded2 = planetTrackerSheet.getRange(2 + row, startCol).getValue();

    if(loaded2 == "" && planet != ""){

      url = FIO_URL + "infrastructure/" + planet + POSTFIX + (requestCount++);
      Logger.log('row. ' + row + " result missing. Requesting: " + loaded2 + "  " + url);

      data = ImportJSON(url, "/InfrastructureReports/TimestampMs,/InfrastructureReports/NextPopulationPioneer,/InfrastructureReports/NextPopulationSettler,/InfrastructureReports/NextPopulationTechnician,/InfrastructureReports/NextPopulationEngineer,/InfrastructureReports/NextPopulationScientist", "noHeaders")

      Logger.log("infrastructure rows: " +data[0].length + " cols: " + data.length);
      if(data.length > 0){
        var cells = planetTrackerSheet.getRange(2 + row, startCol, 1, data[data.length-1].length);
        cells.setValues( [data[data.length-1]] );
      }
    }else{
      Logger.log('infrastructure row. ' + row + " RunPlanetUpdateGov result already loaded: " + loaded2);

    }
  }

}