How to generate an uuid in google sheet?

Question

How to generate an uuid in google sheet (for exemple ccb8a660-91c9-a556-58e0-4fa7-229516872004).

Either with a macro? or a formula?

Answer 1

You can generate UUID using Utilities.getUuid(). But it is required to use a custom function for achieving what you want, because there are no functions for it in Spreadsheet's functions. In order to generate UUID, please do the following flow.

1. Open the script editor for creating Google Apps Script.
2. Copy and paste the following script and save it.
3. Put =uuid() to a cell in a sheet.

By this, you can get UUID.

Script :

function uuid() {
  return Utilities.getUuid();
}

Reference :

• getUuid()

If I misunderstand your question, I'm sorry.

Added:

When a custom function is used, the value is changed by the automatically recalculating of Spreadsheet. If you want to fix the value, how about this sample script? Please think of this as just one of several workarounds.

Sample script:

function onEdit(e) {
  if (e.range.getFormula().toUpperCase()  == "=UUID(TRUE)") {
    e.range.setValue(Utilities.getUuid());
  }
}

function uuid() {
  return Utilities.getUuid();
}

• When you use this script, please do the following flow.
  1. Copy and paste the script to the bound-script of Spreadsheet and save it.
  2. Put =uuid() to a cell in a sheet.
     • In this case, =uuid() is put as a custom function. So when the Spreadsheet is automatically calculated, the value is changed.
  3. Put =uuid(true) to a cell in a sheet.
     • In this case, =uuid() is put as a value by onEdit(). So even when the Spreadsheet is automatically calculated, the value is NOT changed.

Note:

• In this case, =uuid(true) can use when the function is manually put, because this uses the OnEdit event trigger.
• This is a simple sample script. So please modify this for your situation.

Reference:

• Simple Triggers

Answer 2

thinkyfish formula corrected, based on broofa's response at How to create a GUID / UUID

=CONCATENATE(MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),"-",MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),"-4",MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),"-",MID("89ab",RANDBETWEEN(1,4),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),"-",MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1),MID("0123456789abcdef",RANDBETWEEN(1,16),1))

UUID Version 4 compliance.

Answer 3

the created uuid is changed with the time.

I think this problem could not be solved without using the Apps Script.
For example:

var sheet = SpreadsheetApp.getActiveSheet();
var values = sheet.getRange('A1:A10').getValues();

for (var i = 0; i < values.length; i++) {
  // When a cell is empty, set a uuid to the cell.
  if (!values[i][0]) {
    sheet.getRange('A' + (1 + i)).setValue(Utilities.getUuid());
  }
}