Google Sheets – Auto Update Cells with Timestamp

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

I'm new to writing/using scripts. I want to use something to allow for multiple cells to be updated and tracked for the last time they were modified. I've tried various different scripts on here but so far I'm struggling!! I'd really appreciate some help/guidance on how to say if cell M / N / O are modified then the last date that happened ends up in V / W / X.

Best Answer

try like this:

function onEdit(e) {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Sheet3" ) { 
var r = s.getActiveCell();
if( r.getColumn() == 13 ) { 
  var nextCell = r.offset(0, 9);
  var newDate = Utilities.formatDate(new Date(), 
  "GMT+8", "MM/dd/yyyy hh:mm:ss");
  nextCell.setValue(newDate);
}
if( r.getColumn() == 14 ) { 
  var nextCell = r.offset(0, 9);
  var newDate1 = Utilities.formatDate(new Date(), 
  "GMT+8", "MM/dd/yyyy hh:mm:ss");
  nextCell.setValue(newDate1);
}
if( r.getColumn() == 15 ) { 
  var nextCell = r.offset(0, 9);
  var newDate1 = Utilities.formatDate(new Date(), 
  "GMT+8", "MM/dd/yyyy hh:mm:ss");
  nextCell.setValue(newDate1);
}
}
}

"Sheet3" = sheet name
r.getColumn() == 13 = column M / 13th column
r.offset(0, 9) = offset timestamp 9 columns to the right on the same row eg column V
"GMT+8" = timezone
"MM/dd/yyyy hh:mm:ss" = date and time format

Related Solutions

Google-sheets – Conditional formatting based on cell values in same column

Assuming the actual finishing date is in Column F and the sheet is called Sheet1, paste the code below inside your spreadsheet under Tools >> Script editor, then hit Save.

/***
* Color Code+ 
* Conditional formatting with cell referencing for Google Spreadsheets
* Support: scripts@bryanp.com
**/

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Sheet1');
var values1Rule1 = s.getRange('E1:E').getValues();
var values2Rule1 = s.getRange('F1:F').getValues();
var range3Rule1 = s.getRange('E1:E');
var color1 = 'red';

for (var row in values1Rule1) {
for (var col in values1Rule1[row]) {
if (values1Rule1[row][col] == values2Rule1[row][col]) s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor(color1);
else s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor('white'); }}
};

I used this script generator to make creating the code easier. You can adapt it to your needs.

Google-sheets – Add a ‘creation date’ value when a new line is edited

You could add a trigger that runs when new rows are added to the spreadsheet:

function onModification(event) {
  var sheet = event.source.getActiveSheet();
  if (event.changeType == "EDIT") { // A cell is edited. Other values include "INSERT_ROW" (when a new row is inserted)
    fillEmptyRows(sheet);
  }
}

function fillEmptyRows(sheet) {
    var range = sheet.getRange(1, 1, sheet.getMaxRows(), 1);
    var firstColumnValues = range.getValues();
    for (var i = 0; i < firstColumnValues.length; i++) {
      if (firstColumnValues[i][0] == "") { // Column 0 is A
         firstColumnValues[i][0] = new Date();
      }
    }
    range.setValues(firstColumnValues);
}

You need to register this trigger manually: Click Resources -> Triggers -> Add new trigger, select the onModification method and select on modification from the right-most dropdown menu.

When a cell is edited, the script will fill all blank cells in column A with the current timestamp.

I have set up an example spreadsheet, feel free to play with it.

Best Answer

Related Solutions

Google-sheets – Conditional formatting based on cell values in same column

Google-sheets – Add a ‘creation date’ value when a new line is edited

Related Topic