2


2

Use curly brackets within this very simple query: =QUERY({Alpha!A2:B;Beta!A2:B;Charlie!A2:B},"select * where Col1 <>'' order by Col2") The trick here is the curly brackets {....} we use to create and combine the arrays from the different tabs. Extra tip: {Alpha!A2:B;Beta!A2:B;Charlie!A2:B} A semicolon ; combines vertically (in columns, one under ...


1

You can use the entire url: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QDCf2MDkN7eopuTT-EieQI39Bpc_Y6D-3EWefimpk/edit#gid=0", "A:A") But just the spreadsheet id should also work: =IMPORTRANGE("1QDCf2MDkN7eopuTT-EieQI39Bpc_Y6D-3EWefimpk", "A:A") More generally the google sheets url is structured like this: https://docs.google.com/...


1

It sounds to me like you're trying to use a spreadsheet to do the job of a database. (2000 rows is nothing. But three concurrent users, 13 tabs, feeding three other worksheets .. put those together and you're not in spreadsheet-land any more). I'd suggest a review of your overall IT and business architecture, and make decisions from there about what ...


1

Assuming that B1 has a team name: =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#gid=xxxxxxxxxx","Game Week 20!A:E"),"Select Col1, Col2, Col3, Col4, Col5 Where Col3, Col5 matches '"&B1&"'") Please note the use of ' to enclose the team name and & to concatenate it to the SQL statement As the error message says, the ...


1

It's not possible by using IMPORTRANGE but you could achieve something similar by using Google Apps Script or and add-on. For details about extending Google Sheets please read https://developers.google.com/apps-script/guides/sheets Some hints The macro recorder and custom functions could not be used in this case Google Apps Script can't open a spreadsheet ...


1

Use IMPORTRANGE() to import data and VLOOKUP() to find match between Product Section. Here's a sample : First you need to import data from another sheet with IMPORTRANGE() You need to accept the access between sheets when you use IMPORTANGE: The best is to do this with a sample of every sheets to be sure you allow access for each one. To find match ...


1

For users that want to do the same thing, but swap out the sheet name (instead of the cell name) they can use the following formula. =IMPORTRANGE("KEY", A4&"!E2:E") ...where A4 contains the name of the specific sheet you want to reference in your other spreadsheet and E2:E is the range that you want to import from that other spreadsheet.


1

If you want to 'skip a column', you can use regexreplace on one column of your range; so it will have the same number of rows. You would not be able to edit the column afterward obviously, but you can utilize some code in the final input for regexreplace function to drop-in what you need. =arrayformula({index(_yourRange,,1),regexreplace(index(_yourRange,,1)...


1

As a workaround for the now() roadblock, you can change the settings of the spreadsheet to update every hour. File -> Spreadsheet Settings -> Calculation Update the sheet to recalculate itself on "On Change and every hour" or "On change and every minute". Though keep in mind, Selecting recalculation every minute may hang up the spreadsheet.


1

The problem is that Column M and Column Nfrom the source spreadsheet include text and numbers / dates. Blank cells doesn't cause problems. In both columns, you should remove the text values before passing the imported values to QUERY. While it could apparently be "elegant" to have a single formula that does all the job, this makes the spreadsheet harder ...


1

As it was mentioned on the other answer Google doesn't offer a place that include a list of the connected spreadsheets but you could create automatically that list by using Google Apps Script. The access rights that the connection implies is read only and it's granted to the account that authorized the access. This connection will work while the authorizer ...


Only top voted, non community-wiki answers of a minimum length are eligible