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
LinkedIn
Twitter
