Google-sheets – How to reference a cell for the sheet name in IMPORTRANGE function in Google Sheets when the sheet is in another workbook

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.

Spreadsheet functions cannot access the content of other spreadsheets, other than by invoking importrange.

But you can have importrange inside other functions. For example,

=join(",", importrange("...", "Sheet1!A1:A5"))

returns the content of cells A1:A5 in the other spreadsheet, comma-separated within one cell. Another command often nested is query, which allows you to do a lot of processing of another spreadsheet's data without getting intermediate results in. For example:

=query(importrange("...",  "Sheet1!A1:B9"), "select Col1, sum(Col2) group by Col1 order by Col1") 

returns the totals of the second column split by the values of the first column, and sorts by the first column.