Google Sheets - creating unique ID only for new rows when they are added-CodePudding

I have a Google Sheet (with header row) and I need to create a uniqueID for each new row as it's added.

I can create the ID like this (an AppsScript)

function generate() { 
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const row = sheet.getLastRow();
const randomUnique = `${Math.random().toString(36).slice(2)}-${Date.now()}`
}

and I call the function like this

=ArrayFormula(IF(ISBLANK(A2:A),,generate()))

Simply if there is a value in Column A create the ID

BUT when I add the final line to the generate() function I always get an error

This is the final line

 sheet.getRange(row,2 ).setValue(randomUnique);

This is the error

You do not have permission to call setValue()

To summarise, when row X column A has a value I need to have the ID created and inserted into row X Column B

Once set that ID must not change

How do I correct the function / formulas - thanks in advance

CodePudding user response：

You need to return a value.

function generate() { 
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const activeRange = sheet.getActiveRange();
    //stop recalc, if needed:
    //if(activeRange.getValue() !== '') return activeRange.getValue();
    const activeRow = activeRange().getRow();
    const lrow = sheet.getLastRow();
    const randomUnique = () => `${Math.random().toString(36).slice(2)}-${Date.now()}`
    return randomUnique();
    //or return Array(lrow-activeRow).fill("").map(_ => [randomUnique()])
}

CodePudding user response：

Unfortunately, in the current stage, setValue cannot be used with the custom function. And also, when the values are put with the custom function, the values are changed by the recalculation. So, in this case, in order to achieve your goal of To summarise, when row X column A has a value I need to have the ID created and inserted into row X Column B and Once set that ID must not change, how about using a Google Apps Script as not the custom function? The sample script is as follows.

Sample script:

function mYFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  const values = sheet.getRange("A2:B"   lastRow).getDisplayValues().map(([a, b]) => [a && b ? b : a && !b ? `${Math.random().toString(36).slice(2)}-${Date.now()}` : ""]);
  sheet.getRange("B2:B"   lastRow).setValues(values);
}

When this script is run, the columns "A" and "B" are checked. When there are the value of column "A" and no value of column "B", ${Math.random().toString(36).slice(2)}-${Date.now()} is used. When there are the value of columne "A" and "B", the value of column "B" is not changed.

Sample script:

References: