从html解析数据获取 [英] parse data fetch from html

问题描述

我试图从美国联邦储备中获得一些数据并粘贴到我的电子表格中。

所以这是我的代码

  function myFunction（）{
 var response = UrlFetchApp.fetch（http://research.stlouisfed.org/fred2/data/TWEXMANL.txt ）; 
 a = response.getContentText（）; 
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var InputSheet = ss.getSheetByName（Sheet5）; 
 InputSheet.getRange（E5：E5）。setValue（a）; 
} 
  

此代码将所有输出粘贴到E5中。我只想将日期和值后面的数据粘贴到2列中。

任何人都可以帮助我吗？我确信这对你们来说很简单。

谢谢。

  function myFunction（）{
 var response = UrlFetchApp.fetch（ http://research.stlouisfed.org/fred2/data/TWEXMANL.txt）; 
 a = response.getContentText（）; 
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var InputSheet = ss.getSheetByName（Sheet1）; 
 
 //删除列上方的文本
 var allData = a.slice（a.indexOf（DATE VALUE））; 
 
 //将日期和值之间的空格修剪为仅用于拆分的空间
 allData = allData.replace（/ + / g，）; 
 
 //将文本分割为每行一行
 var splitData = allData.split（\\\
）; 
 
 //出于某种原因，有一个额外的行，所以删除
 splitData.pop（）; 
 
 //将每行拆分为数据为一列，值为另一个
（var i = 0; i< splitData.length; i + = 1）{
 splitData [i] = splitData [i] .split（）; 
} 
 
 //保存到电子表格。 +4因为你从E5开始。请注意右侧的F更改。 
 InputSheet.getRange（E5：F+（splitData.length + 4））。setValues（splitData）; 
} 
  

我首先介绍一下介绍性文字。然后我将字符串分割成一个包含数据行的数组。我也将每一行（一个字符串）分成一个数组。这给了我一个数组，我可以在底部使用 setValues（）。因为这不只是一个单元格，我必须具体确切的宽度和高度，否则将失败。

如果您希望在任何时候看到数据在那里，我推荐 Logger.log（splitData）;

I'm trying to get some data from the federal reserve and paste into my spreadsheet.

So here is my code

function myFunction() {
var response = UrlFetchApp.fetch("http://research.stlouisfed.org/fred2/data/TWEXMANL.txt");
a=response.getContentText();
var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var InputSheet = ss.getSheetByName("Sheet5"); 
InputSheet.getRange("E5:E5").setValue(a); 
}

This code pastes all of the output into E5. I just want the data following the "Date" and "Value" pasted into 2 columns.

Can anybody help me with this? I'm sure it's pretty simple for you guys.

Thanks.

解决方案

Using a little string manipulation, this becomes very easy:

function myFunction() {
  var response = UrlFetchApp.fetch("http://research.stlouisfed.org/fred2/data/TWEXMANL.txt");
  a=response.getContentText();
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var InputSheet = ss.getSheetByName("Sheet1"); 

  //Slice off the text above the columns
  var allData = a.slice(a.indexOf("DATE         VALUE"));

  //Trim spaces between date and value to just 1 space for splitting
  allData = allData.replace(/ +/g, " ");

  //Split the text so each line is a row
  var splitData = allData.split("\n");

  //For some reason, there is an extra line, so remove it
  splitData.pop();

  //Split each row so data is one column and value is another
  for(var i = 0; i < splitData.length; i += 1) {
    splitData[i] = splitData[i].split(" ");
  }

  //Save to spreadsheet. +4 because you start at E5. Note the change to F on the right.
  InputSheet.getRange("E5:F" + (splitData.length + 4)).setValues(splitData);
}

I start by taking off the little intro text. I then split the string into an array containing the rows of data. I split each row (a string) into an array as well. This gives me a array I can use setValues() on at the bottom. Because this isn't just one cell, I have to specific the exact width and height or it fails.

If you'd like to see the data at any point in there, I recommend Logger.log(splitData);

这篇关于从html解析数据获取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！