google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-queryimportrange

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


I working on making a report based on data from 19 other Google Sheets. I'm using QUERY but I'm kinda new to this and not sure if I'm doing it right or not.

I'm trying to use below but it seems somehow it's giving the above error. I can't find a workaround to it.

=QUERY({
 IMPORTRANGE("1TRKveEBEitHDkos3WX0pPI6WUVL1gHMzdIkeB6s-dJc", "Data!A1:DL");
 IMPORTRANGE("1FONS-hdcUXnLj4UMAsixLL1CVNfL_WdxMbs68ylsyaU", "Data!A1:DL");
 IMPORTRANGE("1pE4O-rO5Fg-AmjMGQlb_m2KbeMV1ZT4ylaE5qfT_aaQ", "Data!A1:DL");
 IMPORTRANGE("1fMyrxa3rxec_8CMOsl2qbLFqht8Z2_SjvShT-WJ-ld8", "Data!A1:DL");
 IMPORTRANGE("1SC8E_0Qg9zurGwl0NsisQZO1gJyimMLXvCxRaPrqjic", "Data!A1:DL");
 IMPORTRANGE("1rtRAf7T2lY_f_R95-L9B4Mn4sn2a9oVHLour-iJfNMM", "Data!A1:DL");
 IMPORTRANGE("1UhBnBRiqPWf444Eyk26hwTEg27ErNvCE2bviRdikLCI", "Data!A1:DL");
 IMPORTRANGE("1AVr4ZMOcTBCkUkI6AaO73B0N8AeiEWyHwhyt56iJYPo", "Data!A1:DL");
 IMPORTRANGE("1n4p51IPq7m4wgjJiMTHZCKDnoR5udxIwUGY1mgJ6kNo", "Data!A1:DL");
 IMPORTRANGE("1tomsqwtJE60j-AAmt5yWFmvHunQQYjVuQmPz0tAmx-s", "Data!A1:DL");
 IMPORTRANGE("1gsyd7m867UkX20Ueha4EqSc6Uc4pSzwc-fe-gYxey5c", "Data!A1:DL");
 IMPORTRANGE("1KjUVM8nkO0pfJrSed-laSzDAu8S-amPkg6cqSRYWQ2I", "Data!A1:DL");
 IMPORTRANGE("1m2MV6VY7sb3zBTuoEQZWJHTxo7moDKtYV-PYJTnES38", "Data!A1:DL");
 IMPORTRANGE("1p9dAD60KjpsOp69OBQazeg9ktzTWvtbjXLfzmMUHNLk", "Data!A1:DL");
 IMPORTRANGE("15V2rMfnbk5UEPeUa6MtaD8ljm-xbmXBM2WzZrUhDzVU", "Data!A1:DL");
 IMPORTRANGE("1DevNq8TbkDhVBkeHPegaHpxaNgvlGtPZExzueN8cpyk", "Data!A1:DL");
 IMPORTRANGE("1sXQABwo5NXiz166cruJM5Is4JWKVXzoYS3hh6IcXVj4", "Data!A1:DL");
 IMPORTRANGE("1sOBkqGVKl6xn89uRvN-TLlU1TFMJUxD_s8TgmowkLK8", "Data!A1:DL");
 IMPORTRANGE("1t8CdrQiJq1h15OIlF5yaRy1AxHyZ_mnEzfSUDEyPSM8", "Data!A1:DL")},
 "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")

Solution

  • ...understanding the ARRAY_LITERAL ERROR:


    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)