OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Google Apps Script showing previous day

  • Thread starter Thread starter Karl S
  • Start date Start date
K

Karl S

Guest
I just ran this code and the problem seems to be fixed now.

When I look at a date on the spreadsheet, I see 11/1/2005. When I read it into google scripts, it changes to 10/31/2005.
I am in PDT, my spreadsheet is in PDT, (UTC-8).

Is there an easy way to read the date from the spreadsheet so it shows the same date as on the spreadsheet?

Maybe related to Question or Question

this is not working:

Code:
let date2 = Utilities.formatDate(date, Session.getScriptTimeZone(), "yyyy-MM-dd");

Here is as simple an example as I could think of:

Code:
function myFunction() {
  let data = SpreadsheetApp.getActive()
              .getSheetByName("Sheet1")
              .getDataRange()
              .getValues();
  let header = data[0]
  data.splice(0,1) // remove header
  data.forEach(row => {
    let date = row[0]
    let month = date.getMonth() + 1 // zero based month
    let day = date.getDate()
    let year = date.getFullYear()
    row[1] = month;
    row[2] = day;
    row[3] = year;
  })
  data.splice(0,0,header) // put header back
  SpreadsheetApp.getActive()
              .getSheetByName("Sheet1")
              .getDataRange()
              .setValues(data);  
}

Date                MM  DD  YYYY
11/1/2005 0:00:00   10  31  2005  <--  1 day behind
11/1/2007 0:00:00   10  31  2007
11/1/2009 0:00:00   10  31  2009
11/1/2011 0:00:00   10  31  2011
11/1/2015 0:00:00   10  31  2015
11/1/2017 0:00:00   10  31  2017
11/1/2019 0:00:00   10  31  2019
11/1/2021 0:00:00   10  31  2021

<p><strong>I just ran this code and the problem seems to be fixed now.</strong></p>
<p>When I look at a date on the spreadsheet, I see 11/1/2005.
When I read it into google scripts, it changes to 10/31/2005.<br />
I am in PDT, my spreadsheet is in PDT, (UTC-8).</p>
<p>Is there an easy way to read the date from the spreadsheet so it shows the same date as on the spreadsheet?</p>
<p>Maybe related to <a href="https://stackoverflow.com/questions...atdate-in-google-apps-script-are-1-day-behind">Question</a> or <a href="https://stackoverflow.com/questions/35488331/formatdate-gives-correct-date-1-day-google-apps-script">Question</a></p>
<p>this is not working:</p>
<pre><code>let date2 = Utilities.formatDate(date, Session.getScriptTimeZone(), "yyyy-MM-dd");
</code></pre>
<p>Here is as simple an example as I could think of:</p>
<pre><code>function myFunction() {
let data = SpreadsheetApp.getActive()
.getSheetByName("Sheet1")
.getDataRange()
.getValues();
let header = data[0]
data.splice(0,1) // remove header
data.forEach(row => {
let date = row[0]
let month = date.getMonth() + 1 // zero based month
let day = date.getDate()
let year = date.getFullYear()
row[1] = month;
row[2] = day;
row[3] = year;
})
data.splice(0,0,header) // put header back
SpreadsheetApp.getActive()
.getSheetByName("Sheet1")
.getDataRange()
.setValues(data);
}

Date MM DD YYYY
11/1/2005 0:00:00 10 31 2005 <-- 1 day behind
11/1/2007 0:00:00 10 31 2007
11/1/2009 0:00:00 10 31 2009
11/1/2011 0:00:00 10 31 2011
11/1/2015 0:00:00 10 31 2015
11/1/2017 0:00:00 10 31 2017
11/1/2019 0:00:00 10 31 2019
11/1/2021 0:00:00 10 31 2021
</code></pre>
 

Latest posts

Top