CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulagoogle-sheets-queryimportrange
user12341234
user12341234

Reputation: 1497

Different size rows in array literal Google Sheets

I'm trying to do this in Google Sheets:

={{1}; {1, 1}}

and get the error In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

My ultimate goal is to do something like this:

"title A"
IMPORTRANGE("spreadsheet A", A:Z)...
"title B"
IMPORTRANGE("spreadsheet B", A:Z)...
"title C"
IMPORTRANGE("spreadsheet C", A:Z)...
etc...

Preferably the import range would ignore empty A rows as well. Each spreadsheet has different amount of columns and rows and they all change every once in a while.

Upvotes: 1

Views: 1140

Answers (1)

player0
player0

Reputation: 1

you need to do it like this:

={{1, ""}; {1, 1}}

and for your IMPORTRANGE example it will be like:

={{"title A","","","","","","","","","","","","","","","","","","","","","","","","",""}; 
 IMPORTRANGE("ID", "Sheet1A:Z")}

but to avoid repeating of empty cells you can do:

={{"title A", SUBSTITUTE(SPLIT(REPT(12, 25), 2), 1, )};
 IMPORTRANGE("ID", "Sheet1A:Z")}

which means: repeat (REPT) number 12 twenty-five times (because you need to cover range B:Z) this will give you number: 12121212121212121212121212121212121212121212121212 and now you will SPLIT it every time there is number 2 so you will get 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 across 25 cells. and to get rid of those 1's you will SUBSTITUTE number one for nothing

or like this:

=ARRAYFORMULA({{"title A", SUBSTITUTE(COLUMN(B:Z)^0, 1, )};
 IMPORTRANGE("ID", "Sheet1A:Z")})

also don't forget to run every IMPORTRANGE separately before attempting your joint formula. you need to allow access to connect your spreadsheets with each unique IMPORTRANGE

={{"title A", SUBSTITUTE(SPLIT(REPT(12, 25), 2), 1, )};
 IMPORTRANGE("ID1", "Sheet1A:Z");
  {"title B", SUBSTITUTE(SPLIT(REPT(12, 25), 2), 1, )};
 IMPORTRANGE("ID2", "Sheet1A:Z");
  {"title C", SUBSTITUTE(SPLIT(REPT(12, 25), 2), 1, )};
 IMPORTRANGE("ID3", "Sheet1A:Z")}

to get rid of empty rows you could do something like:

=QUERY({{"title A", SUBSTITUTE(SPLIT(REPT(12, 25), 2), 1, )};
        IMPORTRANGE("ID1", "Sheet1A:Z");
        {"title B", SUBSTITUTE(SPLIT(REPT(12, 25), 2), 1, )};
        IMPORTRANGE("ID2", "Sheet1A:Z");
        {"title C", SUBSTITUTE(SPLIT(REPT(12, 25), 2), 1, )};
        IMPORTRANGE("ID3", "Sheet1A:Z")}, 
 "where Col1 is not null", 0)

Upvotes: 1

Related Questions