Error Using ARRAYFORMULA()

[poing]

I'm trying to use ARRAYFORMULA() to apply functions to the entire column. But am getting an error...

Error
Error: Expected required argument item_ids to be a number|number[], but not every item in the array is a number. (line 202).

My spreadsheet is set up to change characters, to avoid ESI calls when a character has fewer assets.

As you can see in the following image, GESI functions being run without data...

Simple Build

Accounts Page

I show the authenticated characters, the main character, and have a drop-down to select the character to use.

A1=getAuthenticatedCharacterNames()
B2={Data->Data Validation(List from a range=$A$1:$A$10)}
C1=getMainCharacter()

Using Accounts!$B$1 as name for most GESI requests.

Assets Page

Pretty straight forward...

A1=characters_character_assets(Accounts!B$1)

Locations Page

I am currently navigating the ESI locations. I started with UNIQUE() to minimize external calls. Followed by finding which location_id is also a item_id, for ships, containers, etc... with a working use of ARRAYFORMULA().

A1=UNIQUE(Assets!E:F)
C1=ARRAYFORMULA(IF(ISNUMBER(A:A),VLOOKUP(A:A,{Assets!C:C, Assets!H:H},2,FALSE),))

Error

I experienced the error when adding the following. D1 is just to show the ARRAYFORMULA() value is handled correctly by Google Sheets. E1 is using universe_structures_structure() and has the error.

D1=ARRAYFORMULA(IF(ISNUMBER(A:A),A:A,))
E1=ARRAYFORMULA(IF(ISNUMBER(A:A),INDEX(universe_structures_structure(A:A,Accounts!B$1,FALSE),1),))

I did try to cast the result as a number with VALUE(), but no change.

E1=ARRAYFORMULA(IF(ISNUMBER(A:A),INDEX(universe_structures_structure(VALUE(A:A),Accounts!B$1,FALSE),1),))

And the shown values work fine. While it was obtained by ARRAYFORMULA() it does work with GESI.

F2=INDEX(universe_structures_structure(D2,Accounts!B$1,FALSE),1)
F3=INDEX(universe_structures_structure(D3,Accounts!B$1,FALSE),1)
...
F9=INDEX(universe_structures_structure(D3,Accounts!B$1,FALSE),1)

Which leads be to believe GESI may have an issue with the ARRAYFORMULA() interaction.

@Blacksmoke16 great plug-in!

[Blacksmoke16]

@poing If I'm reading this correctly, your array formula is going thru each row, calling another GESI function to get the name of the structure/station for that row correct?

If so I would HIGHLY suggest not doing that and instead maintain some data sheets that map ID => Name, and use VLOOKUP to handle it. Otherwise this is a good way to make your sheet very slow and quickly reach the request limit.

[poing]

If so I would HIGHLY suggest not doing that and instead maintain some data sheets that map ID => Name, and use VLOOKUP to handle it. Otherwise this is a good way to make your sheet very slow and quickly reach the request limit.

@Blacksmoke16 that is exactly what I am doing! I am in the process of creating the "data sheets that map ID => Name" and will "use VLOOKUP to handle it."

That's the reason for pulling location_id from the Assets page withUNIQUE(). Reducing the requests to getting each structure/station once and only once.

Although you're right, I might want to cache the structures/stations once I have them. But would still want the flexibility to recognize any newly added structures/stations.

Of course it doesn't solve the ARRAYFORMULA() issue I am experiencing.

And I would still like to use ARRAYFORMULA(), even if I cache the structures/stations later. Which would only change its use to something like this...

- ARRAYFORMULA(IF(ISNUMBER(A:A),universe_structures_structure(A:A),))
+ ARRAYFORMULA(IF(ISNUMBER(A:A),IF(VLOOKUP(A:A,LocationCache!A:Z,n),VLOOKUP(A:A,LocationCache!A:Z,n),universe_structures_structure(A:A)),))

Using VLOOKUP() for structures/stations in the cache, while making a request for what's not in the cache.

[Blacksmoke16]

@poing I think the ultimate problem is that even with your conditional logic, it's providing a range that includes empty/non-integer cell values.

IMO I'd just always use VLOOKUP, if it ever fails, call the related function on that ID, copy/paste the ID/name output into your data sheet and call it a day. If you're noticing it failing a lot, you could write a custom function using jS to handle the caching. I.e. lookup the data in your data sheet, iterate over the passed range, if the ID is present in your data sheet, return the name for that ID, otherwise lookup that ID, append it to the data sheet, and return name.