ARRAY_LITERAL, an Array Literal was missing values for one or more rows

by

…understanding the ARRAY_LITERAL ERROR:

  • until both queries/filters/formulas output something then all is good:

    0

  • however if one of those queries/filters/formulas doesn’t have anything to output it outputs #N/A – No matches are found in QUERY/FILTER evaluation. – the issue is that #N/A is only in the 1st cell:

    e

  • but array expects that matrix on both sides to be same (4 columns from both queries/filters/formulas):

    0

  • so we wrap each query into IFERROR and in case of error we output fake row with 4 fake columns – {"","","",""} – which will trick the array to output it like:

    0

your Array_Literal error is caused because one (or more than one) IMPORTRANGE formula outputs #N/A

to counter this use:

=ARRAYFORMULA(IFERROR(QUERY({
 IFERROR(IMPORTRANGE("1TRKveEBEitHDkos3WX0pPI6WUVL1gHMzdIkeB6s-dJc", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1FONS-hdcUXnLj4UMAsixLL1CVNfL_WdxMbs68ylsyaU", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1pE4O-rO5Fg-AmjMGQlb_m2KbeMV1ZT4ylaE5qfT_aaQ", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1fMyrxa3rxec_8CMOsl2qbLFqht8Z2_SjvShT-WJ-ld8", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1SC8E_0Qg9zurGwl0NsisQZO1gJyimMLXvCxRaPrqjic", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1rtRAf7T2lY_f_R95-L9B4Mn4sn2a9oVHLour-iJfNMM", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1UhBnBRiqPWf444Eyk26hwTEg27ErNvCE2bviRdikLCI", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1AVr4ZMOcTBCkUkI6AaO73B0N8AeiEWyHwhyt56iJYPo", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1n4p51IPq7m4wgjJiMTHZCKDnoR5udxIwUGY1mgJ6kNo", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1tomsqwtJE60j-AAmt5yWFmvHunQQYjVuQmPz0tAmx-s", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1gsyd7m867UkX20Ueha4EqSc6Uc4pSzwc-fe-gYxey5c", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1KjUVM8nkO0pfJrSed-laSzDAu8S-amPkg6cqSRYWQ2I", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1m2MV6VY7sb3zBTuoEQZWJHTxo7moDKtYV-PYJTnES38", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1p9dAD60KjpsOp69OBQazeg9ktzTWvtbjXLfzmMUHNLk", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("15V2rMfnbk5UEPeUa6MtaD8ljm-xbmXBM2WzZrUhDzVU", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1DevNq8TbkDhVBkeHPegaHpxaNgvlGtPZExzueN8cpyk", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1sXQABwo5NXiz166cruJM5Is4JWKVXzoYS3hh6IcXVj4", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1sOBkqGVKl6xn89uRvN-TLlU1TFMJUxD_s8TgmowkLK8", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ));
 IFERROR(IMPORTRANGE("1t8CdrQiJq1h15OIlF5yaRy1AxHyZ_mnEzfSUDEyPSM8", "Data!A1:DL"), SUBSTITUTE(SPLIT(REPT(12, 116), 1), 2, ))},
 "SELECT Col85,Col86,Col87,Col88,Col89,Col90,Col91,Col92,Col93,Col94,Col95,Col96,Col97,Col98,Col99,Col100,Col101,Col102,Col103,Col104,Col105,Col106,Col107,Col108,Col109,Col110,Col111,Col112,Col113,Col114,Col115 
  WHERE Col85 IS NOT NULL", ))

also make sure you connected all importranges by allowing access (this needs to be done one by one)

More Related Contents:

  1. ArrayFormula of Average on Infinite Truly Dynamic Range in Google Sheets
  2. ArrayFormula and “AND” Formula in Google Sheets
  3. How to use arrayformula with formulas that do not seem to support arrayformulas?
  4. In ARRAY_LITERAL, an Array Literal was missing values for one or more rows – indirect formula error in google spreadsheet
  5. How to create INDIRECT array string of multiple sheet references in Google Sheets?
  6. Conditional formatting based on another cell’s value
  7. Google Sheets importXML Returns Empty Value
  8. Get the last non-empty cell in a column in Google Sheets
  9. How to stop / freeze / pause volatile RAND / RANDBETWEEN / RANDARRAY?
  10. How would I import YouTube Likes and Dislikes and a ratio from YouTube onto Google Sheets? [closed]
  11. How to SUM duration in Google Sheets?
  12. Is there a custom function or script that returns gid of a specific sheet in Google Sheets?
  13. Selecting the last value of a column
  14. Difference between two dates expressed as years, months, days (in one column)
  15. Query is ignoring string (non numeric) value
  16. Repeat whole row N times based on column value in Google Sheets
  17. I get Formula parse error with all formulas
  18. IMPORTXML and right XPath from Bloomberg price [duplicate]
  19. What factors determine the memory used in lambda functions?
  20. How to list all permutations without repetition?
  21. Repeat each row N times in Google Sheets
  22. Google Sheets Formula for Extracting Domain From Website?
  23. How to create all possible pair combinations without duplicates in Google Sheets?
  24. Col BY causing parse error in Google sheets query
  25. Count cells that contain any text
  26. How to make a range repeat n-times in Google SpreadSheet
  27. ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?
  28. Using built-in spreadsheet functions in a script
  29. Google Sheets QUERY Function: Select Columns by Name
  30. How to maintain absolute cell address even when deleting ranges?

Leave a Comment