Best Structure for Keys/Labels in Google Sheet to manipulate data in Google Sheets AND AppSheet

[Francis_Rafal]

Hi,
I have a question regarding the proper setup of keys/labels in Google Sheets so I can add and edit data in AppSheet AND in Google Sheets:

I have a Customer table (Columns: Primary Key, Customer Name)
and a Contacts table (Columns: Primary Key, Contact Name, Customers Primary Key, Customer Name)
Multiple Contacts can be related to one Customer.
The Primary Key Values are created either with AppSheet’s UNIQUEID() or manually in Google Sheets with =DEC2HEX(RANDBETWEEN(0; 4294967295); 8).

My main problem is that in

• Google Sheets I usually use the Customer Name (which is set as the Label in AppSheet) instead of the Primary Key (set as Key in AppSheet) as a dropdown validation to make connecting tables human-readable but
• AppSheet references use the Keys instead of Labels to connect tables.

That means that when I enter a new Contacts record in AppSheet, I have to pull the Customer Name through the Customers Primary Key into the new record, which works with the expression [Customers Primary Key].[Customer Name] as Initial Value in Customer Name.

But how do I properly enter new data in Google Sheets directly? When I add a Customer Name to a Contacts record with my dropdown validation, I would then have to pull the Customers Primary Key of this Customer Name from the Customers table. I could do it with a VLOOKUP ARRAYFORMULA but then I can’t really add new data to the Customers Primary Key column through AppSheet because ARRAYFORMULA only works when the cells to be filled are empty.
I could also use a normal VLOOKUP and copy-paste the formula down when I add a new record in Google Sheets directly but I want to avoid this manual work.

So my question is: How do I solve this in Google Sheets?
Or how do I generally setup proper Keys with UNIQUEID() and labels so that I can add data in AppSheet AND Google Sheets? Is there a better way?

Thanks so much!

Francis

[tony1]

Hi @Francis_Rafal. If you want a key value that is both unique and human readable, what about concatenating the customer name with a unique value? So your key values will look like Tony Fader-ad43fge5. This has the disadvantage that if you change the human-readable part of the record, the key value won’t be updated (e.g. if I change Customer Name from Tony Fader to Anthony Fader the key would remain as Tony Fader-ad43fge5).

You could do this by changing your initial value formula to something like [Customer Name] & "-" & UNIQUEID().

I don’t see any Google Sheets “row label” equivalent, unfortunately.

[Bahbus]

I guess my question is, why do you want to manually enter new data in Google Sheets when all the relevant information can be handled inside of AppSheet?

[Francis_Rafal]

Thank you both for your tips!
@tony Will give this a try!
@Bahbus
Our Google Sheets database has grown quite big and we’re used to entering data really quickly in Google Sheets, especially when we do it in batch. So I guess if there maybe was a way to upload a CSV in AppSheet or enter data in table form in AppSheet that could maybe remove our need to enter data in Google Sheets directly.

[Bahbus]

There is a CSV import function which could handle bulk updates, and you can setup the columns in AppSheet to do any other relevant work for displaying or whatever other manipulations you use AppSheet for.

A whole thread about making sure its working: Import data from CSV

[Francis_Rafal]

Great, thank you! Will check this out!