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

={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.

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.

Go to Source
Author: Matthew Baptiste-Cerra