Converting item and system IDs to names

hey folks,

i am working with googledocs to do industry related calculations. to retrieve the data i am using mainly code by fuzzy. to be explicit, those three:

with these i am able to create endless lists of data with item or system IDs and things like prices or cost indexes, which is fine. but i dont know which ID belongs to which item or system name.
can i use an additional function to also retrieve the names? how would such one have to look like? i heard of SDE. how can i work with that? when i am able to just generyte two colums in my googledocs, one with system/item ID and the other with their names, i am good to go.

unfortunately i dont know how to programm/write the needed code for those json files myself. till now i was only able to do slight modifications to fuzzy´s code (which i posted above).

thx for help in advance

Install, then use the =universe_names(A1:A1000) function. This will convert a range of IDs to names/categories.

The best way to work with this is store everything in a static sheet, then use =VLOOKUP to resolve the id to name from your static list of id => name.

alright i did that. works out great.
now the function loadRegionAggregates( ) seems no longer to work. it says: Internal error while executing the user-defined function. (translated from german)

could you imagine what the problem is? what does this kind of error mean? what do i have to look for?

That usually means it took more than 30 sec to complete. Maybe try cut pasting it to another cell.

The other option is to load the data from the SDE into different worksheets and then use a vlookup on them

Faster though with a downside of needing updated every so often. you mean changing the cell, in which i am using this function? did that, but does not make a difference. loading for 30 sec => error

how can i load data from SDE? do i have to download it? do i need some code to retrieve the data from that link?
do yo mean with different worksheet just a new tab/page in my current sheet or a whole new document?

i fixed function loadRegionAggregates( ). the error was due to too much requested data. i forgot to delete some unnecessary itemIDs. SO thats it when fuzzysteve means: to much to handle for googledocs.

now i got a new error when using function =universe_names( ). it says:

{“body”:{“error”:“failed to coerce value ‘id’ into type integer (format: int32), ‘ids’ is required”},“code”:400,“character”:“Bjoern Panzer”,“sheet_id”:“1d0yLrIS4B4LC6rPpgT1vyuev7K7xp78GwK3_QAKxjCI”,“path”:“”} (Zeile 308).

what to do?

Ah good to hear.

Make sure all the cells you are looking up contain numbers. That error happens when you try to look up non numeric id.

ok. got that. i took the header out, now it works properly. thanks again.

but what would a new day be without a new problem?^^ that looks odd right?

any ideas or suggestion? the first column is all function =vlookup( ) like in the top and just copied to the cells below

Hmm, hard to say without looking at it. Try setting vlookup isSorted property to false.

Can readup on the docs here: . Take a look at the first bullet in the notes section.

yeah, that solved it. i am wondering why this is not set as standart, but whatever.

now everything should work properly. alot of thanks to both of you. keep it up! :slight_smile:

i hope, i dont have to bother you again

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.