Thanks in advance for your insights!

I have two Google Sheets: one for each of my teams (in separate worlds, obviously). In each, a core function to create the data I need is, where cell B1 is a four digit code associated with a team:

=importhtml("https://www.whatifsports.com/hd/TeamProfile/Ratings.aspx?tid="&B1,"table",1)

This has worked for years. For one file, it continues to work fine. For another, it suddenly throws a "Could not fetch URL" error. I've checked, and the below URL remains correct:

https://www.whatifsports.com/hd/TeamProfile/Ratings.aspx?tid=4080

Nothing online I've found explains the error. The website is correct, my other sheet's formula continues to work, so I don't think the website is blocking Google scraping or anything like that. I've tried switching between "http" and "https", still no luck.

My entire system is broken if I can't get this import working and I'm at a loss. Any ideas for a weary sports nerd?

I'll make sure to update this post when it's solved. Again, thanks for any help you might offer.

You aren't the only one having issues with this. Seems like a WIS server/firewall issue. Perhaps one or more google IPs have been either banned or severely rate limited. The only thing that doesn't point to this is Gil potentially having issues with his scraper. That wouldn't have any relation to Google, but he hasn't confirmed yet on his end what problems he might be having.

Posted by Basketts on 3/26/2022 2:26:00 PM (view original):
You aren't the only one having issues with this. Seems like a WIS server/firewall issue. Perhaps one or more google IPs have been either banned or severely rate limited. The only thing that doesn't point to this is Gil potentially having issues with his scraper. That wouldn't have any relation to Google, but he hasn't confirmed yet on his end what problems he might be having.

fffffuuuuuuuuu

This would be terrible for me.

But why would a sheet pulling from one world work and not another?

Looks like it's working now. Maybe some temporary site-side thing on that server.

It's a temporary issue with Google Sheets. We are all having it. Not clear what is happening.

Posted by kaw_86 on 3/26/2022 3:13:00 PM (view original):

Posted by Basketts on 3/26/2022 2:26:00 PM (view original):
You aren't the only one having issues with this. Seems like a WIS server/firewall issue. Perhaps one or more google IPs have been either banned or severely rate limited. The only thing that doesn't point to this is Gil potentially having issues with his scraper. That wouldn't have any relation to Google, but he hasn't confirmed yet on his end what problems he might be having.

fffffuuuuuuuuu

This would be terrible for me.

But why would a sheet pulling from one world work and not another?

Google has a pool of IPs for their services. One sheet might be tied to one while another isn't. This is all speculation without any comment from WIS.

Any word on this. I am going to have to stop paying for the game if they can't get this issue fixed. It usually works at some point during the day, but I have set up my teams on a schedule, so if it is not working at that point (when I have time to set up for the day), it doesn't matter if it comes back in an hour.

Did anyone submit a ticket for this issue? I don't mind doing so if no one else has.

i sent them a ticket, they didn't know anything about it. i don't have any specifics about the google sheet, though, and it seems like the google sheet fails more than chrome or the java program i use? i think we need to get some better info on what is happening and send it to them, or maybe come up with straight forward instructions to reproduce it, if that's possible? like maybe if the sheet now pulls in 1 team it should pull in 1 from each world, if that makes it fail... i don't know. but i think we need to get them more.

its hard to debug 'sometimes this user-created tool just errors out'. i meant to take some time to open the sheet and play with it, and with the java program, to see if i could get better errors or something. i haven't had a chance yet though, was out of town the last week, but i'll probably take a look this weekend.

Posted by gillispie on 4/8/2022 11:39:00 AM (view original):
i sent them a ticket, they didn't know anything about it. i don't have any specifics about the google sheet, though, and it seems like the google sheet fails more than chrome or the java program i use? i think we need to get some better info on what is happening and send it to them, or maybe come up with straight forward instructions to reproduce it, if that's possible? like maybe if the sheet now pulls in 1 team it should pull in 1 from each world, if that makes it fail... i don't know. but i think we need to get them more.

its hard to debug 'sometimes this user-created tool just errors out'. i meant to take some time to open the sheet and play with it, and with the java program, to see if i could get better errors or something. i haven't had a chance yet though, was out of town the last week, but i'll probably take a look this weekend.

So .. If I can find a sheet that somehow works .. I can copy it and use it for other teams as well, changing both my team and the opponent.

I am really at a loss on why it is happening. I do know I can sometimes duplicate the issue with an excel / openoffice local spreadsheet as well as google sheets sheet. So it seems the issue is at the remote location (ie, the connection to OR the timing of the WIS server).

I have no idea what google sheets uses as it's identification when it makes a get query against an http server, or how balancing proxy server software (if WIS is using any) might impact this.

