May be an old question but its still being looked up - so here is the workaround:
Importrange as we all know will only import raw un formatted data into a new googlesheet. As a workaround to avoid the hassle of redoing all the conditional formatting rules on the new import data sheet, do the following: - on the original sheet which you plan to import data from- right click the sheet tab n click 'copy to'
when the browse file window pops up select the sheet you plan to use the importrange formula on.
once you get the successful copy message - open that sheet - press ctl A and press the delete key
then in cell A1 type your importrange formula - your data will now display with all conditional formatting rules that have been set in the original sheet which you are importing from.
check out my post on "importrange while preserving conditional formatting rules" https://productforums.google.com/forum/#!topic/docs/-KnnBPq4d24
Answer from cryptonic on Stack Exchangegoogle sheets - IMPORTRANGE with preserved formatting - Web Applications Stack Exchange
import - Google Sheets IMPORTRANGE with cell variables - Stack Overflow
Importrange function in Excel online
google sheets - Combining multiple spreadsheets in one using IMPORTRANGE - Stack Overflow
Videos
I have access to my company's old database on sheets using importrange where the techguy used it to make it so that only using the employee number, we were able to automatically see all the other details on other sheets
I fired the tech guy about a week ago, and since then I have been locked out of the account that was used to make the original masterdatabase sheet with all the employee details.
I have reported the same to the nearest police station as this was a clear violation of ethics and law by exploiting my credentials to deprive me and the tech dept of the database.
But now I have no clue how to access the master database, and without it a lot of things have come to a halt, I would really appreciate someone helping me out with how to reconstruct my master database using the importrange command, because I checked and the thing my previous guy set up still works, we are able to insert entire detail rows only using employee numbers, so I figured that he probably forgot to revoke access on the sheet.
May be an old question but its still being looked up - so here is the workaround:
Importrange as we all know will only import raw un formatted data into a new googlesheet. As a workaround to avoid the hassle of redoing all the conditional formatting rules on the new import data sheet, do the following: - on the original sheet which you plan to import data from- right click the sheet tab n click 'copy to'
when the browse file window pops up select the sheet you plan to use the importrange formula on.
once you get the successful copy message - open that sheet - press ctl A and press the delete key
then in cell A1 type your importrange formula - your data will now display with all conditional formatting rules that have been set in the original sheet which you are importing from.
check out my post on "importrange while preserving conditional formatting rules" https://productforums.google.com/forum/#!topic/docs/-KnnBPq4d24
Creating a template with formatting and then applying the import range seems to be working
You should be able to use a vertical array in the Spreadsheet 3:
={IMPORTRANGE("Sheet1Key","SheetName!A2:A500");IMPORTRANGE("Sheet2Key","SheetName!A2:A500")}
Of course, it is also possible to combine several IMPORTRANGE() functions with the QUERY() function, which gives us a greater control over the results we import.
For example, we can use such a construction:
=QUERY(
{
IMPORTRANGE("key-or-url-of-spreadsheet-1", "'sheet-name-1'!A2:Z100");
IMPORTRANGE("key-or-url-of-spreadsheet-2", "'sheet-name-2'!A2:Z100");
IMPORTRANGE("key-or-url-of-spreadsheet-3", "'sheet-name-3'!A2:Z100");
IMPORTRANGE("key-or-url-of-spreadsheet-4", "'sheet-name-4'!A2:Z100")
},
"SELECT * WHERE Col1 IS NOT NULL ORDER BY Col3 ASC"
)
###Explanation:
The above query removes blank lines from imported ranges:
SELECT * WHERE Col1 IS NOT NULL
and sorts ascending all data collected together in relation to the third column:
ORDER BY Col3 ASC
For descending, just use DESC in place of ASC.
Of course, we can also arrange any other criteria, or omit them displaying everything without modification:
"SELECT * "
###Note:
In order to use the above constructed query, we first need to call a single IMPORTRANGE() method for each of the spreadsheets we want to refer:
=IMPORTRANGE("key-or-url-of-spreadsheet-1", "'sheet-name-1'!A2:Z100")
We have to do this even if we refer to the same spreadsheet in which we write this formula, but for every spreadsheet it is enough to do it once.
This is to be able to connect these sheets and allow access to the sheets (to which we have the access rights anyway):
After giving permission for all spreadsheets, we can use the above query.