Google-sheets – How to merge multiple spreadsheet content having the list of keys to them

google sheets

Input: column of Google keys for spreadsheet that contains tables with some data, eg:

date | refNum | Name | Description

Require: a sheet that will contain a merged table with data from spreadsheets referenced by keys.

Actual Results:

for two cells that contains two spreadsheet keys

C21:C22

I tried the next formula:

=ARRAYFORMULA( {importrange(C21, "A13:C23" ) ; importrange(C22, "A13:C23" )})

and it is working well, but what I need, is something more autamatic, to not be in situation to modify every time the formula when I add a new spreadsheet key.

Does anybody know HOW TO get it?

Best Answer

One alternative is to use CONCATENATE or the ampersand operator to create the formula for you, but the result will be a text. Then copy, paste as value and edit the result to have a working formula.

You could automate this with Google Apps Script.

References
CONCATENATE - Docs editors Help
Extend Google Docs, Sheets, and Forms with Apps Script - Docs editors Help

Related Topic