CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Processit
Processit

Reputation: 55

insert date/time stamp and user name or email on google sheets method

I am new to google scripts app, but I would like to add to this script the following:

When an insert into "sheet B" is performed it also inserts the date/time (Now) in to column 4 and also any user detail into a cell column 5. So user name, email address, something that is user-related so I know who did it.

   function transferdata() {

var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
  '***          ATTENTION          ***', 
  'Do you want to "SAVE" this information on the "Sheet B" tab',
  ui.ButtonSet.YES_NO);
  if (result == ui.Button.YES) {

  var tab1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet A");

  var Name = tab1.getRange("B1").getValue();
  var Group = tab1.getRange("B3").getValue();
  var System = tab1.getRange("B5").getValue();


  var tab2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet B");
  var lr = tab2.getLastRow() + 1;

  tab2.getRange(lr, 1).setValue(Name);
  tab2.getRange(lr, 2).setValue(Group);
  tab2.getRange(lr, 3).setValue(System);


  tab1.getRange("B1").clearContent();
  tab1.getRange("B3").clearContent();
  tab1.getRange("B5").clearContent();


  ui.alert ('Information was successfully SAVED on the DATABASE tab');
   } else {
     ui.alert ('The information "NOT" has been saved!');

 }
}

Thanks

Upvotes: 0

Views: 1449

Answers (2)

ziganotschka
ziganotschka

Reputation: 26796

You can add the following code:

function onEdit(e) {
  var ss=SpreadsheetApp.getActiveSheet();
  if(ss.getName()=="sheet B"){
    var row=e.range.getRow();
    ss.getRange(row, 4).setValue(new Date());
        ss.getRange(row, 5).setValue(Session.getActiveUser().getEmail());
  }
}

But keep in mind that the username will be only retrieved for users of your domain.

Upvotes: 1

Sidney Cardoso dos Santos
Sidney Cardoso dos Santos

Reputation: 333

Change this part of your code

In column 1 will be the email and in column 2 the date

tab2.getRange(lr, 1).setValue([Session.getActiveUser().getEmail()]);
tab2.getRange(lr, 2).setValue(new Date());
tab2.getRange(lr, 3).setValue(Name);
tab2.getRange(lr, 4).setValue(Group);
tab2.getRange(lr, 5).setValue(System);

Upvotes: 0

Related Questions