CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Alister
Alister

Reputation: 21

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

I've been trying get Google spreadsheet to add data into a cell automatically when another cell is edited, however I can only seem to make the onedit function work globally.

Specifically, if B2, B3, B4, etc. was edited, I would like N2, N3, N4 etc. to automatically fill with a timestamp, or the user's name.

Upvotes: 1

Views: 2567

Answers (2)

Sir Rubberduck
Sir Rubberduck

Reputation: 2276

For others that might want to automatically add the date and time of the data insert.

I just came across this problem and I modified the code provided by Internet Geeks.

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

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

It works by specifying the column to track for changes, and then creating an 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.

Upvotes: 0

Mogsdad
Mogsdad

Reputation: 45750

Expanding on the answer 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's 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>
}

Upvotes: 2

Related Questions