Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – The time from my google spreadsheet is being displayed wrongly in the sheet's web app, users see 1899 as year instead of just the time

I have this function that automatically assigns the time when cells are being filled up in certain rows. It works fine in the datasheet, but in the web app that I created for this sheet, VERY wrong dates are being displayed. The time is being read as December 30, 1899.

Here’s the code:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var column = range.getColumn();
  
     // Check if the edited cell is in column 2 (B), 5 (E), or 8 (H)
  if (column == 2 && sheet.getName() == "Join Queue [FOR ADVISEES]") { // Column 2
    var row = range.getRow();
    var dateCell = sheet.getRange(row, 1);
    if (dateCell.getValue() === "") {
      dateCell.setValue(new Date());
    }
      sheet.getRange(blankRow, 7).setValue(Session.getActiveUser().getEmail());

  } else if (column == 6 && sheet.getName() == "Join Queue [FOR ADVISEES]") { // Column 6
    var row = range.getRow();
    var timeCell = sheet.getRange(row, 9);
    if (timeCell.getValue() === "") {
      var currentTime = new Date();
      var formattedTime = Utilities.formatDate(currentTime, Session.getScriptTimeZone(), "HH:mm:ss");
      timeCell.setValue(formattedTime);
    }
  } else if (column == 8 && sheet.getName() == "Join Queue [FOR ADVISEES]") { // Column 8
    var row = range.getRow();
    var timeCell = sheet.getRange(row, 10);
    if (timeCell.getValue() === "") {
      var currentTime = new Date();
      var formattedTime = Utilities.formatDate(currentTime, Session.getScriptTimeZone(), "HH:mm:ss");
      timeCell.setValue(formattedTime);
    }
  }
}

And here’s how they are being shown in the web app (these are from the two else if statements btw):

enter image description here

Now, I noticed that the code in the main if statement is giving the correct date and time, here: so I tried to use the same code as that, and use that to the remaining columns.so the code became:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var column = range.getColumn();
  
  // Check if the edited cell is in column 2 (B), 5 (E), or 8 (H)
  if (sheet.getName() == "Join Queue [FOR ADVISEES]" && [2, 6, 8].includes(column)) {
    var row = range.getRow();
    var dateCell;
    
    if (column == 2) { // Column 2
      dateCell = sheet.getRange(row, 1);
      if (dateCell.getValue() === "") {
        dateCell.setValue(new Date());
      }
      sheet.getRange(row, 7).setValue(Session.getActiveUser().getEmail());
    } else if (column == 6) { // Column 6
      dateCell = sheet.getRange(row, 9);
      if (dateCell.getValue() === "") {
        dateCell.setValue(new Date()); // Set current date and time
      }
    } else if (column == 8) { // Column 8
      var statusCell = sheet.getRange(row, 8);
      if (statusCell.getValue() === "DONE") {
        dateCell = sheet.getRange(row, 10);
        if (dateCell.getValue() === "") {
          dateCell.setValue(new Date()); // Set current date and time
        }
      }
    }
  }
}

however, it did not came out like the first one, and only the year has changed. From 1899, it’s now giving 1970.

Tags:

2

Answers


  1. 0

    This is just my guess.

    Modification points:

    • In your showing script, the values of new Date() or formattedTime of HH:mm:ss are put into cells with setValues. In this case, I guess that those values are put into the cells as the date object and the cell values are showing as HH:mm:ss.
    • From It works fine in the datasheet, but in the web app that I created for this sheet, VERY wrong dates are being displayed. The time is being read as December 30, 1899., And here's how they are being shown in the web app (these are from the two else if statements btw): and your showing image, I’m worried that the script for creating the Web Apps might not be shown in your question. If you retrieve the cell values by getValues and you are using the retrieved values on the Javascript side of Web Apps, this might be the reason for your current issue in your showing image because the date object is directly used in the table cell value. In order to resolve this issue, I would like to propose modifying getValues() with getDisplayValues(). By this, the cell values are directly used as the retrieved values.

    Modified script:

    From the above points, about the script for creating your Web Apps or the script for creating the HTML table on Web Apps of your image, please modify as follows.

    From getValues() to getDisplayValues()

    and test it again.

    Note:

    Login or Signup to reply.
  2. 0

    You can simplify the code by figuring out what is common and what is not.

    function onEdit(e) {
  //e.source.toast("Entry");
  const sh = e.range.getSheet();  
  const col = [2,6,8];//column
  const off = [-1,3,2];//timecell offset
  const idx = col.indexOf(e.range.columnStart);
  if (~idx && sh.getName() == "Join Queue [FOR ADVISEES]") { 
    //e.source.toast("Gate1");
    var dateCell = e.range.offset(0,off[idx]);
    if (dateCell.getValue() === "") {
      dateCell.setValue(Utilities.formatDate(new Date(), e.source.getSpreadsheetTimeZone(), "HH:mm:ss"));
    }
      //sh.getRange(blankRow, 7).setValue(Session.getActiveUser().getEmail());blankrow is undefined
  } 
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search