In the old days, you had one physical server behind a router and things ran on it. Nowadays, there is load balancing, proxy servers, redirects based on geography and/or load (and maybe even QOS) and many other possible complications. Basically, to troubleshoot his, you need to know a bunch about network design and who their service provider is .. AND in the case of google sheets, the infrastructure on the cloud where the sheets are actually located what path the network packets take, maybe even the TCPIP MTU size of the packets, etc. The possibilities are almost endless :)

Each physical connection / router along the way could have a packet size misconfiguration that breaks up the packet in a way that it causes issues .. or a proxy server might filter items differently between a web browser or a spreadsheet / scraping program .. or something may not handle redirects properly.

Just as a point of reference, I am currently a Linux software engineer, but I have had titles such as Oracle DBA, UNIX Systems Administrator, Network Engineer, etc since the early 1980's :).

Unfortunately, Google Sheets doesn't give you much in the form of a debug log for the importhtml function. It just occasionally fails similar to your java program.

Posted by Basketts on 4/8/2022 12:08:00 PM (view original):
Unfortunately, Google Sheets doesn't give you much in the form of a debug log for the importhtml function. It just occasionally fails similar to your java program.

Right, and the thing is, I never have any problem loading the web pages in my web browser. So, whatever the problem is, Chrome and Edge (or Firefox) can get content back just fine from WIS.

So, the problem is almost certainly with the limitations of the client (ie, the program or sheet making the request). Sheets and scraping programs are not nearly as robust and web browsers in handing things like redirects from proxy servers to back end machines .. OR packet size changes, etc.

Also, programs and sheets can identify themselves as NOT BEING web browsers and filtering software sometimes treat them differently.

I was thinking that but WIS and specifically Hoops Dynasty does not have a robot.txt at the server level to block or limit scrapers. And it's odd to me that multiple methods of calls are all experiencing timeouts. That seems more like a firewall issue on their end which you hinted at.

Posted by hughesjr on 4/8/2022 12:20:00 PM (view original):

Posted by Basketts on 4/8/2022 12:08:00 PM (view original):
Unfortunately, Google Sheets doesn't give you much in the form of a debug log for the importhtml function. It just occasionally fails similar to your java program.

Right, and the thing is, I never have any problem loading the web pages in my web browser. So, whatever the problem is, Chrome and Edge (or Firefox) can get content back just fine from WIS.

So, the problem is almost certainly with the limitations of the client (ie, the program or sheet making the request). Sheets and scraping programs are not nearly as robust and web browsers in handing things like redirects from proxy servers to back end machines .. OR packet size changes, etc.

Also, programs and sheets can identify themselves as NOT BEING web browsers and filtering software sometimes treat them differently.

i occasionally have issues via browser. one thing that i used to get a lot, but only saw once in the past year or two - and my browser tabs were like, unreasonable even by my standards, i think i had 300 or something - so it mighta been on my end purely. but what i saw was, the entire color scheme of my team was radically different. all the data was there, but the colors like for the banner at the top and how those colors are woven into the conference standings and all that stuff, it was all different. this used to happen fairly often, like most folks had seen it once or twice at least, i'm guessing. we figured something didn't finish making it over, style sheets related or whatever.

one i have seen a few times recently though, and when i didn't have an overloaded chrome/PC - i will pull up the team popup page, and it will look like it finished loading, but it doesn't. all the data will be there, but i won't be able to sort any of the columns, and then i'll realize the spinner is still spinning. sometimes it finishes, sometimes it doesn't. a simple reload generally resolves it promptly, even clicking on another tab i think? so i sort of question if this happens and folks just don't realize it. (all of these things, i kinda wonder, is the WIS side not flushing and waiting for the flush to complete before closing the connection, something along those lines... which just normal differences in clients and internet connections, time of day / load too, could cause to manifest much more or less in various circumstances)

but i do agree with you. i think we need to dig into the client side, more. see what the spread sheet is struggling with. i run that java dynasty rankings program out of the cloud, but its a crappy little VM, it could be the problem. but at least it means its not just my PC. i figured if i could reproduce the issue there, i'd run the same program on my PC, and perhaps a dev machine at work too, to get a nice diversity of client setups and client networks involved.

i just haven't spent any time, been on vacation... but i will circle back and spend some time on this soon. i was hoping the dynasty rankings stuff would magically fix itself after some time, but it hasn't, so i really have been meaning to address the issues there, at least to catch them and re-download the pages to get clean results. but this part about your excel/chrome sheets having errors, got my interest, because i wonder if its all related? however, you guys seem to experience a frequency of failures... that i do not. so perhaps its completely unrelated. or perhaps the sheet just deals with the problem worse, so it manifests there more. i feel like it could be either way - but with the sheet being the place it seems to fail most regularly - i'm curious to see what is going on there.

I've seen the issues you describe too, Gil. I also get similar color issues on the NT tournament pages where none of the school colors load for each team name.