1

4/17 6PM Update *** I am now trying to use the URL Fetch App. I've been playing with this for hours, but can't seem to figure out the issue. I am just trying to get the info from this site as plain text (so the score doesn't mess up) in a Google Sheet.

function myFunction() {
    var response = UrlFetchApp.fetch("https://www.basketball-reference.com/boxscores/pbp/202003110MIA.html");
 Logger.log(response.getContentText());
   var doc = SpreadsheetApp.getActiveSpreadsheet();
   var cell = doc.getRange('a1');
}

I'm using ImportHTML to export NBA play by play data.

For some reason, random cells containing the score (formatted 12-10 as an example) transfer incorrectly as long numbers (43864 as an example).

I've tried various formatting options that are offered & I've looked at different games as well, but don't see any patterns as to why certain scores work and others don't.

Lastly, when I copy and paste the info directly from the site it works flawlessly, but when you paste as values the score becomes incorrect -- I'm assuming that is what ImportHTML is doing.

Could someone possibly explain to me why this is happening? Is there a way to make sure all scores stay correctly formatted?

For reference, I'm building a script that will take all of this information, and transform it so that I can track different events for players based off of time. This has been my one little hiccup I can't seem to get past.

I've attached screenshots of the error in Sheets, along with the information as it is on the website.enter image description here

enter image description here

4
  • When 12-10 becomes 43864 it means Google-sheet interpret it as a date. You can try to force a format with =text() – pjmg Apr 17 '20 at 11:13
  • The "update" should be posted as a new question, not as an edit b/c it's about Google Apps Script not about IMPORTHTML. – Rubén Apr 17 '20 at 22:23
  • Sorry @Ruben - I recently got banned from asking questions on Overflow unfortunately. Bit of a learning curve with the site, so I was trying to be careful with asking questions. My apologies – Anthony Madle Apr 18 '20 at 2:12
  • To lift the post ban try improving your questions. Having upvotes on them will help a lot. It might also help that you accept the good answers. – Rubén Apr 18 '20 at 2:30
0

Tl;Dr IMPORTHTML and IMPORTXML assigns the data type before adding the values to the spreadsheet. Numbers like 43863 are serial numbers used to represent dates, time and durations.

Google Sheets

  • automatically try to detect the data type of the value entere on each cell based on the spreadsheet regional setting.
  • uses serial numbers to handle dates and durations
  • IMPORTHTML is a function that is calculated on the server instead of being calculated on the client (web browser)

You could try to build a complex formula (see Trying to use Google Sheets importHTML() to import a table. It forces content to a date format) but as you already are building a script it's very likely that using the script to scrap the web content will be better as you will have more control about how the content is parsed.

2
  • Great - thanks @Ruben. I will try basing my own complex ImportHTML function off of that link. I had originally tried to use a script to scrape the info, but I couldn't figure it out. I am pretty new to this, I recorded a macro for my other work. I tried using the fetch URL info from this link developers.google.com/apps-script/reference/url-fetch/… but still struggled. Is fetch URL the action you would use if making a script? Is there another method I should try approaching or is that the key? – Anthony Madle Apr 17 '20 at 15:28
  • Yes, you could use the URL Fetch service. On Stack Overflow there are several questions about web scraping by using it. – Rubén Apr 17 '20 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.