How to generate an uuid in google sheet?

Dom picture Dom · Jul 9, 2018 · Viewed 17.9k times · Source

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

Either with a macro? or a formula?

Answer

Tanaike picture Tanaike · Jul 9, 2018

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 :

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: