CODEWITHSUNDEEP
databasegoogle-sheets
Hayden Reynolds
Hayden Reynolds

Reputation: 15

How to auto repeat ImportHTML with multiple URLS in Google Sheets

I am working on using ImportHTML in a google sheet right now, and am using it to import data that is always 6 columns wide, but is dynamic in length: Data

The formula I am using is: =IMPORTHTML(A1,"table",1,"en_US")

In Column A, i want to be able to add as many URLS as I want, and for it to add all of the data to a large table 6 wide by however long. I know how to add arrays using =Vstack, but i dont know how to use a dynamic list of URLS instead of just one. URLS Text: https://ftc-events.firstinspires.org/2023/USIAMCM1/qualifications

Added bonus if its possible to remove the text Column Labels that is imported with each data set. text: Match # Red Blue Score

Upvotes: 1

Views: 64

Answers (2)

rockinfreakshow
rockinfreakshow

Reputation: 30250

You may try:

=reduce(tocol(,1),tocol(A:A,1),lambda(a,c,vstack(a,let(Σ,importhtml(c,"table",),filter(Σ,index(Σ,,1)<>"Match #")))))

enter image description here

Upvotes: 1

Kreeszh
Kreeszh

Reputation: 809

I don't think there's a way to dynamically feed an importhtml multiple URLs but if you can predict the number of URLs you think you would need to accommodate, you could use something like this (this is designed for up to 6, and it will also strip out the column labels assuming they all start with "Match"):

=QUERY(query({
iferror({IMPORTHTML(A1,"table",1,"en_US")},{"","","","","",""});
iferror({IMPORTHTML(A2,"table",1,"en_US")},{"","","","","",""});
iferror({IMPORTHTML(A3,"table",1,"en_US")},{"","","","","",""});
iferror({IMPORTHTML(A4,"table",1,"en_US")},{"","","","","",""});
iferror({IMPORTHTML(A5,"table",1,"en_US")},{"","","","","",""});
iferror({IMPORTHTML(A6,"table",1,"en_US")},{"","","","","",""})}),
"select * where Col1 is not null and not Col1 starts with 'Match'")

Upvotes: 0

Related Questions