Manually Generating UniqueId Key Values

If you specify UniqueId() in the Initial Value property of your key column, AppSheet will automatically generate a unique key for any rows added through your application.

However, there are two cases in which you may need to generate unique key values yourself.

  1. The first, is when your worksheet contains existing data rows without a UniqueId() key value. In this case, you need to generate unique key values for these rows once, when you first create your application. Approach One is best in this case.
  2. The second, is when you wish to add new rows directly to your worksheet without going through the AppSheet application. Approach Two is best in this case.

 

Approach One

  1. Open your worksheet in Google Sheets.
  2. Add a new temporary column to your worksheet.
  3. Paste the formula =DEC2HEX(RANDBETWEEN(0,4294967295),8) into each cell of the temporary column. This formula will compute a UniqueId value for each row. If your worksheet specifies a Locale that uses comma as the decimal separator, use this formula instead =DEC2HEX(RANDBETWEEN(0;4294967295);8).
  4. Copy the computed values from the cells of the temporary column.
  5. Select the cells in the key column. Right click, and select “Paste Special” > “Paste values only”.
  6. Each key column in your worksheet will now contain a UniqueId value.
  7. Delete the temporary column.
  8. Close the worksheet in Google Sheets.

 

Approach Two

  1. Open your worksheet in Google Sheets.
  2. Paste the formula =IF(A2=0, DEC2HEX(RANDBETWEEN(0,4294967295),8), A2) into the key column. Both instances of "A2" in the formula must be replaced with the cell address in which you paste the formula. This formula will compute a UniqueId value for each row that does not already have one. If your worksheet specifies a Locale that uses comma as the decimal separator, use this formula instead =IF(A2=0; DEC2HEX(RANDBETWEEN(0;4294967295);8); A2). Both forms of the formula contain a circular reference which we resolve in step 4.
  3. Copy this formula into all subsequent cells in the key column.
  4. To resolve the circular reference, go to Google Sheets File menu and select Spreadsheet settings...
  5. Select Calculation and set Iterative calculation to "On" and Max number of iterations to 1 or more.
  6. Click Save settings.
  7. Close the worksheet in Google Sheets.

Caution: If you use Approach Two and then make a copy of the app, including a new copy of the sheet, you will need to repeat steps 4+ in the new copy. Additionally, the random values produced in the copy will not match the values in the original app. To make them match, you can copy and paste "values only" (Approach One, step 5) from the original into the copy, then reapply the formula from Approach Two to the column.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.