I am using Google Apps Script to retrieve and display data from Google Sheets. However, the data for the last 7 days is not appearing in my output, even though it is available in the sheet.

What I Have Tried:

Verified that the latest data is correctly updated in the Google Sheet.

Checked the script and confirmed that it retrieves 8 entries for OPE MH150406-8 (No.05)- in the last 7 days, but none appear after March 12th.

Other data is being retrieved correctly, except for the missing dates.

Used the following date filtering logic to ensure only the last 7 days are considered:

var today = new Date();
var sevenDaysAgo = new Date();
sevenDaysAgo.setDate(today.getDate() - 7);

var dates = Object.keys(recordsMap)
  .filter(date => new Date(date) >= sevenDaysAgo) // Filter only last 7 days
  .sort((a, b) => new Date(b) - new Date(a)); // Sort from newest to oldest

Below is the relevant part of my code:

var recordsMap = {};
for (var i = 0; i < result.data.length; i++) {
  var rec = result.data[i];
  var date = new Date(rec.timestamp);
  if (isNaN(date.getTime())) continue;

  var formattedDate = date.toISOString().split("T")[0];
  var hours = date.getHours() + (date.getMinutes() / 60);
  var shift = (hours >= 22.75 || hours < 7.01) ? "Shift 1" : (hours < 15.41) ? "Shift 2" : "Shift 3";

  if (!recordsMap[formattedDate]) recordsMap[formattedDate] = { "Shift 1": {}, "Shift 2": {}, "Shift 3": {} };
  recordsMap[formattedDate][shift] = rec;

Despite adding this filtering, the result remains the same.

Expected Behavior:

• The script should retrieve and display all records from the last 7 days, including entries after March 12th.

• The data should be filtered and sorted correctly.

• Observed Behavior:

Data for the last 7 days does not appear after March 12th, even though it exists in the sheet.

Sample Data Format:

TimeStamp	Area Kerja
21/03/2025 7:05:24	WH 1 STORE

I am using Google Apps Script to retrieve and display data from Google Sheets. However, the data for the last 7 days is not appearing in my output, even though it is available in the sheet.

What I Have Tried:

Verified that the latest data is correctly updated in the Google Sheet.

Checked the script and confirmed that it retrieves 8 entries for OPE MH150406-8 (No.05)- in the last 7 days, but none appear after March 12th.

Other data is being retrieved correctly, except for the missing dates.

Used the following date filtering logic to ensure only the last 7 days are considered:

var today = new Date();
var sevenDaysAgo = new Date();
sevenDaysAgo.setDate(today.getDate() - 7);

var dates = Object.keys(recordsMap)
  .filter(date => new Date(date) >= sevenDaysAgo) // Filter only last 7 days
  .sort((a, b) => new Date(b) - new Date(a)); // Sort from newest to oldest

Below is the relevant part of my code:

var recordsMap = {};
for (var i = 0; i < result.data.length; i++) {
  var rec = result.data[i];
  var date = new Date(rec.timestamp);
  if (isNaN(date.getTime())) continue;

  var formattedDate = date.toISOString().split("T")[0];
  var hours = date.getHours() + (date.getMinutes() / 60);
  var shift = (hours >= 22.75 || hours < 7.01) ? "Shift 1" : (hours < 15.41) ? "Shift 2" : "Shift 3";

  if (!recordsMap[formattedDate]) recordsMap[formattedDate] = { "Shift 1": {}, "Shift 2": {}, "Shift 3": {} };
  recordsMap[formattedDate][shift] = rec;

Despite adding this filtering, the result remains the same.

Expected Behavior:

• The script should retrieve and display all records from the last 7 days, including entries after March 12th.

• The data should be filtered and sorted correctly.

• Observed Behavior:

Data for the last 7 days does not appear after March 12th, even though it exists in the sheet.

Sample Data Format:

TimeStamp	Area Kerja
21/03/2025 7:05:24	WH 1 STORE

...

Additional Information:

The timestamps are stored in the format DD/MM/YYYY HH:MM:SS.
The missing data is located in the Store1, WH2, WEST sheet.

Link to view the issue (data after March 12th missing): text

lINK Spreadsheet : text

Question:

1. What could be causing this issue where data after March 12th is not appearing?
2. How can I correctly retrieve and display the last 7 days of data?

• javascript
• datetime
• google-sheets
• google-apps-script

Share Improve this question Follow edited Mar 24 at 15:17 Wicket 38.7k99 gold badges8080 silver badges195195 bronze badges asked Mar 24 at 5:56 Muhammad AffanMuhammad Affan 122 bronze badges 2

• 8 entries for OPE MH150406-8 (No.05) appear on sheet:"Store1" of the spreadsheet but the code has no obvious link to this sheet; values for result.data and recordsMap are generated by a doGet and it is not clear where/how filters are executed and/or displayed. The referenced code is part of a webapp. I recall this question asked recently; this version is not an improvement. Please read How do I ask a good question? and How to create a Minimal, Reproducible Example. – Tedinoz Commented Mar 24 at 6:58
• you might try comparing the Date.valueOf() rather than the date – Cooper Commented Mar 24 at 15:10

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

The primary issue is how the code handles comparisons and arithmetic operations with dates.

Instead of

new Date(date) >= sevenDaysAgo

the comparison expression should be

new Date(date).getTime() >= sevenDaysAgo.getTime()

and instead of

new Date(b) - new Date(a)

The arithmetic expression should be

new Date(b).getTime() - new Date(a).getTime()

Related

• Compare two dates with JavaScript
• How to sort an object array by date property?