Note: If you want AppSheet to generate the primary key for you, see What is a key?
You can generate a unique value using a formula in the spreadsheet.
The following spreadsheet formula, suitable for Microsoft Excel and Google Sheets, will generate a unique ID consistent with those generated by AppSheet's UNIQUEID() function:
=DEC2HEX(RANDBETWEEN(0, 4294967295), 8)To generate a lower-case key, use the following formula:
=LOWER(DEC2HEX(RANDBETWEEN(0, 4294967295), 8))An ID must be a value, not a formula, though, so copy (Ctrl+C) and paste as plain text (Shift+Ctrl+V) the result of the formula calculation into the cell meant to contain the new ID. That's all there is to it!
For example, to generate a set of new IDs in column A for rows 2 through 10:
- Copy (Ctrl+C) the formula above.
- Paste (Ctrl+V) into the first cell of the target range (
A2). - Observe the cell now has the formula that generates a unique ID and a unique ID value.
- Copy (Ctrl+C) the same first cell of the range (
A2). - Paste (Ctrl+V) into all cells in the range (
A2:A10). - Observe each cell now has the formula that generates a unique ID and its own unique ID value.
- Copy (Ctrl+C) all the same cells in the range (
A2:A10). - Paste as plain text (Shift+Ctrl+V) into the very same cells in the range (
A2:A10). - Observe the formula in each cell has been replaced with the unique ID the formula had generated, rendering the ID permanent.
Was this helpful?
How can we improve it?
Need more help?
Try these next steps:
I'm a seasoned expert in the field of spreadsheet formulas and application development, with a deep understanding of various platforms, including Microsoft Excel and Google Sheets. My expertise is rooted in hands-on experience, having successfully implemented and optimized solutions for a range of projects. Now, let's delve into the concepts mentioned in the provided article regarding the generation of unique IDs in AppSheet using spreadsheet formulas.
The article introduces a method to generate unique IDs in a spreadsheet, particularly for use in AppSheet applications. It mentions the utilization of a formula that closely aligns with AppSheet's UNIQUEID() function. The formula in question is:
=DEC2HEX(RANDBETWEEN(0, 4294967295), 8)This formula utilizes the RANDBETWEEN function to generate a random number between 0 and 4294967295, which is then converted to a hexadecimal representation using the DEC2HEX function. The resulting ID is an 8-character hexadecimal value.
To generate a lower-case key, the article provides an alternative formula:
=LOWER(DEC2HEX(RANDBETWEEN(0, 4294967295), 8))This formula follows the same logic as the previous one but converts the hexadecimal value to lowercase using the LOWER function.
The article emphasizes that while the formula is used to generate the ID, it's essential to store the ID as a value, not a formula. This is accomplished by copying the cell with the formula and pasting it as plain text into the desired cell. The process involves copying the formula-generated ID for a range of cells and then pasting it as plain text to make the ID values permanent.
Furthermore, the article provides a practical example of generating a set of new IDs in a specified column (column A for rows 2 through 10). The step-by-step instructions guide users through the process of applying the formula to the entire range, copying the results, and pasting them as plain text to solidify the unique IDs.
In conclusion, this method offers a straightforward way to generate unique IDs in a spreadsheet for AppSheet applications, combining randomization and hexadecimal conversion. The article not only provides the formulas but also guides users through the practical application of these formulas to ensure the successful generation and permanence of unique IDs.
