I'm trying to summarize a bunch of sheets into one via a query([(importrange)(importrange)(etc.)}. I've done this multiple times without issue, but now the data I'm pulling is has a columns that is populated with an arrayformula(textjoin....
Is the array formula the cause of the importrange (or query) not working? My query is simply "select * where Col1 is not null".
=ArrayFormula(TEXTJOIN("; ",true,if('All days'!$D$1:$D$400 = D2,'All days'!$L$1:$L$400,"")))
-
Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely.– doubleunaryCommented Jan 20, 2023 at 21:56
Add a comment
|
2 Answers
The error message "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows."
The error message suggests that this is an importrange() authorization issue. In order to grant the necessary permission to importrange(), you need to unwrap it so that the import gets initiated from outside the query(), like this:
=importrange("...put spreadsheet id here...", "A1")
If the new formula cell displays #REF! and a red corner flag, click it to authorize the import. Repeat that with all the imports. After that, your existing formulas that use importrange() should start working as well. You can delete the new formula cells after that. See Query+Importrange for multiple sheets.
query([(importrange)(importrange)(etc.)}.
Another possible issue is that the query() function will only accept one data type per column. You are merging several imports in an array expression, and the columns need to align not only by name but also by content.
The textjoin() function produces text strings, so if one of the files you are merging has numbers in that column, or the occasional date or Boolean, the majority type will rule, and other types will be returned as nulls. If your data is very sparse, the column type may even end up as being null.
answered Jan 20, 2023 at 21:56
-
The = arrayformula was meant to be on a separate line to show the context of the source. The query in the target sheet is structured correctly based on past successful uses. The arrayformula/textjoin is the variable from when I've done this before. The error message "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows." There's empty fields in the data as this is a sheet that will build throughout the year, and we're only 3 weeks in, but each row in already pre-populated with the week # 1-52 for the year. All the textjoin data is either truly text or empty cells.– MattCommented Jan 20, 2023 at 22:14
-
Edited the answer per your new problem description. Please add all relevant detail in the question rather than in comments. Commented Jan 21, 2023 at 1:42
Have you tried to do a simple IMPORTRANGE for each one of the sheets outside of the QUERY?? If you're missing values in a row, that may be an issue.
Try doing =IMPORTRANGE(URL1,"A1") =IMPORTRANGE(URL2,"A1") and so on, with each one of the imported sheets to check if you have already linked them, and try the QUERY again
