Just something that i found especially if you find that you have done everything correct with your syntax and links. When attempting to import multiple ranges or sheets, it could be throwing this error due to a lack of permissions granted. It is likely that you have link-sharing turned off for the source spreadsheets that you desire to import and query. In ...
From the question
Is it possible to reference filtered data from one sheet to another in google sheets?
I am using the filter button to sort in a master sheet. I am then using this formula in a new sheet: =IMPORTRANGE("urlofthemastersheet", "sheetname!A1:A")
It's not possible by using IMPORTRANGE but you could import that data by using Google Apps ...
You can access a cell or a range from another google sheet (i mean a separate file) using the the IMPORTRANGE function. Here is an example:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Aviydfsh6PygtPjphKc5Fnvnm8g_Xoedf6utNqp2Ql4A", "MySheetName!E107") + SUM(E4:E99)
Here we provide the url to the other sheet in the IMPORTRANGE function, fetches ...
Use the Google Sheets array handling feature
Lets assume that A1 has the spreadsheet key required by IMPORTRANGE, that eh source spreadsheet has only the rows required to hold the data (no blank rows) and that the formula is set in A2
See the google-sheets-arrays wiki for references ...
You can record a macro which deletes all the values in the sheet then pastes the IMPORTRANGE formula back into the cell you want.
That way you have a macro set to a shortcut than refreshes it.
Not ideal but works
Looking into how to make the macro run every minute or with a button. Not sure how to do either.
Google Sheets doesn't have built-in functions that return a list of the sheet names and it's not possible to use something like !A1:D21 in formulas to refer to a range in the first sheet of the spreadsheet.
The alternative to automatically get a list of the sheet names is to use Google Apps Script. You could create a custom function to get the list of ...