用 URLFetchApp 替换 =ImportHTML

问题描述

我一直在使用 ImportHTML 从这个链接中提取 MLB 统计数据，没有问题:"https://widgets.sports-reference.com/wg.fcgi?css=1&site=br&url=%2Fleagues%2FMLB%2F2021-standard-batting.shtml&div=div_players_standard_batting".我只是在 Google 表格上复制表格.

I've been using ImportHTML to draw MLB stats from this link without issue: "https://widgets.sports-reference.com/wg.fcgi?css=1&site=br&url=%2Fleagues%2FMLB%2F2021-standard-batting.shtml&div=div_players_standard_batting". I was simply reproducing the table on a Google Sheet.

但是，源表现在变得如此之大，以至于我的工作表中出现此错误:url 内容中的资源超出了最大大小."

However, the source table has now grown so large that I'm getting this error in my Sheet: "Resource at url contents exceeded maximum size."

我知道我需要转向 Google Apps Script 和 URLFetchApp，但我的业余方法只产生空单元格.我可以使用这种方法在我的链接中重现表格吗?

I know I need to pivot to a Google Apps Script and URLFetchApp, but my amateur approach produces nothing but empty cells. Is it possible for me to reproduce the table in my link using this approach?

感谢您的指导和您的时间.

Thanks for any guidance and your time.

推荐答案

从表中检索数据的更好方法:

A better way to retrieve data from table :

function importTableHTML(url) {
  var html = '<table' + UrlFetchApp.fetch(url).getContentText().match(/(?<=<table).*(?=</table)/g) + '</table>';
  var trs = [...html.matchAll(/<tr[sSw]+?</tr>/g)];
  var data = [];
  for (var i=0;i<trs.length;i++){
    var tds = [...trs[i][0].matchAll(/<(td|th)[sSw]+?</(td|th)>/g)];
    var prov = [];
    for (var j=0;j<tds.length;j++){
      donnee=tds[j][0].match(/(?<=>).*(?=</)/g)[0].replace(/&nbsp;/g,' ');
      if(donnee.indexOf("</a>")>-1){
        prov.push(donnee.match(/(?<=>).*(?=</)/g)[0]);
      }else{
        prov.push(donnee);
      }
    }
    data.push(prov);
  }
  return(data);
}

这篇关于用 URLFetchApp 替换 =ImportHTML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！