自动将XML数据收集到电子表格 [英] Automated XML Data Collection to Spreadsheet

问题描述

我试图设置一个XML解析器，它自动收集数据并每小时将其附加到电子表格的底部。我收集的数据每小时更新一次，我希望尽可能保持最新状态。

我绘制的XML来自这里：
，格式如下：

 <？xml version ='1.0'encoding ='UTF-8'？> ; 
< eveapi version =1> 
< currentTime> 2007-12-12 11：50：38< / currentTime> 
<结果> 
  < / rowset> 
< dataTime> 2007-12-12 11：50：38< / dataTime> 
< / result> 
< cachedUntil> 2007-12-12 12：50：38< / cachedUntil> 
< / eveapi> 
  

当然，实际上有更多的行（每个集合4,000-5,000个）。

目前，我有一个Google电子表格，其中包含一个用于收集数据的脚本，这个脚本一直运行良好，直到我到达所要做的自动部分。这里是脚本：
$ b $ pre $ function EVEJumpsCollection（）{
var ss = SpreadsheetApp.openByUrl（
https ：？//docs.google.com/spreadsheet/ccc键= 0AgjR0Xz9d5o_dFc1RDdsYmZtNFo5eEp3a1FYZ2piT3c和放大器; USP =共享）;
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 ：真正}）;








当我手动运行它时，这很好用，但它是自动化的部分分解了。我已经尝试设置每小时时间驱动的触发器来运行脚本，但是我一遍又一遍地用自动脚本得到相同的错误：范围的坐标或尺寸是无效的（第13行，文件Code ）

我也认识到，即使我得到这个工作，我一次只能收集〜10小时的块（谢谢你400,000个单元的限制.. 。）

因此，我希望有任何建议：

1. 还有其他的方式来做我想要完成的事情吗？


2. 
   



解决方案

有趣的方法，但由于计算公式计算和脚本执行延迟，因此认为使用导入公式以及已触发的Google Apps脚本函数总是会遇到问题。



由于Google Apps脚本拥有自己的 XmlService Service ，它可以很好地处理格式良好的Xml。您可以使用您特定的Xml文档：



 函数EVEJumpsCollection（）{
 var output = []; 
 
 //由https://developers.google.com/apps-script/reference/xml-service/修改
 var url ='https://api.eveonline.com/地图/ Jumps.xml.aspx'; //其中
 var xml = UrlFetchApp.fetch（url）.getContentText（）; //获取
 var document = XmlService.parse（xml）; //解析
 
 //下一个导航到树的一部分并获取值
 var cachedUntil = document.getRootElement（）。getChild（cachedUntil）。getValue（）; 
 var currentTime = document.getRootElement（）。getChild（currentTime）。getValue（）; 
 
 //获取rowset元素并迭代到
 var rowset = document.getRootElement（）。getChild（result）。getChild（rowset）。getChildren（）; 
 for（var i = 0; i< rowset.length; i ++）{
 
 //提取属性值（注意区分大小写）
 var solarSystemID = rowset [i] .getAttribute（ solarSystemID）的getValue（）; 
 var shipJumps = rowset [i] .getAttribute（shipJumps）。getValue（）; 
 
 //更有效地建立对象[] []的值，并写一个
 output.push（[solarSystemID，shipJumps，cachedUntil，currentTime]）; 
} 
 
 //我们希望它去的地方
 var ss = SpreadsheetApp.openByUrl（https://docs.google.com/spreadsheet/ccc?key=0AgjR0Xz9d5o_dFc1RDdsYmZtNFo5eEp3a1FYZ2piT3c& ; USP =共享）; 
 var sheet = ss.getSheets（）[0]; 
 sheet.getRange（sheet.getLastRow（）+ 1，1，output.length，4）.setValues（output）; 
} 
  

关于Google表格填写，有几个选项。您可以创建脚本新的Google电子表格 ，它将这个.CSV文件写入Google Drive，这是最近的一个项目。另外值得注意的是，Stackoverflow上的类似Apps Script / XML 像这样使用旧的Xml服务已弃用。


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?

解决方案

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.

这篇关于自动将XML数据收集到电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！