ImportHTML - "Error Resource at url contents exceeded maximum size."

user_17380150 0
Viewed 97

Hoping someone in here can help,

I have been pulling player prop data for the last couple years and it has worked fine, but this year they added more selections to the drop down and I get the error "Resource at url contents exceeded maximum size" anytime there are more than 5 games.

The function we use is =importhtml("https://www.scoresandodds.com/nba/props", "table",1)

Hoping somebody can point me in the right direction to get this working again, if possible at all. I am not very familiar with scripts and from what I've gathered that is the only way to work around this error.

Thank you for your time!

google-apps-script google-sheets
2 Answers

In your situation, how about using Sheets API with Google Apps Script? When the pasteData request of Sheets API is used, the HTML table can be parsed and put to the Spreadsheet. When this is reflected to the script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet, and please enable Sheets API at Advanced Google services. And, please run myFunction at the script editor. By this, the retrieved table is put to the sheet.

function myFunction() {
  const sheetName = "Sheet1";  // Please set the destination sheet name.
  const url = "https://www.scoresandodds.com/nba/props";

  const html = UrlFetchApp.fetch(url).getContentText();
  const table = html.match(/<table[\s\S\w]+?<\/table>/);
  if (table) {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const requests = { requests: [{ pasteData: { html: true, data: table[0], coordinate: { sheetId: ss.getSheetByName(sheetName).getSheetId() } } }] };
    Sheets.Spreadsheets.batchUpdate(requests, ss.getId());
    return;
  }
  throw new Error("Table cannot be retrieved.");
}

References:

user_71086530

Try this script

function importTableHTML(url) {
  var html = '<table' + UrlFetchApp.fetch(url).getContentText().match(/(?<=\<table).*(?=\<\/table)/g) + '</table>';
  var trs = [...html.matchAll(/<tr[\s\S\w]+?<\/tr>/g)];
  var data = [];
  for (var i=0;i<trs.length;i++){
    var tds = [...trs[i][0].matchAll(/<(td|th)[\s\S\w]+?<\/(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);
}
user_150193800
Related