Google Sheets/Apps Script to automatically add a datestamp to a sheet when values are pulled in

Question

Hello I have a google sheet with some Apps script that copies the values at the end of the day into another sheet and adds a datestamp to keep a log of the values, however it only adds a datestamp to a single row when the data I'm copying needs to have it across all rows (about 30) (plan to have it display on a chart at some point if I can get it to look decent). The work around I came up with was too add a different script that would add a datestamp to rows with data in Column B I was not able to find any sample code for such a script though (probobly just searched the wrong term) so tried using an onEdit script but that only triggers when a "user" edits a cell and does not trigger when my other script pastes in values.

So I"m looking for a way to do one of two things either edit my first script so when copying over values it adds a datestamp to column A for every row copied or an onEdit type trigger that works with data being imported.

The script I'm using to copy my values:

function dailyLog() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('Debug2');
  var logSheet = ss.getSheetByName('Log');
  var lastRow = logSheet.getLastRow();
  logSheet.getRange(lastRow + 1, 1).setValue(new Date());  // insert timestamp
  var range = sourceSheet.getDataRange();
  range.copyTo(logSheet.getRange(lastRow + 1, 2), {contentsOnly: true});
}

The script using onEdit that is not working for my needs:

function onEdit(event) {
  var eventRange = event.range;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('Log');  
  if (eventRange.getColumn() == 2) { // 2 == column B
    var columnARange = sourceSheet.getRange(eventRange.getRow(), 1, eventRange.getNumRows(), 1);
    var values = columnARange.getValues();
    for (var i = 0; i < values.length; i++) {
       values[i][0] = new Date();
    }
    columnARange.setValues(values);
  }
}

If requested I can put together a sample sheet.

thanks, Jason

Cooper Cooper · Accepted Answer · 2019-11-21T21:11:06

Try this for DailyLog:

This assumes that the srcsh does not have a header row.

function dailyLog() {
  var ss=SpreadsheetApp.getActive();
  var srcsh=ss.getSheetByName('Debug2');
  var logsh=ss.getSheetByName('Log');
  var rg=srcsh.getDataRange();//if you have header rows this needs to change
  var vA=rg.getValues();
  vA.forEach(function(r){
    r.unshift(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss"));
    logsh.appendRow(r);
  });  
}

I think what you trying to do with the onEdit() is to go in and add the dates that you weren't getting in the daily log function but now that they're there you no longer need the onEdit().

Google Sheets/Apps Script to automatically add a datestamp to a sheet when values are pulled in

2 Answers