0

Main Question

I'm trying to reduce my IMPORTHTML requests. The main issue is I'm forced to do the process twice.

=INDEX(IMPORTHTML(*Link*,"Table", 1),ROWS(IMPORTHTML(*Link*,"Table", 1)),2)

Is there any way to index the last row of the table without having to reinitialize the table again? The table size is random between 4-12. This wouldn't be an issue if it wasn't for the fact I'm using this 5 times (different cells from the imported table) across 282 tables. 1410 total cells like this. Over 2820 IMPORTHTML requests...

If I could remove the second request within the formula, finding the last row some other way, or re-reference the initial request, I could cut the requests in half. 1410 is still a lot, but it's faster than 2820.

Ideally, I'd like to reference the information of a full table within a single cell. Then I could reference the cell whenever I needed the table. Then I'd be down to 282 requests.

If I absolutely have to, I can remove 2 of the columns as the main usage of this chart only needs 3 (Current Team, Career Hits/Strikeout, Career Homeruns/Shutouts). This would reduce it to 1692 requests.

Specific Information

A Simplified version of the sheet I'm working on.

Here's what I'm trying to do on each page. I'm sure there are ways to combine some of these into a single step, but I didn't want my formula's to get so long leaving me unable to find errors. The original sheet has 20 teams.

  • TeamScraper: Pull the current players on each team and create 2 lists (Batters and Pitchers)
  • TeamTranspose: Rotate the list to be vertical.
  • PlayerStack: Take all the players and organize them into 2 lists(Batters and Pitchers). Create 2 more lists with the names reformatted for the LinkBuilder.
  • LinkBuilder: Turn each of the names into a link of the webpage with that player's stats table.
  • Batters & Pitchers: Pull needed stats from the table relevant to that player.

Don't mind Baby Triumphant. He's one of the few cases where the name he's given in the team list isn't the one used by the stats website. I'm working on an Exceptions page that the reformatting process will reference.

Edit: While writing this I saw someone asking about too many IMPORTHTMLs. The answer-er mentioned a limit of 50 instances of the request per sheet. I find this odd considering my 2820 requests split between 2 sheets EVENTUALLY works. It's just slow. Maybe they mean only 50 requests every few minutes?

Improve this question

1 Answer 1

Reset to default
0

See testSheet sheet "Batters"

QUERY() function would help a bit.

For example Team, Hits (Season), HomeRuns (Season) are extracted with formula

=QUERY(IMPORTHTML(LinkBuilder!$A2,"Table", 1),
"Select Col2, Col7, Col11
Order by Col1 Desc
Limit 1",0)

And Hits (Career) and HomeRuns (Career) with

=QUERY(IMPORTHTML(LinkBuilder!$A2,"Table", 1),
"Select Col7, Col11
Where Col2 is NULL",0).

That would decrease number of IMPORTHTML requests in case all tables have same structure.

Improve this answer
1
  • I suspected it may have been Query that would have solved my problem, but wasn't able to wrap my head around any of it's functions. Most of what I've been using I was already familiar with. This would bring it down to roughly 564 in that section which is a fifth of what I've started with. Thank you kindly.
    – Foreman
    Oct 22, 2020 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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