To add data to one cell automatically when another cell is edited in Google Spreadsheets

Question

I am trying to get a google spreadsheet to automatically add data to a cell when another cell is being edited, but I can only imagine that the onedit function works globally.

In particular, if B2, B3, B4, etc. were edited, I would like N2, N3, N4, etc. Automatically populated with a timestamp or username.

Any help would be greatly appreciated!

thanks

Answer 1

The extension of the answer is here ...

function onEdit(e) { var row = e.range.getRow(); var sheet = e.range.getSheet(); // if B2, B3, B4 etc. was edited, fill N2, N3, N4 etc. with a timestamp var value = new Date(); // ... or the user name. // var value = Session.getActiveUser().getEmail(); var headerRows = 1; // # header rows to ignore // Skip header rows if (row <= headerRows) return; // We're only interested in column B (aka 2) if (e.range.getColumn() != 2) return; sheet.getRange(row,14).setValue(value); // N<row> } 

Answer 2

For other users who can automatically add the date and time of data insertion.

I just ran into this problem and I changed the code provided by Internet Geeks .

Their code works by updating the specified column, the timestamp is inserted into the same row in another specified column.

What I changed is that I highlighted the date and time, because timestamp is a string, not a date format. Mine was useful for generating graphs.

It works by specifying a column to track changes, and then creating upDate and upTime columns for the date and time, respectively.

 function onEdit(event) { var timezone = "GMT+1"; var date_format = "MM/dd/yyyy"; var time_format = "hh:mm"; var updateColName = ""; var DateColName = "upDate"; var TimeColName = "upTime"; var sheet = event.source.getActiveSheet(); // All sheets // var sheet = event.source.getSheetByName('Test'); //Name of the sheet where you want to run this script. var actRng = event.source.getActiveRange(); var editColumn = actRng.getColumn(); var index = actRng.getRowIndex(); var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); var dateCol = headers[0].indexOf(DateColName); var timeCol = headers[0].indexOf(TimeColName); var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol + 1; if (dateCol > -1 && timeCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself! var cellDate = sheet.getRange(index, dateCol + 1); var cellTime = sheet.getRange(index, timeCol + 1); var date = Utilities.formatDate(new Date(), timezone, date_format); var time = Utilities.formatDate(new Date(), timezone, time_format); cellDate.setValue(date); cellTime.setValue(time); } } 

Hope this helps people.