Google-sheets – importing from multiple workbooks for Google Sheets to ignore workbooks that have nothing listed, instead of returning an error

formulasgoogle sheetsimportrange

={FILTER(IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "Trade and Manufacture!P17:T"), IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "Trade and Manufacture!P17:P")<>"");FILTER(IMPORTRANGE("1tWboZO37-_V5nJTNNxRhlvCIONLwHU43iie67zg4IU4", "Trade and Manufacture!P17:T"), IMPORTRANGE("1tWboZO37-_V5nJTNNxRhlvCIONLwHU43iie67zg4IU4", "Trade and Manufacture!P17:P")<>"")}

I am using Google sheets as a management system for a game I play in my classroom with my students. I was able to make a dynamic trading table – as in the students can post a trade good for sale in their workbook, and then it populates to this range (in a separate book) and then is sent to every student's workbook.

This line is just for me testing if it worked across two workbooks, and it will be expanded to more of them – so this function will get larger. The problem I am having is that during testing with items listed – formula works great. The moment either of the two testing workbooks has NOTHING in the trade offers – it breaks. "Array Literal was missing values in one or more rows" I want it to ignore that and just post what is has available, and not break if even one country is not selling something.

Caveats
This code will be expanded to pull from more workbooks, so please note what has to be repeated vs what just applies to the single line of code.

Yes I know I should probably learn a coding language to make this work better (if you saw the rest of this workbook….. ) but I am a teacher and spare learning time is at a premium right now.

I do have an active script to make a dynamic drop down in another part of the workbook, so if a new script is needed I am not opposed to it – I just won't be able to do it from scratch.

Best Answer

You import data from external Google spreadsheets using IMPORTDATAand use FILTER to combine the results. However you get an #VALUE! error message ("In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.") if/when one or more sheet(s) contains no data.

IFNA works well when there is a single set of data, however it fails when there are multiple sets of data. This is because it returns one entry for an array reference - that is, it doesn't populate as many columns as exist in the original data.

An effective solution is to use QUERY. This has several advantages for the OP:

  1. it is easier to populate with additional sheets; simply add a "semi-colon" (;) and paste a new IMPORTRANGE value.
  2. the validation of Column P is done once only and applies for all data sets.
  3. if additional conditions are added, there are well-defined rules for how they are applied.

Formula

=query({IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "Trade and Manufacture!P17:T");IMPORTRANGE("1tWboZO37-_V5nJTNNxRhlvCIONLwHU43iie67zg4IU4", "Trade and Manufacture!P17:T")},"select Col1, Col2, Col3, Col4, Col5 where Col1 is not null")

Note: spreadsheet has been tested, but the IDs in the formula are the OP's, and the formula is untested for those spreadsheets.

Related Topic