How to automatically add timestamp in google spreadsheet

Question

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

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

Now I want that when cell 1 was supplied with data, the time stamp would automatically appear in cell 4. And when cell 2 and cell 3 were supplied with data, the time stamp would be the new value for cell 5.

My friend, give me the code to be inserted into the 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 inserted in =IF(B6="","",timestamp(B6)) cell 4, and this code =IF(D6="","",timestamp(C6&B6)) is in cell 5. in it An example tracker works. But when I copied it to mine, the output in cell 4 and cell 5 is the date today, not the time.

Can someone help me? why displays date and not time?

Answer 1

You can refer to 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 will probably help you.

Answer 2

I just ran into this problem and I changed the code provided by Internet Geeks .

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

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

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

Answer 3

I made a slightly different version, also based on code from online geeks

To support multiple named sheets, and also because Google Sheets Script does not currently support Array.prototype.includes (), I have included the polyfill mentioned here

In addition, in my version, timestamp marks the creation date of this line string, and not the date of the last update, as in other scripts presented here.

 // http://www.internetgeeks.org/tech/add-timestamp-time-stamp-google-docs-spreadsheet/ function onEdit(event) { var sheetNames = [ 'NEW Pounds £', 'NEW 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)); } } } // /questions/16202747/alternative-to-arrayincludes-in-google-apps-script/25098987#25098987 // https://tc39.imtqy.com/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; } }); }