Google-sheets – Script gives 1899 for the timestamp year. How to fix it

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

The following code was almost exactly what I was looking for to timestamp the adjacent cell after being edited. However, I need to incorporate the currnet date as well as time. The code below gives a date "1899".

What do I need to edit or is there a better code?

function onEdit() {
    var s = SpreadsheetApp.getActiveSheet();
    if ( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
        var r = s.getActiveCell();
        if ( r.getColumn() == 13 ) { //checks the column
            var nextCell = r.offset(0, 1);
            if ( nextCell.getValue() === '' ) //is empty?
                var time = new Date();
                time = Utilities.formatDate(time, "GMT", "HH:mm:ss");
                nextCell.setValue(time);
        };
    };
}

Best Answer

If you need to display date along with the timestamp, then look at this line:

time = Utilities.formatDate(time, "GMT", "HH:mm:ss");

You can set this to whatever you like, in the year case try this:

time = Utilities.formatDate(time, "GMT-08:00", "MM/DD/yy, hh:mm:ss");

If you are only concerned about displaying the year, then replace the MM/DD/yy info with just yyyy

Related Solutions

Google Sheets – Parsing Timestamp in Google Sheets with Script

The following piece of code will set the background color of the entire row:

function onFormSubmit(e) {
  // get data and make it a 1D array
  var time = e.range.getValues()[0];

  // get the minutes and hours of the timestamp (time[0]) 
  var minutes = time[0].getMinutes(), hours = time[0].getHours();

  // calculate the total amount of minutes
  var totalMin = (hours * 60) + minutes;

  // do the logic (thanks to geekzspot for the 1087)
  if(totalMin > 1087) {
    e.range.setBackgroundColor('yellow');
  } else {
    e.range.setBackgroundColor('red'); 
  }  
}

UPDATE
This code will set only the back ground color of the timestamp:

function onFormSubmit(e) {
  // get active spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();

  // get data and make it a 1D array
  var time = e.range.getValues()[0];

  // get row index
  var row = e.range.getRowIndex();

  // get the minutes and hours  
  var minutes = time[0].getMinutes(), hours = time[0].getHours();

  // calculate the total amount of minutes
  var totalMin = (hours * 60) + minutes;

  // set range to timestamp
  var range = sh.setActiveCell("A"+row);

  // do the logic (thanks to geekzspot for the 1087)
  if(totalMin > 1087) {
    range.setBackgroundColor('yellow');
  } else {
    range.setBackgroundColor('red'); 
  }  
}

Add this script, via the script editor, to your spreadsheet and set the trigger:
enter image description here

Here's the form: Test For Web Applications and here's the sheet

Google Sheets – Sending Email Notifications Based on Timeframe and Cell Data

Here's a pseudocode version of what he was saying:

What he wrote...

counter = 0

loop rows {
    if something {
        add to message
        increment counter
    }
}

if counter {
    send message to one person
}

What you need to write:

counter = 0

loop rows {
    if something {
        make message
        send message to person in row
    }
}

You will need to access the data from the email column as well. I'll leave the rest to you to work out, except to say that you should read this article on how to get the data out of your sheet efficiently.

Best Answer

Related Solutions

Google Sheets – Parsing Timestamp in Google Sheets with Script

Google Sheets – Sending Email Notifications Based on Timeframe and Cell Data

Related Topic