Automatically populate cell in Google Sheets when another cell in same row is manually filled

Question

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.

Tanaike · Accepted Answer · 2022-06-17 23:56:54Z

2

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

edited Jun 17, 2022 at 23:56

answered Jun 17, 2022 at 13:16

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ulfelder Over a year ago

Thank you. I imported and edited this in my own test sheet, and it produced the expected results. That said, is there a way to set the trigger to run on entry of data into a blank cell only (vs. any edit to said cell)? In my test sheet, I see that the ID values remain fixed when I sort rows, but the ID regenerates if I cut or copy and then paste a row. Ideally, they would remain fixed under those circumstances, too.

kaitlynmm569 Over a year ago

@ulfelder you could add an IF statement into the script to first check if there is already a value in the code's cell. Something like the following should work: if(dstRange.getValue() != '') return;. Place it below the dstRange declaration, but above the .setFormula(). This may not be the cleanest way to do it, but it should work.

ulfelder Over a year ago

Thank you, @kaitlynmm569. I was just tinkering with that idea but don't know JavaScript and couldn't get the syntax right. Your solution did the trick.

Tanaike Over a year ago

@ulfelder Thank you for replying. About That said, is there a way to set the trigger to run on entry of data into a blank cell only (vs. any edit to said cell)?, I updated the script. Could you please confirm it?

ulfelder Over a year ago

@Tanaike, yes, that modified script produces the desired behavior. I really appreciate you taking the extra time to respond to my follow-up question!

|

kaitlynmm569 · Accepted Answer · 2022-06-17 13:42:22Z

1

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!

edited Jun 17, 2022 at 13:42

answered Jun 17, 2022 at 13:29

kaitlynmm569

1,7251 gold badge9 silver badges20 bronze badges

2 Comments

ulfelder Over a year ago

Thank you! Your script worked for me, but I tagged the other answer as the solution because their code was a bit simpler.

kaitlynmm569 Over a year ago

No problem! I figured such would be the case, but provided mine simply because it was how I originally learned to complete something like this (that I was able to actually understand), and felt it may be helpful to beginner coders as well.

Collectives™ on Stack Overflow

Automatically populate cell in Google Sheets when another cell in same row is manually filled

2 Answers 2

Sample script:

Reference:

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Sample script:

Reference:

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related