Google表格:如何导入以下数据?

问题描述

数据源
https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yieldAll

我正在尝试将以下数据保存到Google表格中，但是使用IMPORTXML这样做很棘手.知道怎么做吗?

I am trying to get the following data onto a Google Sheet, but it is looking to be tricky to do so using IMPORTXML. Any idea how to do it?

推荐答案

• 您要从URL的HTML数据中检索表.
  • 我想从我试图将以下数据存储到Google表格上.

  如果我的理解正确，那么这个答案如何?

  If my understanding is correct, how about this answer?

  不幸的是，HTML的文件大小似乎很大.因此，当 = IMPORTXML("https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yieldAll"，"//title";)，URL资源中的 Resource错误超过最大大小..当我从URL检索HTML数据时，HTML数据的大小约为9 MB.认为错误的原因是由于此.因此，作为一种解决方法，如何使用Google Apps脚本?在这种解决方法中，使用以下流程.

  Unfortunately, it seems that the file size of HTML is large. So when =IMPORTXML("https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yieldAll","//title") is used, an error of Resource at url contents exceeded maximum size. occurs. When I retrieve HTML data from the URL, the size of HTML data was about 9 MB. It is considered that the reason of error is due to this. So as one of workaround, how about using Google Apps Script? In this workaround, the following flow is used.

  1. 使用 UrlFetchApp
  检索HTML数据
  2. 使用作为GAS库的 Parser 解析HTML数据.
  3. 使用Sheets API的 PasteDataRequest 将已解析的数据放入电子表格的活动工作表中.
  1. Retrieve HTML data using UrlFetchApp
  2. Parse the HTML data using Parser which is a GAS library.
  3. Put the parsed data to the active sheet on the Spreadsheet using PasteDataRequest of Sheets API.

  用法:

  准备工作:

  1. 请安装解析器.关于库的安装，您可以在此处看到它.

  1. Please install Parser. About the install of library, you can see it at here.

  • 该库的项目密钥为 M1lugvAXKKtUxn_vdAG9JZleS6DrsjUUV .

  请在高级Google服务中启用Sheets API .

  示例脚本:

  请将以下脚本复制并粘贴到电子表格的容器绑定脚本的脚本编辑器中.完成上述设置后，请运行 myFunction()函数.运行脚本时，HTML表将被放置到电子表格上的活动表中.

  Sample script:

  Please copy and paste the following script to the script editor of the container-bound script of the Spreadsheet. After above settings were done, please run the function of myFunction(). When the script is run, the table of HTML is put to the active sheet on the Spreadsheet.

  function myFunction() {
    // Retrieve HTML data from URL.
    var url = "https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yieldAll";
    var html = UrlFetchApp.fetch(url).getContentText();
  
    // Parse HTML data.
    var table = "<table" + Parser.data(html).from("<table class=\"t-chart\"").to("</table>").build() + "</table>";
  
    // Put the values to the Spreadsheet.
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var resource = {requests: [{pasteData: {html: true, data: table, coordinate: {sheetId: sheet.getSheetId()}}}]};
    Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
  }
  

  参考文献:

  • 解析器
  • PasteDataRequest
  • 高级Google服务

  如果我误解了你的问题，而这不是你想要的方向，我深表歉意.

  If I misunderstood your question and this was not the direction you want, I apologize.

  面向Google Apps脚本的新IDE终于在2020年12月7日发布.Ref 到此，在当前阶段，要安装Google Apps脚本库，需要使用Google Apps Script项目的脚本ID.

  New IDE for Google Apps Script has finally been released at December 7, 2020. Ref By this, in the current stage, in order to install Google Apps Script library, it is required to use the script ID of Google Apps Script project.

  在这种情况下，当解析器已安装，很遗憾，此ID M1lugvAXKKtUxn_vdAG9JZleS6DrsjUUV 无法使用.

  In this case, when the Google Apps Script library of Parser is installed, unfortunately, this ID M1lugvAXKKtUxn_vdAG9JZleS6DrsjUUV cannot be used.

  因此，当您使用新的IDE时，请使用以下脚本ID.

  So when you use new IDE, please use the following script ID.

  1Mc8BthYthXx6CoIz90-JiSzSafVnT6U3t0z_W3hLTAX5ek4w0G_EIrNw
  

  此脚本ID是 M1lugvAXKKtUxn_vdAG9JZleS6DrsjUUV 的Google Apps脚本项目的ID.这样，就可以将Parser的库安装到新的IDE中.

  This script ID is the ID of Google Apps Script project of M1lugvAXKKtUxn_vdAG9JZleS6DrsjUUV. By this, the library of Parser can be installed to the new IDE.

  关于安装库的方法，您可以参阅官方文档.

  About the method for installing the library, you can see the official document.

  • 图书馆

  这篇关于Google表格:如何导入以下数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！