In Google Sheets, I want to create a macro that will automatically populate a column in each row when another column in that row is manually filled. The autofilled cell will use a formula that takes a chunk of the information (date) that's been entered manually and use a formula to concatenate it with a random number in order to create a unique ID. After inserting and executing the formula, the macro needs to copy and then paste "values only" the result of that formula. The point is to automatically create a stable ID in response to a triggering event (entry of date in row).

In pseudocode, here's the process I'd like the macro to execute:

when (date in yyyy-mm-dd format entered into A[i]) {

  fill B[i] with =CONCATENATE(SUBSTITUTE(LEFT(A[i], 7), "-", ""),RANDBETWEEN(0,1000000000));
  copy B[i];
  PASTE_VALUES B[i] in B[i];

}

Apologies if I've overlooked a previous answer that solves this problem. I'm not new to coding, but I am new to coding in Google Sheets and am not sure what terms or phrases to use to describe what I'm after.

CodePudding user response：

I believe your goal is as follows.

• For example, when a value with the format of yyyy-mm-dd is put to the cell "A1", you want to put the formula of =CONCATENATE(SUBSTITUTE(LEFT(A1, 7), "-", ""),RANDBETWEEN(0,1000000000)) to the cell "B1".
• You want to fix the value of the formula as the value.
• You want to achieve this using OnEdit trigger.
• Added: You want to put the value to the column "B", when the column "B" is empty.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet, and save the script. And, please set the sheet name you want to use. When you use this script, please put the value with the format of yyyy-mm-dd to the column "A", by this, the script is run.

function onEdit(e) {
  const sheetName = "Sheet1"; // Please set the sheet name.

  const range = e.range;
  const sheet = range.getSheet();
  const [a, b] = range.offset(0, 0, 1, 2).getDisplayValues()[0];
  if (sheet.getSheetName() != sheetName || range.columnStart != 1 || !/\d{4}-\d{2}-\d{2}/.test(a) || b) return;
  const dstRange = range.offset(0, 1);
  dstRange.setFormula(`=CONCATENATE(SUBSTITUTE(LEFT(${range.getA1Notation()}, 7), "-", ""),RANDBETWEEN(0,1000000000))`);
  SpreadsheetApp.flush();
  dstRange.copyTo(dstRange, { contentsOnly: true });
}

Reference:

• Simple Triggers

CodePudding user response：

This is the script I came up with:

/** @OnlyCurrentDoc */

function onEdit(e) { //Runs every time the sheet is edited
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1'); //Change this to whatever your sheet is named
  var inputCol = sheet.getRange('A1'); //Change this to whatever column the date is going to be entered

  //This is the range that will be checked. Slightly redundant, but makes it easier to reuse this script without needing to retype every variable
  var myRange = inputCol;

  //Get the row & column indexes of the active cell
  var row = e.range.getRow();
  var col = e.range.getColumn();

  //Check that your edited cell is within the correct column
  if (col == myRange.getColumn()) { //This runs only if a value is entered into the column defined in 'inputCol'
    if(sheet.getRange(e.range.getA1Notation()).getValue() == '') {return}; //If the edited cell is empty (ie the date is deleted, nothing happens)
    if(row == 1) {return} //If the header is changed, nothing happens
    let codeCell = sheet.getRange('B' row); //Change to the column that will store the generated code value
    codeCell.setValue('=CONCATENATE(SUBSTITUTE(LEFT(A' row ', 7), "-", ""),RANDBETWEEN(0,1000000000))');
    let hardValue = codeCell.getValue(); //Gets the value from the formula you just entered
    codeCell.setValue(hardValue); //Replaces the formula with just the resulting value
  };
}

Comments are included to explain everything that is happening. Linked below is the spreadsheet I used to test this. It is set to allow editing, so feel free to use it to test the script yourself.

https://docs.google.com/spreadsheets/d/1UONgRPBEbxn8CQeiRSPS4eFKHjh4ae8hXGYn6ImHxeI/edit?usp=sharing

Hope this helps!