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?

score 96 · Accepted Answer · edited Mar 07 '23 at 16:39

96

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:

Open the Google Apps Script editor.
Copy and paste the following script and save it.
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

edited Mar 07 '23 at 16:39

serraosays

7,163
3
35
60

answered Jul 09 '18 at 21:58

Tanaike

181,128
11
97
165

1

@I'-'I Thank you for the comment. When ``Utilities.getUuid()`` is run, the result value is always difference. But I have no information about whether the created value is changed with the time. I'm sorry for this situation. If I found about the information, I would like to report it. If I misunderstand your comment, please tell me. – Tanaike Jul 10 '18 at 22:45
1

@I'-'I I'm also interested in the change with the time. I would like to try to investigate it. Thank you for providing the interesting theme. – Tanaike Jul 10 '18 at 22:50
Is the proposed solution creating a UUID V4? This criteria is crucial in my project – tivoni Oct 30 '22 at 16:50
@tivoni About your question of `Is the proposed solution creating a UUID V4? This criteria is crucial in my project`, the official document of [getUuid()](https://developers.google.com/apps-script/reference/utilities/utilities#getUuid()) says "Get a UUID as a string (equivalent to using [the java.util.UUID.randomUUID() method](https://docs.oracle.com/javase/7/docs/api/java/util/UUID.html)).". Is this information useful? – Tanaike Oct 30 '22 at 23:09
According to the java.util documentation, it's a uuid V2. Which brings me to the question, is it possible to generate a V4 via the GS Utilities interface? – tivoni Nov 01 '22 at 00:07
@tivoni Thank you for replying. About your new question of `According to the java.util documentation, it's a uuid V2. Which brings me to the question, is it possible to generate a V4 via the GS Utilities interface`, in this case, I would like to recommend posting it as a new question. Because your new question is different from this question. If you can cooperate to resolve your new question, I'm glad. Can you cooperate to resolve your new question? – Tanaike Nov 01 '22 at 00:09
Agree. Posted https://stackoverflow.com/questions/74292330/create-a-uuid-version-4-via-utilities-getuuid-in-google-sheets – tivoni Nov 02 '22 at 16:09
1

@tivoni Thank you for your response. About your new question, now, I noticed that your new question has already been resolved. I'm glad for it. – Tanaike Nov 03 '22 at 00:46

score 22 · Answer 2 · edited Nov 03 '22 at 00:39

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.

Is there any way to prevent the value from recalculating everytime the sheet is updated? EDIT: Seems like this is impossible when using ```RANDBETWEEEN```, but may be [possible using psuedo random values](https://webapps.stackexchange.com/questions/120983/how-can-i-generate-a-random-number-that-does-not-change-in-google-sheets) — ANimator120, Jun 22 '21 at 23:34

score 5 · Answer 3 · answered May 31 '19 at 18:04

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

score 4 · Answer 4 · edited Nov 03 '22 at 00:33

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))

score 1 · Answer 5 · answered Jan 16 '21 at 22:06

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:

selected range must cover just a single column
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!

score -1 · Answer 6 · answered Aug 24 '20 at 16:22

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)