CODEWITHSUNDEEP
xmlgoogle-apps-script
Calvin_xc1
Calvin_xc1

Reputation: 127

Automated XML Data Collection to Spreadsheet

I'm trying to set up an XML parser that automatically collects data and appends it to the bottom of a spreadsheet every hour. The data I'm collecting updates every hour and I want to keep current with it as much as possible.

The XML I'm drawing from is from here: https://api.eveonline.com/map/Jumps.xml.aspx and is formatted as such:

<?xml version='1.0' encoding='UTF-8'?>
<eveapi version="1">
  <currentTime>2007-12-12 11:50:38</currentTime>
  <result>
    <rowset name="solarSystems" key="solarSystemID" columns="solarSystemID,shipJumps">
      <row solarSystemID="30001984" shipJumps="10" />
    </rowset>
    <dataTime>2007-12-12 11:50:38</dataTime>
  </result>
  <cachedUntil>2007-12-12 12:50:38</cachedUntil>
</eveapi>

Naturally there's a lot more rows in the actual (4,000-5,000 per collection).

Currently I have a google spreadsheet with a script set up to collect the data, which works fine up until I get to the 'automated' part of what I'm trying to do. Here's the script:

function EVEJumpsCollection() {
  var ss = SpreadsheetApp.openByUrl(
     "https://docs.google.com/spreadsheet/ccc?key=0AgjR0Xz9d5o_dFc1RDdsYmZtNFo5eEp3a1FYZ2piT3c&usp=sharing");
   var sheet = ss.getSheets()[0];

  var newrowstart = sheet.getLastRow()+1;

  sheet.appendRow(['=ImportXML("https://api.eveonline.com/map/Jumps.xml.aspx", "/eveapi/result/rowset/row/@solarsystemid")', '=ImportXML("https://api.eveonline.com/map/Jumps.xml.aspx", "/eveapi/result/rowset/row/@shipjumps")', '=ImportXML("https://api.eveonline.com/map/Jumps.xml.aspx", "//eveapi/cachedUntil")', '=ImportXML("https://api.eveonline.com/map/Jumps.xml.aspx", "//eveapi/currentTime")']);

  var newrowend = sheet.getLastRow();

  sheet.getRange(newrowstart,3,1,2)
  .copyTo(sheet.getRange(newrowstart+1, 3, newrowend-newrowstart,2),{contentsOnly:true});

  sheet.getRange(newrowstart,1,newrowend-newrowstart+1,4)
  .copyTo(sheet.getRange(newrowstart,5,newrowend-newrowstart+1,4),{contentsOnly:true});

}

This works great when I run it manually, but it's the automated part the breaks down. I've tried setting up an hourly time-driven trigger to run the script, but I get the same error over and over again with the automated script: "The coordinates or dimensions of the range are invalid. (line 13, file "Code")"

I also recognize that even if I get this to work I'll only be collecting ~10 hour chunks at a time (thank you 400,000 cell limit...)

As such I'm hoping for any advice on either:

  1. Are there other ways of doing what I'm hoping to accomplish?
  2. Are there ways of re-tooling my current setup to work for what I want to do?

Upvotes: 2

Views: 3110

Answers (1)

mhawksey
mhawksey

Reputation: 2053

Interesting approach but think you'll always hit problems using import formula in combination with triggered Google Apps Script functions because of a delay in the formula calculating and the script executing.

As Google Apps Script has it's own XmlService Service which works great on well-formed Xml using this instead is a better approach. For your particular Xml document you can use:

function EVEJumpsCollection() {
  var output = [];

  // modified from https://developers.google.com/apps-script/reference/xml-service/
  var url = 'https://api.eveonline.com/map/Jumps.xml.aspx'; // where
  var xml = UrlFetchApp.fetch(url).getContentText(); // fetch
  var document = XmlService.parse(xml); // parse

  // next nav to part of tree and get values
  var cachedUntil = document.getRootElement().getChild("cachedUntil").getValue();
  var currentTime = document.getRootElement().getChild("currentTime").getValue();

  // get rowset elements and iterate accross
  var rowset = document.getRootElement().getChild("result").getChild("rowset").getChildren();
  for (var i = 0; i < rowset.length; i++) {

    // extracting attribute values (note case sensitive)
    var solarSystemID = rowset[i].getAttribute("solarSystemID").getValue();
    var shipJumps = rowset[i].getAttribute("shipJumps").getValue();

    // more effiecient to build object[][] of values and do one write
    output.push([solarSystemID, shipJumps, cachedUntil, currentTime]);
  }

  // where we want it to go
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheet/ccc?key=0AgjR0Xz9d5o_dFc1RDdsYmZtNFo5eEp3a1FYZ2piT3c&usp=sharing");
  var sheet = ss.getSheets()[0];
  sheet.getRange(sheet.getLastRow()+1, 1, output.length, 4).setValues(output);
}

In terms of the Google Sheet filling up there are a couple of options. You could script the creation of a new Google Spreadsheet each time it runs or for this recent project it writes a .CSV file to Google Drive. Also worth noting that similar Apps Script/XML on Stackoverflow like this one use the old Xml Service which is deprecated.

Upvotes: 2

Related Questions