2

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!

Ryan
  • 23
  • 4

2 Answers2

1

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • I tried this one and it keeps giving me this error. ReferenceError: Sheets is not defined myFunction @ Code.gs:10 – Ryan Nov 11 '21 at 01:56
  • @Ryan Thank you for replying. I apologize for the inconvenience. In that situation, I'm worried that you didn't enable Sheets API at Advanced Google services. Could you please confirm it again? And test it again? – Tanaike Nov 11 '21 at 01:57
  • Yes, i have the API enabled and it is still giving me the error. Do I need to create credentials as well or not? – Ryan Nov 11 '21 at 02:03
  • @Ryan Thank you for replying. I apologize for the inconvenience. Can you provide the detail of `still giving me the error.`? And, for example, If your issue was not resolved, how about creating new Google Spreadsheet and copy and paste the script and enable Sheets API, and then, run the script, what result will you obtain? – Tanaike Nov 11 '21 at 02:05
  • I have Sheets API enabled and just tried the script on a new Google Spreadsheet. When I run the script, i get an error saying "ReferenceError: Sheets is not defined myFunction @ Code.gs:10" It does say once I enable the sheets API that I may need credentials to use the API. – Ryan Nov 11 '21 at 02:10
  • @Ryan Thank you for replying. I apologize for the inconvenience. It is considered that the error of `Sheets is not defined myFunction` is that Sheets API is not enabled at Advanced Google services. So, please confirm this again? If you cannot understand how to enable it, please check [this document](https://developers.google.com/apps-script/guides/services/advanced#enable_advanced_services). By the way, in order to check your current situation, can you share the sample Spreadsheet to replicate your current issue? By this, I would like to check whether Sheets API is enabled. – Tanaike Nov 11 '21 at 02:13
  • I'd like to apologize, I was not aware that I also needed to enable the API under Services in the script editor. Thank you very much for your help, it is working now! – Ryan Nov 11 '21 at 02:16
  • One more question, if I wanted the data to start in A2 instead of A1, would that be an easy fix? Thanks again!!! – Ryan Nov 11 '21 at 02:20
  • @Ryan Yes. You can do it. In that case, please modify `requests` to `const requests = { requests: [{ pasteData: { html: true, data: table[0], coordinate: { sheetId: ss.getSheetByName(sheetName).getSheetId(), rowIndex: 1 } } }] };`. `rowIndex: 1` is added to the property of `coordinate`. By this, the data is put from "A2" of the sheet. – Tanaike Nov 11 '21 at 02:22
  • I'm hoping you find this, the website we were pulling data from recently did an update and now the script you had helped me with no longer works. Is there anything we can add that would expand the different options on the page automatically? Everything is now hidden and needs to be collapsed in order to be viewed. – Ryan Jan 24 '22 at 23:00
  • @Ryan Thank you for replying. From your replying, it is considered that the specification of the site was changed. In that case, the question is different. In this case, can you post it as a new question? By this, it will help users including me think of the solution. If you can cooperate to resolve your new question, I'm glad. Can you cooperate to do it? – Tanaike Jan 25 '22 at 00:12
  • Thank you, Tanaike. I have posted a new question – Ryan Jan 25 '22 at 01:01
  • @Ryan Thank you for your response. I found your new question. And I answered it. – Tanaike Jan 25 '22 at 01:08
0

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);
}
Mike Steelson
  • 14,650
  • 2
  • 5
  • 20