How to create unique primary key in Google Sheets?

Dan_Underhill · 05-21-2020 12:59 PM

Hi there,

I’m used to working with mySQL and I’m new here playing about with AppSheet trying to re-create an older app I wrote years ago. Think of it as a specialized Rolodex book.

I’m reproducing my old mySQL database using Google Sheets. My question is what happens when I go to create a new record into that table in terms of a Primary Key? First of all, AppSheet doesn’t seem to let me hide the Primary Key in the Preview which I don’t want to see. But if I add a new name record, I need for it to have a new unique Primary Key of course, just like in mySQL. But it looks like it doesn’t do that if I’m seeing it correctly? How are my employees supposed to know how to create a new record AND create a unique Primary Key that gets saved back into the Sheet. Normally the end user just creates the record and doesn’t worry about the creation of a unique Primary key for that entry. How do we overcome that in AppSheet? Maybe link it to a mySQL DB instead of a Google Sheet?

Thanks

tvinci

Steve

You need to designate a column as the table’s key column, and make sure the column value for each new row is set to a value unique within the column.

Typically, the key column value is set by the column’s configured Initial value expression, but you could also use a user input column as the key column, with the understanding that the key column value cannot be changed once saved initially.

The UNIQUEID() function exists specifically to assist creating unique key column values. A common Initial value expression is, simply: UNIQUEID().

There are a variety of ways to hide column values from display. A good method to start with is the Show_If column constraint. You can also choose which columns to hide or display in the view configurations for most view types.