How to automatically add a timestamp in google spreadsheet

Question

I have a sheet in my Google spreadsheet that contains 5 cells, the first 3 contains only words while the last 2 contains time, specifically a timestamp.

cell 1 = data
cell 2 = data
cell 3 = data
cell 4 = time start
cell 5 = time ended

Now, what I want is when cell 1 is supplied with data, a timestamp will automatically appear in cell 4. And when cell 2 and cell 3 is supplied with data, a timestamp will be the new value for cell 5.

My friend give me a code, that should pasted in Script editor:

function readRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

for (var i = 0; i <= numRows - 1; i++) {
  var row = values[i];
  Logger.log(row);
 }
};

And

function onOpen() {
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 var entries = [{
 name : "Read Data",
 functionName : "readRows"
}];
spreadsheet.addMenu("Script Center Menu", entries);
};
function timestamp() {
return new Date()
}

and this code is pasted in =IF(B6="","",timestamp(B6))cell 4 and this one =IF(D6="","",timestamp(C6&B6)) is on cell 5. in his example tracker its working. But when i copied it to mine, the output in cell 4 and cell 5 is the Date today and not the time.

can anyone help me? why does it output the date and not the time?

Answer 1

You can refer this tutorial, if this helps. In the script code, change

var timestamp_format = "MM-dd-yyyy"; // Timestamp Format.

to

var timestamp_format = "MM-dd-yyyy hh:mm:ss"; // Timestamp Format.

This should probably help you.

Answer 2

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 way 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.

Updated and simpler code

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  var sheets = ['Sheet1']; // Which sheets to run the code.

  // Columns with the data to be tracked. 1 = A, 2 = B...
  var ind = [1, 2, 3].indexOf(e.range.columnStart); 

  // Which columns to have the timestamp, related to the data cells.
  // Data in 1 (A) will have the timestamp in 4 (D)
  var stampCols = [4, 5, 6]

  if(sheets.indexOf(sh.getName()) == -1 || ind == -1) return;

  // Insert/Update the timestamp.
  var timestampCell = sh.getRange(e.range.rowStart, stampCols[ind]);
  timestampCell.setValue(typeof e.value == 'object' ? null : new Date());
}

Answer 3

I made a slightly different version, based also on the code from Internet Geeks

In order to support multiple named sheets, and because Google Sheets Script doesn't currently support Array.prototype.includes(), I included the polyfill mentioned here

Also, in my version, the timestamp marks the date of creation of that row's cell, not the date of the last update as in the other scripts provided here.

function onEdit(event) {
  var sheetNames = [
    'Pounds £', 
    'Euros €'
  ]
  var sheet = event.source.getActiveSheet();
  if (sheetNames.includes(sheet.getName())){
    var timezone = "GMT";
    var dateFormat = "MM/dd/yyyy";
    var updateColName = "Paid for ...";
    var dateColName = "Date";

    var actRng = sheet.getActiveRange();
    var editColumn = actRng.getColumn();
    var rowIndex = actRng.getRowIndex();
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
    var dateCol = headers[0].indexOf(dateColName) + 1;
    var updateCol = headers[0].indexOf(updateColName) + 1;
    var dateCell = sheet.getRange(rowIndex, dateCol);
    if (dateCol > 0 && rowIndex > 1 && editColumn == updateCol && dateCell.isBlank())
    {
      dateCell.setValue(Utilities.formatDate(new Date(), timezone, dateFormat));
    }
  }
}

// https://stackoverflow.com/a/51774307/349169
// https://tc39.github.io/ecma262/#sec-array.prototype.includes
if (!Array.prototype.includes) {
  Object.defineProperty(Array.prototype, 'includes', {
    value: function(searchElement, fromIndex) {

      if (this == null) {
        throw new TypeError('"this" is null or not defined');
      }

      // 1. Let O be ? ToObject(this value).
      var o = Object(this);

      // 2. Let len be ? ToLength(? Get(O, "length")).
      var len = o.length >>> 0;

      // 3. If len is 0, return false.
      if (len === 0) {
        return false;
      }

      // 4. Let n be ? ToInteger(fromIndex).
      //    (If fromIndex is undefined, this step produces the value 0.)
      var n = fromIndex | 0;

      // 5. If n ≥ 0, then
      //  a. Let k be n.
      // 6. Else n < 0,
      //  a. Let k be len + n.
      //  b. If k < 0, let k be 0.
      var k = Math.max(n >= 0 ? n : len - Math.abs(n), 0);

      function sameValueZero(x, y) {
        return x === y || (typeof x === 'number' && typeof y === 'number' && isNaN(x) && isNaN(y));
      }

      // 7. Repeat, while k < len
      while (k < len) {
        // a. Let elementK be the result of ? Get(O, ! ToString(k)).
        // b. If SameValueZero(searchElement, elementK) is true, return true.
        if (sameValueZero(o[k], searchElement)) {
          return true;
        }
        // c. Increase k by 1. 
        k++;
      }

      // 8. Return false
      return false;
    }
  });
}