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

Map Utilities.getUuid() to a Custom Function

Google Workspace Apps Script Documentation describes the getUuid() method as:

Get a UUID as a string (equivalent to using the java.util.UUID.randomUUID() method).

Create The Custom Function

The above getUuid() method can be mapped to a custom function, =UUID.

Click Extensions / Apps Script

Paste in the Following JavaScript

Note custom functions appear in AUTOCOMPLETE only if their script includes a JsDoc @customfunction tag.

/**
 * Returns a UUID v4
 * @customfunction
 */
function UUID() {
  return Utilities.getUuid();
}

Use The Custom Function

In any cell, type in =UUID and press Enter. In order to store the returned UUID, you'll want to copy / paste as value after the custom function =UUID returns a value.

Answer 2

Here's a modified version of @Greg Fenton that generates uuid only upto the last row of the active range and make no changes to what already exists:

function _genUuid_v2() {
  let curSheet = SpreadsheetApp.getActiveSpreadsheet();
  let curSelection = curSheet.getSelection();
  let actRange = curSelection.getActiveRange();
  let curRange = curSheet.getRangeByName("static_id_2_nr");

  if (curRange.getNumColumns() !== 1) {
    Logger.log(`Range must only contain one column.`);
    return;
  }

  for (let i = 0; i < curRange.getNumRows(); i++) {
    let _lastRowOfActiveRange = actRange.getLastRow();
    if((i+1) <= _lastRowOfActiveRange){
      if(i == 0){      
        curRange.getCell(1 + i, 1).setValue("Static ID")  
      }else{
        Logger.log(curRange.getCell(1 + i, 1).getValue());
        if(curRange.getCell(1 + i, 1).getValue() == ''){
          curRange.getCell(1 + i, 1).setValue(Utilities.getUuid());
        }        
      }
    }        
  }

  Logger.log(`Added ${curRange.getNumRows()} UUIDs`);
}

This function is invoked by a trigger onFormSubmit.

Answer 3

Seeing a lot of overly complicated solutions for this. We can generate a UUID4 via this:

=lower(CONCATENATE(DEC2HEX(RANDBETWEEN(0, 16^8-1), 8),"-",DEC2HEX(RANDBETWEEN(0, 16^4-1), 4),"-",DEC2HEX(RANDBETWEEN(0, 16^4-1), 4),"-",DEC2HEX(RANDBETWEEN(0, 16^4-1), 4),"-",DEC2HEX(RANDBETWEEN(0, 16^6-1), 6)),DEC2HEX(RANDBETWEEN(0, 16^6-1), 6)))

Remove the lower if you want the uppercase version.

This is based on the formula from here: https://support.google.com/appsheet/answer/10105828?hl=en

The following spreadsheet formula, suitable for Microsoft Excel and Google Sheets, will generate a unique ID consistent with those generated by AppSheet's UNIQUEID() function:

=DEC2HEX(RANDBETWEEN(0, 4294967295), 8) To generate a lower-case key, use the following formula:

=LOWER(DEC2HEX(RANDBETWEEN(0, 4294967295), 8))

4294967295 is just the actual value of 16^8-1. We use 2x16^6 rather than a 16^12 because 16^12 exceeds the max allowed value for RANDBETWEEN.

Though I imagine:

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

Is probably still the better answer if you are scripting, but if you want to avoid scripting the formula above should do the job.

Answer 4

Define Named Functions

Function Name: RANDOM_HEX
Formula definition:
=LOWER(DEC2HEX(RANDBETWEEN(0, 15)))

Function Name: RANDOM_HEX_BY_LENGTH
Argument placeholders [OPTIONAL]: length
Formula definition:
=JOIN("", MAKEARRAY(1, length, LAMBDA(rowIndex, columnIndex, RANDOM_HEX())))

Function Name: UUID_V4
Formula definition:
=JOIN("-", RANDOM_HEX_BY_LENGTH(8), RANDOM_HEX_BY_LENGTH(4), RANDOM_HEX_BY_LENGTH(4), RANDOM_HEX_BY_LENGTH(4), RANDOM_HEX_BY_LENGTH(12))

Or...

=JOIN("-", JOIN("", MAKEARRAY(1, 8, LAMBDA(rowIndex, columnIndex, LOWER(DEC2HEX(RANDBETWEEN(0, 15)))))), JOIN("", MAKEARRAY(1, 4, LAMBDA(rowIndex, columnIndex, LOWER(DEC2HEX(RANDBETWEEN(0, 15)))))), JOIN("", MAKEARRAY(1, 4, LAMBDA(rowIndex, columnIndex, LOWER(DEC2HEX(RANDBETWEEN(0, 15)))))), JOIN("", MAKEARRAY(1, 4, LAMBDA(rowIndex, columnIndex, LOWER(DEC2HEX(RANDBETWEEN(0, 15)))))), JOIN("", MAKEARRAY(1, 12, LAMBDA(rowIndex, columnIndex, LOWER(DEC2HEX(RANDBETWEEN(0, 15)))))))

I recommend Named Functions!

Answer 5

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

Answer 6

thinkyfish formula corrected, based on broofa's response at How do I 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 7

I tried creating a pure sheet function that creates full uuid's just with sheet code and this is what I ended up with it's pretty silly but it does seem to work. It will re-generate every time the sheet updates so this might not be useful but I wanted to post it anyway because there wasn't a pure sheet implementation available.

=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),"-",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),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))

Answer 8

Thanks for all the details on this thread. What I ended up doing is creating a macro that will fill all the selected cells in a range each with their own UUID. I use this approach to set a value in selected cells and not worry about "overwriting" them. I can use Protect Range to avoid the cells being updated inadvertently.

This script has 2 checks:

1. selected range must cover just a single column
2. all selected cells must be blank (i.e. no selected cell can have a value)

If the above 2 constraints are met, then each cell gets filled with a value. Here's the macro code:

function fillSelectedWithUUIDs() {
  let curSheet = SpreadsheetApp.getActiveSheet();
  let curSelection = curSheet.getSelection();
  let curRange = curSelection.getActiveRange();

  let ui = SpreadsheetApp.getUi();

  if (curRange.getNumColumns() !== 1) {
    ui.alert(`Range must only contain one column.`);
    return;
  }

  for (let i = 0; i < curRange.getNumRows(); i++) {
    let curCell = curRange.getCell(1 + i, 1);
    if (curCell.getValue() !== "") {
      ui.alert(`ERROR: Cannot overwrite value in cell (${curCell.getA1Notation()})`);
      return;
    }
  }

  for (let i = 0; i < curRange.getNumRows(); i++) {
    curRange.getCell(1 + i, 1).setValue(Utilities.getUuid())
  }

  ui.alert(`Added ${curRange.getNumRows()} UUIDs`);
}

Hope this is of use to some!

Answer 9

Try this if you just want a formula you can quickly drop in. The caveat here is that you won't have any alpha characters in the generated value, but these should be "good enough" for nearly any purpose regardless.

=RANDBETWEEN(10000000,99999999) & "-" & RANDBETWEEN(1000,9999) & "-" & RANDBETWEEN(1000,9999) & "-" & RANDBETWEEN(1000,9999)& "-" & RANDBETWEEN(1000,9999) & RANDBETWEEN(10000000,99999999)

Answer 10

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());
  }
}