CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestamp
user12333294
user12333294

Reputation: 9

Username and Timestamp when a cell is edited in Google Sheets using Google App Script

I'm trying to write a script to achieve the following:

Let's say in google sheets data is entered in Col K. I would like Col L to give me the username of the person who entered the data and Col M to show me when the data was entered with a timestamp. Further, if Col K is updated later on in the future, i want Col L and M to remain as is but Col N to show username of the person updated Col K and Col O to show the timestamp when the user updated Col K.

I wrote a script (see below) which seems to be working fine on the columns K,L,M,N,O however when I change the script to work for columns to F, G, H, I, J I get the timestamps in columns H and J but I don't get the usernames in Col G and I which seems odd. Could someone see what is the issue here?

function onEdit(event) {
  var name = event.range.getSheet().getName();
  switch (name) {
    case "AA":
      update(event)
      break;
  }
}


function update(event){

  var ColK = 11;  // Column Number of "K"

  var changedRange = SpreadsheetApp.getActiveRange();
  if (changedRange.getColumn() == ColK) {
  if (changedRange.getRowIndex() >=7 ) {
    // An edit has occurred in Column J
    var state = changedRange.getValue();
    var adjacentDate = SpreadsheetApp.getActive().getSheetByName("AA").getRange(changedRange.getRow(),ColK+4);
    var adjacentDate2 = SpreadsheetApp.getActive().getSheetByName("AA").getRange(changedRange.getRow(),ColK+2);
    var adjacentLDAP = SpreadsheetApp.getActive().getSheetByName("AA").getRange(changedRange.getRow(),ColK+3);
    var adjacentLDAP2 = SpreadsheetApp.getActive().getSheetByName("AA").getRange(changedRange.getRow(),ColK+1);
    var timestamp = new Date(); // Get the current time

    adjacentDate2.setValue(timestamp);
    adjacentLDAP2.setValue(Session.getActiveUser().getEmail());

    if (adjacentDate.getValue() == "") {

        // Write timestamp into adjacent cell
        adjacentDate.setValue(timestamp);
        adjacentLDAP.setValue(Session.getActiveUser().getEmail());
    }
  }
    }
}

Upvotes: 0

Views: 1029

Answers (1)

CodeCamper
CodeCamper

Reputation: 6980

I suggest you use something like this as you may have multiple edits and you could very easily run out of columns. Then you can use a simple formula to extract the first or last edit if needed easily visible.

var triggerSheet = "AA"; //trigger sheet
var triggerRow = 7; //trigger row
var ColK = 11;  // trigger column
var ColL = 12;  // comma seperated users
var ColM = 13;  // comma seperated time stamps

function onEdit(event) {
  var name = event.range.getSheet().getName();
  switch (name) {
    case triggerSheet:
      update(event)
      break;
  }
}

function update(event){
  var changedRange = SpreadsheetApp.getActiveRange();
  if (changedRange.getColumn() == ColK) {
    if (changedRange.getRowIndex() >=triggerRow ) {
      var state = changedRange.getValue();
      var users = SpreadsheetApp.getActive().getSheetByName(triggerSheet).getRange(changedRange.getRow(),ColL);
      var times = SpreadsheetApp.getActive().getSheetByName(triggerSheet).getRange(changedRange.getRow(),ColM);
      var timestamp = new Date(); // Get the current time
      var originalUsers = users.getValue();
      var originalTimes = times.getValue();
      times.setValue(originalTimes+timestamp+",");
      users.setValue(originalUsers+Session.getActiveUser().getEmail()+",");
    }
  }
}

Upvotes: 1

Related Questions