Google Sheets getDay() Shows an Incorrect Value

user_18611600 0
Viewed 90

Date() shows the correct date while getDay() shows an incorrect value.

function Test()
{
  const date = new Date();
  const dateToday = Utilities.formatDate(new Date(), "GMT+8", "MM/dd/YYYY");
  const dateYesterday = Utilities.formatDate(new Date(new Date().setDate(date.getDate() - 1)), "GMT+8", "MM/dd/YYYY");
  var day = date.getDay();
  var a = SpreadsheetApp.getActiveSheet().getRange(3, 2);

  switch (day)
  {
  case 1:
    day = "Sunday";
    break;
  case 2:
    day = "Monday";
    break;
  case 3:
     day = "Tuesday";
    break;
  case 4:
    day = "Wednesday";
    break;
  case 5:
    day = "Thursday";
    break;
  case 6:
    day = "Friday";
    break;
  case 7:
    day = "Saturday";
  }

  a.setValue("Today is " + day + " " + dateToday + ". Yesterday was " + dateYesterday);
}

When this code was executed in Google Sheet, it will output below: Today is Tuesday 04/21/2022. Yesterday was 04/20/2022

The dates are correct while the day shows Tuesday which is incorrect. It's Thursday right now.

google-apps-script google-sheets
4 Answers

The getDay() method returns the day of the week for the specified date according to local time, where 0 represents Sunday.

Sunday is 0, Monday is 1, etc.

docs

And example with a less length implementation: 

const getDayofWeek = x => x === 0 ? 'Sunday' : x === 1 ? 'Monday' : x === 2 ? 'Tuesday' : x === 3 ? 'Wednesday' : x === 4 ? 'Thursday' : x === 5 ? 'Friday' : x === 6 ? 'Saturday' : "Invalid Day";
for (i = 0; i < 8; i++) {
  console.log(getDayofWeek(i))
}

user_118659560

Try it this way:

function Test() {
  const dt = new Date();
  const dateToday = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy");
  const dateYesterday = Utilities.formatDate(new Date(dt.getFullYear(), dt.getMonth(), dt.getDate() - 1), Session.getScriptTimeZone(), "MM/dd/yyyy");
  var d = dt.getDay();
  var a = SpreadsheetApp.getActiveSheet().getRange(3, 2);
  var day;
  switch (d) {
    case 0:
      day = "Sunday";
      break;
    case 1:
      day = "Monday";
      break;
    case 2:
      day = "Tuesday";
      break;
    case 3:
      day = "Wednesday";
      break;
    case 4:
      day = "Thursday";
      break;
    case 5:
      day = "Friday";
      break;
    case 6:
      day = "Saturday";
  }
  Logger.log("Today is " + day + " " + dateToday + ". Yesterday was " + dateYesterday)
  a.setValue("Today is " + day + " " + dateToday + ". Yesterday was " + dateYesterday);

}
user_72150910

Try

function test() {
  const dayOfTheWeek = ["Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"]
  const date = new Date();
  console.log(dayOfTheWeek[date.getDay()])
}


  const dayOfTheWeek = ["Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"]
  const date = new Date();
  console.log(dayOfTheWeek[date.getDay()])

The most important thing is to check your timezone in your script editor : go to your script editor, ckick on the gear on the left hand side, check the third box, go back to script editor and review in appsscript.json, hange timezone according to your locale

user_150193800

As another approach, in your situation, how about the following modification?

Modified script:

function sample() {
  const date = new Date();
  const dateToday = Utilities.formatDate(new Date(), "GMT+8", "MM/dd/YYYY");
  const dateYesterday = Utilities.formatDate(new Date(new Date().setDate(date.getDate() - 1)), "GMT+8", "MM/dd/YYYY");
  const day = Utilities.formatDate(new Date(), "GMT+8", "EEEE"); // Added
  const a = SpreadsheetApp.getActiveSheet().getRange(3, 2);
  a.setValue("Today is " + day + " " + dateToday + ". Yesterday was " + dateYesterday);
}
  • In this modification, the day name is retrieved using Utilities.formatDate like Utilities.formatDate(new Date(), "GMT+8", "EEEE").

Reference:

user_71086530
Related