Google Sheets – Use Cell Value to Define Sheet in IMPORTRANGE

I've been struggling with the same problem. Instead of writing a custom function, I add a different query string to spreadsheet_url in IMPORTRANGE in the hope that each time the page is refreshed, Google thinks it needs to fetch data from a new spreadsheet. I simply append a new timestamp to make the url unique each time. It's a blatant hack, but it has been working for me across a lot of sheets.

My formula previously looked something like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174","'Sheet1'!A1:B25")

And now it looks like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174"&"?"&now(),"'Sheet1'!A1:B25")

Update:

This method no longer works since Google no longer permits now() inside importrange(). See comment from Hugh below.

In the expression

 '=IMPORTRANGE(cellA2value, "Sheet1!b2:b20")'

cellA2value is just a string, a bunch of characters with no particular meaning: letter c followed by letter e, and so on. What you wanted is

 '=IMPORTRANGE(' + cellA2value + ', "Sheet1!b2:b20")'

which is the concatenation of three strings, the second of them being cellA2value.

By the way, importrange has to be approved by the user the first time it imports a spreadsheet (via the dialog "connect these spreadsheets"). So you'll need to make sure that each of the possible sources is approved in this way.