Automatically obtain date and time when I type in a cell but in separate columns at google sheets

user_16716722 0
Viewed 70

I'm new around here, apologies in advance. I go with my question: I have a google spreadsheet with multiple cells and two sheets. What I am trying to do is that when I type a value in any cell in column 2, the time and date will automatically appear in the adjacent cell. I have gotten this code, which only worked for me on Sheet 1:

function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "HOJA 1-HF-SSB" ) { //checks that we're on the correct sheet
      var r = s.getActiveCell();
    if( r.getColumn() == 2 ) { //checks the column
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) //is empty?
        nextCell.setValue(new Date()).setNumberFormat("dd-MM-yyyy HH:mm");
    }
  }
}

Then I have managed to simplify it and adapt it to work on both sheets of the document:

function onEdit(e) {
var sheets = ["HOJA 1-HF-SSB", "HOJA 2-FM-DMR"];
if (sheets.indexOf(e.source.getActiveSheet()
    .getName()) === -1 || e.range.columnStart !== 2) return;
e.range.offset(0, 1)
    .setValue(new Date()).setNumberFormat("dd-MM-yyyy HH:mm");
}

My questions are: Is there a way to get the date and time to go in separate columns? That is, when I fill in column 2, the date is written in column 3 and the time in column 4?

My final goal, apart from separating the date and time, is to determine later (I don't know how to do it) and highlight which values ​​recorded in column 2 are duplicated on the same date, comparing them with the automatic dates in column 3, regardless of the time. Thank you and excuse me, I am a very newbie!

google-sheets datetime
1 Answers

Try (only for column #2)

function onEdit(e) {
  var sheets = ["HOJA 1-HF-SSB", "HOJA 2-FM-DMR"];
  if (sheets.indexOf(e.source.getActiveSheet().getName()) === -1 || e.range.getColumn() !== 2) return;
  var dateObj = new Date();
  var month = dateObj.getUTCMonth() + 1; //months from 1-12
  var day = dateObj.getUTCDate();
  var year = dateObj.getUTCFullYear();
  var newdate = year + "/" + month + "/" + day;
  e.range.offset(0, 1).setValue(newdate).setNumberFormat("dd-MM-yyyy");
  e.range.offset(0, 2).setValue(new Date()).setNumberFormat("HH:mm");
}

https://docs.google.com/spreadsheets/d/1NYxNfoPM6l_PJTPBDxEth2n-T_H0o03FG6C0OMDuJOo/copy

user_150193800
Related