2

I need to consolidate multiple sheets in one file, no blanks or space. But always got this error:

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

=QUERY({
IMPORTRANGE("LISTS!B2","Filename!A3:T");
IMPORTRANGE("LISTS!B3","Filename!A3:T");
IMPORTRANGE("LISTS!B4","Filename!A3:T");
IMPORTRANGE("LISTS!B5","Filename!A3:T");
IMPORTRANGE("LISTS!B6","Filename!A3:T");
IMPORTRANGE("LISTS!B7","Filename!A3:T");
IMPORTRANGE("LISTS!B8","Filename!A3:T");
IMPORTRANGE("LISTS!B9","Filename!A3:T");
IMPORTRANGE("LISTS!B10","Filename!A3:T")
}, "SELECT * WHERE Col1<>;''")

I should get all the information from the sheets I have mentioned with no blanks but the error is persistent that

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

I don't know what's missing since all the sheets listed are good as per checking. What should I check in order to display all the values, rows?

player0
  • 124,011
  • 12
  • 67
  • 124
Yucute
  • 21
  • 1
  • 1
  • 3

2 Answers2

4

use this logic which counters Array_Literal error:

=ARRAYFORMULA(IFERROR(QUERY({
 IFERROR(IMPORTRANGE("ID 1", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 2", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 3", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 4", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 5", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 6", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 7", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 8", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 9", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ))}, 
 "where Col1 is not null")))

ARRAY_LITERAL error explained

UPDATE:

if you want to reference ID from another cell, those cell references cannot be quoted. eg it should look like this:

0

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • I'm still a newbie in Gsheet functions. Tried to turn on the iterative calculation, not sure on the max number of iterations as well as threshold. tried to input 10 but no values/rows being displayed. – Yucute Sep 27 '19 at 15:22
  • can you share a copy of your sheet? – player0 Sep 27 '19 at 15:24
  • https://docs.google.com/spreadsheets/d/1MxcSrygpKA-4DHpaUjYC_XIWYhyEWYjuQ8sxsm1s1-w/edit#gid=947004540 – Yucute Sep 27 '19 at 15:54
  • yeah, I understood... did you check the updated answer? – player0 Sep 27 '19 at 16:56
  • oops, sorry, already removed the double quotes to cell reference.. but still no display, values and error in ARRAY_LITERAL: an Array Literal was missing values for one or more rows. – Yucute Sep 27 '19 at 16:58
  • you may check again the shared sheet. still error. i don't know but inner functions works separately. I have just need to conso all the cell references. – Yucute Sep 27 '19 at 17:04
  • the issue is in those sheet names. check them out. see: https://i.stack.imgur.com/3XCqo.png - https://docs.google.com/spreadsheets/d/1xN6kc6atk8qn8vtfQVYN3arzDazvJtDl-T5waZ1Am5Y/edit#gid=947004540 - I removed them and managed to get data from one of those 3 spreadsheets. you need to include proper sheet names and every IMPORTRANGE formula needs to be run solo first to be authorized and only then this query formula will work as you want it – player0 Sep 27 '19 at 17:17
  • Tried it, but results is #N/A...=ARRAYFORMULA(IFERROR(QUERY({ IFERROR(IMPORTRANGE(LISTS!B2, "A2:T"), SUBSTITUTE(COLUMN:A1:T1)^0, 1, ); IFERROR(IMPORTRANGE(LISTS!B3, "A2:T"), SUBSTITUTE(COLUMN:A1:T1)^0, 1, ); IFERROR(IMPORTRANGE(LISTS!B4, "A2:T"), SUBSTITUTE(COLUMN:A1:T1)^0, 1, )}, "WHERE Col1 IS NOT NULL",0))) – Yucute Sep 28 '19 at 01:25
  • as already mentioned several times you need to include exact sheet name of each imported spreadsheet – player0 Sep 28 '19 at 09:00
  • also you have a typo there... `COLUMN:A1:T1)` should be: `COLUMN(A1:T1)` – player0 Sep 28 '19 at 09:03
  • it works after correcting the typo but only the first sheet is being displayed. :( I need to consolidate more than 20 sheets and still unable to find correct function. please advise. – Yucute Sep 28 '19 at 13:08
  • add appropriate sheet names – player0 Sep 28 '19 at 13:14
  • i don't know but they are all the same sheets. – Yucute Sep 29 '19 at 00:55
  • can you share all your sheets? – player0 Sep 29 '19 at 01:03
  • Here are the sheets: B2: https://docs.google.com/spreadsheets/d/1GoF3CAwcqD2Xz_SMMeSNgUG26qJgYcobVeMkSEjB8Fw/edit#gid=0 B3:https://docs.google.com/spreadsheets/d/1U-611kGcCO20c9JIDVZUR9CexdgRWUQUMDjbkYvu5lc/edit#gid=0 B3: https://docs.google.com/spreadsheets/d/1MeqgYIpZNBPF1fRHqZkHkFlnuwqmzzIZL24hOVqhT-M/edit#gid=0 – Yucute Sep 29 '19 at 02:25
  • 2nd and 3rd sheets are private – player0 Sep 29 '19 at 12:07
  • 1
    hi. already got the right process! now works fine :) thankyou. – Yucute Sep 30 '19 at 06:10
0

Test all inner functions separately:

=IMPORTRANGE("LISTS!B2","Filename!A3:T")

=IMPORTRANGE("LISTS!B3","Filename!A3:T")

and so on.

One or more of them gives an error.

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • tried it and it works fine: =IMPORTRANGE(LISTS!B2,"Filename!A3:T") ... why it is not working with multiple sheets? I don't know what function should I use in order to consolidate multiple sheets.. – Yucute Sep 27 '19 at 15:04
  • When you use import range in a cell, it prompts you to allow access for that sheet. The problem arises when you use importrange from multiple sheets, in this case you don't get prompted for the allow access dialog. This is important to embed credentials. The workaround is to use importrange separately at least once for each of your sheets that you plan to use in your sheet. Once you have allowed access to each one, you can remove these separate calls. This should fix the error. – Asad Rauf Jun 16 '22 at 10:25