CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Darkknight
Darkknight

Reputation: 1826

Log the timestamp in a cell when a another cell clicked on google sheet

I have a google sheet, one column (website) that has the link to the website(if you click it will go to the website). The second column (timestamp) needs to automatically log the time if the website cell is clicked. user may press it more than one time in that case it should save all the timestamps in a single cell. I'm trying to do with google script since I'm new to google script I'm not sure is this possible or not. Can anyone help me?

this is what I've done so far but this only works if the cell is edited and not able to add multiple timestamps in a single cell.

function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Sheet1" ) {
var r = s.getActiveCell();
if( r.getColumn() == 1 ) { 
var nextCell = r.offset(0, 1);
if( nextCell.getValue() === '' ) 
nextCell.setValue(new Date());
}
}
}

Upvotes: 0

Views: 363

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15308

Unfortunately, there is no way (at the moment) to create an onClick() function on a link for Google Spreadsheets. Neither onEdit nor onSelectionChange are appropriate here.

onSelectionChange could be used if you first click on the cell before clicking on the link.

for instance (put a trigger on the function)

function onSelectionChange() {
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName() == "Sheet1") {
    var r = s.getActiveCell();
    if (r.getColumn() == 1) {
      r.offset(0, 1).setValue(r.offset(0, 1).getValue()+'\n'+Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MMM dd yyyy hh:mm a").toString());
    }
  }
}

Upvotes: 2

Related Questions