Generating product IDs for an existing database

Tony_Cicerone · 07-09-2020 09:37 AM

Realizing I need to have product ids for the app I’m designing. I’m working off a pre-existing database, is there a way to add the IDs needed vie app sheet? I was hoping that setting the feild to UNIQUEID() would do it, but only works for new entries.

LeventK

Are you using Google Spreasheet as a database?

Tony_Cicerone

Yup!

LeventK

@Tony_Cicerone

1.) From your spreadsheet go to Tools > Script Editor which will launch the script editor for your gSheet
2.) Copy & Paste below code in the editor, give a friendly name to your script project and save all

/**
 * Calculate Universally Unique ID (RFC1422 compliant)
 * @return {string} Universally Unique Identifier Value
 */
function UUID(){
   var chars = '0123456789aAbBcCdDeEfFgGhHjJkKmMnNpPqQrRsStTvVwWxXyYzZ'.split('');
   var uuid = [], rnd = Math.random, r;
   uuid[8] = uuid[13] = uuid[18] = uuid[23] = '-';
   uuid[14] = '4'; // version 4

   for (var i = 0; i < 36; i++) {
  	if (!uuid[i]) {
     	    r = 0 | rnd()*16;
     	    uuid[i] = chars[(i == 19) ? (r & 0x3) | 0x8 : r & 0xf];
  	}
   }
   return uuid.join('').substring(0, 8);
}

3.) In first blank cell where your ID column is type the below sheet formula and hit enter:

=UUID()

4.) Drag this cell and fill your empty ID range of cells.
5.) After all your empty IDs are filled out, select the whole column Copy > Paste Values Only
6.) Sync the app and all done

Tony_Cicerone

Thanks!

LeventK

You’re welcome

Guy_Merlin_Dyan

Really Amazing @LeventK

Brand-It

Thank you for this… this is a great little trick!

Greg_Pommen

Beautiful! It worked first try!

Marc_Dillon

A script seems a bit overkill here.

I use this spreadsheet formula to create the IDs, then copy and “paste values only” (ctrl-shift-v)

=DEC2HEX(RANDBETWEEN(0, 999999999), 8)

Lynn

Marc_Dillon

Yay a new help article I didn’t know about!

Lynn

That’s an old one.