Google-sheets – Is it possible to use the importHTML function with a concatenated URL

concatenategoogle sheetsimporthtml

What I'm trying to do is use a URL previously made with the CONCATENATE function in an IMPORTHTML function. When I try:

importHTML (H1, "table", 0)

Where H1 is the cell with the concatenated URL, I get a #VALUE! error. Not even referencing H1 from a different sheet will work.

Any ideas about how to get past this?

According to the usual syntax, the URL needs to be enclosed in double quotes. I'm trying to set things up so I only need to type in a few lines to automatically perform IMPORTHTML runs.

Best Answer

Seems to work for me:

WA47779 example

Your formula is in K4. Maybe the first part of what you are concatenating has to be a valid address itself?

Related Solutions

Google Sheets – ImportHTML #VALUE! Error for Specific URL

It seems wodindex.wikispaces.com attempts to store a cookie, and then do some fancy redirect tricks, for reasons I don't know. The functionality behind ImportHTML needs to work as a browser (or at least an HTTP client), and probably does not support cookies.

This is what I get from running wget http://wodindex.wikispaces.com:

--12:41:12--  http://wodindex.wikispaces.com/
           => `index.html'
Resolving wodindex.wikispaces.com... done.
Connecting to wodindex.wikispaces.com[75.126.104.177]:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://session.wikispaces.com/1/auth/auth?authToken=f7a1a3abdd9511c29392cf7000b27dd5 [fol
lowing]
--12:41:13--  https://session.wikispaces.com/1/auth/auth?authToken=f7a1a3abdd9511c29392cf7000b27dd5
           => `auth@authToken=f7a1a3abdd9511c29392cf7000b27dd5'
Resolving session.wikispaces.com... done.
Connecting to session.wikispaces.com[208.43.192.33]:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://wodindex.wikispaces.com/?responseToken=f7a1a3abdd9511c29392cf7000b27dd5 [following]

--12:41:14--  http://wodindex.wikispaces.com/?responseToken=f7a1a3abdd9511c29392cf7000b27dd5
           => `index.html@responseToken=f7a1a3abdd9511c29392cf7000b27dd5'
Connecting to wodindex.wikispaces.com[75.126.104.177]:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://wodindex.wikispaces.com/ [following]
http://wodindex.wikispaces.com/: Redirection cycle detected.

C:\Users\viramd>wget http://wodindex.wikispaces.com/
--12:42:28--  http://wodindex.wikispaces.com/
           => `index.html'
Resolving wodindex.wikispaces.com... done.
Connecting to wodindex.wikispaces.com[75.126.104.177]:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://session.wikispaces.com/1/auth/auth?authToken=2141639d8901c291dc288a940c9609e8 [fol
lowing]
--12:42:28--  https://session.wikispaces.com/1/auth/auth?authToken=2141639d8901c291dc288a940c9609e8
           => `auth@authToken=2141639d8901c291dc288a940c9609e8'
Resolving session.wikispaces.com... done.
Connecting to session.wikispaces.com[208.43.192.33]:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://wodindex.wikispaces.com/?responseToken=2141639d8901c291dc288a940c9609e8 [following]

--12:42:29--  http://wodindex.wikispaces.com/?responseToken=2141639d8901c291dc288a940c9609e8
           => `index.html@responseToken=2141639d8901c291dc288a940c9609e8'
Connecting to wodindex.wikispaces.com[75.126.104.177]:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://wodindex.wikispaces.com/ [following]
http://wodindex.wikispaces.com/: Redirection cycle detected.

As can be seen here, the request is redirected to session.wikispaces.com, which has some kind of redirect loop, that can never end.

One 'solution' to your problem is to save the contents of wodindex.wikispaces.com to another web server, and fetch it from there. But I'm not sure that would be entirely legal.

Google Sheets Query – How to Reference a Cell Containing a Date

Indeed, one cannot put things like "A6" inside the query string because then it would just a part of a string, not a cell reference. Instead, concatenate the value of the cell to the string:

=JOIN(", ", UNIQUE(QUERY(A2:F4, "select E where day(A) ="&day(A6))))

You don't need transpose, because join works equally well with one-dimensional arrays of either direction: vertical or horizontal.

That said, I find a solution using filter slightly more readable:

=join(", ", unique(filter(E$2:E$4, A$2:A$4 = A6)))

Best Answer

Related Solutions

Google Sheets – ImportHTML #VALUE! Error for Specific URL

Google Sheets Query – How to Reference a Cell Containing a Date

Related Topic