您可以使用 Utilities.getUuid()
生成 UUID。但是,由于 Google Sheet 的函数集中没有相应的函数,因此需要使用自定义函数来生成 UUID。为了生成 UUID,请执行以下操作:
=uuid()
放入工作表中的单元格中。function uuid() {
return Utilities.getUuid();
}
当使用自定义函数时,值会因电子表格的自动重新计算而发生变化。此示例将修复UUID。
function onEdit(e) {
if (e.range.getFormula().toUpperCase() == "=UUID(TRUE)") {
e.range.setValue(Utilities.getUuid());
}
}
function uuid() {
return Utilities.getUuid();
}
=uuid()
。
=uuid()
被放置为自定义函数。因此,当电子表格自动计算时,该值会发生变化。=uuid(true)
。
=uuid()
由 onEdit()
放置为值。因此,即使电子表格自动计算,该值也不会改变。=uuid(true)
,因为它使用了 OnEdit 事件触发器。修正版的 thinkyfish 公式,基于 broofa 在 如何创建 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))
这段代码是用于生成一个随机的UUID串,采用的是Excel函数CONCATENATE以及MID。其中,0123456789abcdef是16进制数,-4表示一个固定的字符,89ab是一段固定的16进制数。
符合UUID版本4标准。
DEC2HEX(RANDBETWEEN(0, 16^8-1), 8)
来实现。 - undefined=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))
创建的 UUID 会随时间变化。
我认为这个问题如果不使用 Apps Script 是无法解决的。
例如:
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());
}
}
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`);
}
定义命名函数
函数名称:RANDOM_HEX
公式定义:
=LOWER(DEC2HEX(RANDBETWEEN(0, 15)))
函数名称:RANDOM_HEX_BY_LENGTH
参数占位符 [可选]:length
公式定义:
=JOIN("", MAKEARRAY(1, length, LAMBDA(rowIndex, columnIndex, RANDOM_HEX())))
函数名称:UUID_V4
公式定义:
=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))
或者...
=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)))))))我推荐使用命名函数!
RANDOM_HEX_BY_LENGTH(8)
可以简化为 DEC2HEX(RANDBETWEEN(0, 16^8-1), 8)
。 - undefined4294967295
只是16^8-1
的实际值。我们使用2x16^6
而不是16^12
,因为16^12
超过了RANDBETWEEN允许的最大值。function uuid() {
return Utilities.getUuid();
}
如果你只是想要一个可以快速使用的公式,请尝试这个。需要注意的是,生成的值中不会有任何字母字符,但是对于几乎任何目的来说,这些值都应该足够好用。
=RANDBETWEEN(10000000,99999999) & "-" & RANDBETWEEN(1000,9999) & "-" & RANDBETWEEN(1000,9999) & "-" & RANDBETWEEN(1000,9999)& "-" & RANDBETWEEN(1000,9999) & RANDBETWEEN(10000000,99999999)