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

Generate UUID

You can generate a UUID using Utilities.getUuid(). But it is required to use a custom function because there are no functions for it in Google Sheet's set of functions. In order to generate UUID, please do the following:

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

Script :

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

Reference :

• getUuid()

Generate Static UUID

When a custom function is used, the value is changed by the automatic recalculating of Spreadsheet. This example will fix the UUID.

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