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.
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?