Clash Royale CLAN TAG

Google Sheets Auto Date and Time Stamp on Multiple Columns

I want my Google Sheet to automatically do the following:

I have already added named ranges for Columns A through E as follows:

I found a code that lets me do the datestamp/timestamp thing, but it only works for one column (from https://www.internetgeeks.org/tech/add-timestamp-time-stamp-google-docs-spreadsheet/):

function onEdit(event)
{
var timezone = "PST";
var timestamp_format = "MM-dd-yy";
var updateColName = "Title";
var timeStampColName = "Date";
var sheet = event.source.getSheetByName('Sheet1');

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(timeStampColName);
var updateCol = headers[0].indexOf(updateColName);
updateCol = updateCol + 1;

if (dateCol > -1 && index > 1 && editColumn == updateCol) {
var cell = sheet.getRange(index, dateCol + 1);
var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
cell.setValue(date);
}
}


I tried to copy and paste this same code over and over again and just change the values of the updateColName, timeStampColName, and timestamp_format variables.

updateColName

timeStampColName

timestamp_format

And as I found out, you cannot just copy and paste multiple onEdit functions and expect it to run on the same document.

onEdit

I've been trying to find a solution to this for a while now. I feel like I would've been able to figure this out if I knew even a little bit about coding, but I don't.

Here is a link to my Google Sheet: https://docs.google.com/spreadsheets/d/1_dW8erkzVJFT6aUiB-0SikeMTbCnXmoo3d5mgUBN910/edit?usp=sharing

1 Answer
1

The main issue is that you need to have the function looking for edits in three places. Your current code is only looking for edits in your "Title" column. I've added code to look for the other locations.

function onEdit(event)
{
var timezone = "PST";
var datestamp_format = "MM-dd-yy";
var updateColName = "Title";
var dateStampColName = "Date";
var startEditColName = "TimeStart"; // edit column to signal time start
var startTimeColName = "TimeStart";
var stopEditColName = "TimeStop"; // edit column to signal time stop
var stopTimeColName = "TimeStop";
var sheet = event.source.getSheetByName('Sheet1');


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(dateStampColName);
var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
if (dateCol > -1 && index > 1 && editColumn == updateCol) {
var cell = sheet.getRange(index, dateCol + 1);
var date = Utilities.formatDate(new Date(), timezone, datestamp_format);
cell.setValue(date);
}

var startCol = headers[0].indexOf(startTimeColName);
var startEditCol = headers[0].indexOf(startEditColName); startEditCol = startEditCol+1;
if (startCol > -1 && index > 1 && editColumn == startEditCol) {
var cell1 = sheet.getRange(index, startCol + 1);
var date = new Date();
var startTime = date.toTimeString();
cell1.setValue(startTime);
}

var stopCol = headers[0].indexOf(stopTimeColName);
var stopEditCol = headers[0].indexOf(stopEditColName); stopEditCol = stopEditCol+1;
if (stopCol > -1 && index > 1 && editColumn == stopEditCol) {
var cell2 = sheet.getRange(index, stopCol + 1);
var date = new Date();
var stopTime = date.toTimeString();
cell2.setValue(stopTime);
}
}


I've also added this to your 'TimeStamp' script file as a separate script - Date_Timestamp.gs, so you can see the functionality.

This isn't pretty by any means, but it does what you asked. Let me know if you need more explanation or if I didn't answer your question.

startTime

stopTime

var startTime = (date.getHours() + ":" + date.getMinutes() + ":" + date.getSeconds());

var stopTime = (date.getHours() + ":" + date.getMinutes() + ":" + date.getSeconds());

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.