UNIQUE ID

AppSheet AppSheet Q&A
,
1

Hi everyone! I’m in the middle of creating an Inventory Management App for my client. So far, all your tutorials have been so very helpful and easy to understand.

But my question is, my client has a list of products already with its own Product Code. But I want to create an ID as a Key for the table. Can I generate UniqueID automatically for those existing information already in Google Sheet or do I have to encode them again one by one to generate a UniqueID? I was hoping I can generate it automatically cause the list of products is about 50+ and it would save me a lot of time.

Hoping for your kind assistance. Thank you!

2 Likes
2

You could have the Google Doc run a function on the new ID column to create unique IDs. Then once done remove the function so new rows have the UniqueID created by Appsheet.

2 Likes
3

Hi,

Here is a code i use to do what you are trying to achieve.
It is a Google App Script code.
Hope it helps.

var SHEETNAME1 = "sheet1";
var SHEETNAME2 = "sheet2";
var ID_COLUMN = 1;
var ID_LENGTH = 8;

function generateUID () {
  var ALPHABET = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  var rtn = '';
  for (var i = 0; i < ID_LENGTH; i++) {
    rtn += ALPHABET.charAt(Math.floor(Math.random() * ALPHABET.length));
  }
  return rtn;
}

function SN1(evt) {
  var range = evt.range;
  var sheet = range.getSheet();
  if(sheet.getSheetName() !== SHEETNAME1) return;

  var rangeValues = range.getValues();

  rangeValues.forEach(function(row,index,arr){
    var conc = row.join("").length; 
    if(conc > 0) { 
      var idRange = sheet.getRange( range.getRow() + index, ID_COLUMN ); 
      var idCell = idRange.getCell( 1, 1 ); 
      var idValue = idCell.getValue(); 
      if (idValue == "") {
        idCell.setValue( generateUID() ); 
      }
    }
  })
}

function SN2(evt) {
  var range = evt.range;
  var sheet = range.getSheet();
  if(sheet.getSheetName() !== SHEETNAME2) return;

  var rangeValues = range.getValues();

  rangeValues.forEach(function(row,index,arr){
    var conc = row.join("").length; 
    if(conc > 0) { 
      var idRange = sheet.getRange( range.getRow() + index, ID_COLUMN ); 
      var idCell = idRange.getCell( 1, 1 ); 
      var idValue = idCell.getValue(); 
      if (idValue == "") {
        idCell.setValue( generateUID() ); 
      }
    }
  })
}

function onEdit(e) {
  SN1(e);
  SN2(e);
}
1 Like
4

Yes, you can

No matter how you will generate key values for initial data. But remember to generate really unique values within column. In my apps Im always use simply incrimented numbers 1,2… for initial data keys

2 Likes
5

This is the equivalent formula of UNIQUEID() in Google Sheet:

DEC2HEX( RANDBETWEEN(1; 4294967296); 8 )

To use it, paste this formula in an empty column for as many rows as you need, then copy the generated results, then go to your ID key column and choose Paste Values only. Then remove the column that you used in the beginning.

7 Likes
6

This is the recommended way by the AppSheet Help Docs

Manually Generating UNIQUEID() Key Values | AppSheet Help Center

3 Likes
7

Thanks Oscar for the link. I noticed an error, the formula in the link won’t work. I’ll post a Documentation Bug.

1 Like
8

Why it wouldn’t? If it’s because of the usage of , instead of ; let me tell you it depends on locale

1 Like
9

Curious, I didn’t know about that. Thank you!

I experimented a bit in Google Sheets:

  • Changing the locale to US, the semicolons in the formulas changed automatically to commas. I changed them back to semicolons, and the formulas where accepted.
  • Changing back to a European locale, the commas changed automatically to semicolons. Changing them back to commas, the formulas resulted in an error.

So it seams that semicolons are accepted by Google Sheet anyway, but commas will only work for US or similar locales.

10

I use excel so I cannot test it :joy:

But I’ve seen a similar patter on it, online and desktop versions

1 Like
11

I wanted to test in Excel but I don’t have it :disappointed_face: So again thank you :grinning_face:

12

Thank you for this! :heart:

13

Thank you! :heart:

1 Like
14

Just wonder: i use that formula for my existing rows of data.

Then for the new records i use uniqueID() from within AppSheet.

If i need to add new massive records that i add directly in my GoogleSheet is it possible to overwrite the whole column where i’ve my uniqueID codes with new uniqueID codes or i need to leave the old codes for the old records ?

15

Yes it is possible.

1 Like
16

If your app includes References between tables - AppSheet Help it’s crucial to leave existing key values in place since one table’s keys also exist in other tables.

17

No my tables are “stand alone” so no have references.

In this case i think i could overwrite old uniqueID codes with new one, is it correct?

18

Potentially. It’s only safe to revise those key values if they exist in only one place–e.g., not in other tables, not in other columns in the same table, not in the app on your or another user’s device from which the rows could be edited, not in any in-progress automation runs.

19

To be sure which is the best way to create new key values for the new massive records to add directly in Google Sheets without change/overwrite the old keys of existing records ?

20

Manual: Create your key values wherever is convenient and then paste them as values into the rows where you need them.

Automated: Investigate Apps Script.