Recent Week's Data Missing in Script Output Despite Presence in Google Sheet

Other Questions Google Sheets
1

I’m having trouble with my Google Apps Script that pulls info from a Google Sheet. The script isn’t showing data from the past week even though it’s in the sheet.

Here’s what’s going on:

  • The sheet has all the latest info
  • My script finds 8 entries for a specific item in the last week but nothing after March 12th
  • Other older data shows up fine

I tried to fix it by adding this date filter:

let now = new Date();
let weekAgo = new Date(now - 7 * 24 * 60 * 60 * 1000);

let filteredDates = Object.keys(dataObject).filter(d => new Date(d) >= weekAgo).sort((a, b) => new Date(b) - new Date(a));

But it didn’t help. The script still misses data after March 12th.

My data looks like this:
TimeStamp | Area Kerja
21/03/2025 7:05:24 | WH 1 STORE

The dates are in DD/MM/YYYY HH:MM:SS format.

Any ideas why this is happening? How can I get my script to show all the data from the last week?

2

I’ve run into this exact problem before, and it can be super frustrating. One thing that helped me was to double-check the timezone settings in both my Google Sheet and my script. Sometimes, if they’re not synced up, it can cause weird date discrepancies.

Another trick I found useful was to use the Utilities.formatDate() function in Apps Script to standardize the date format. It helped eliminate any ambiguity in how dates were being interpreted.

Also, have you tried logging the data at different stages of your script? That might help pinpoint where exactly the recent data is getting filtered out.

Lastly, make sure your script isn’t hitting any quota limits or timing out before it can process the most recent entries. Sometimes adding a bit of delay or breaking the process into smaller chunks can help with that.

3

hey there, i had a similar issue recently. have u checked if the date format in ur sheet matches the one ur script expects? sometimes google sheets can be tricky with dates. maybe try converting the dates to a standard format before filtering. also, double-check if theres any hidden filters on ur sheet. good luck!

4

I encountered a comparable problem in one of my projects. The issue might lie in how your script interprets the date format. Consider parsing the date strings explicitly using a custom function. For instance:

function parseCustomDate(dateString) {
    const [date, time] = dateString.split(' ');
    const [day, month, year] = date.split('/');
    return new Date(year, month - 1, day);
}

Then, modify your filter logic to use this function:

let filteredDates = Object.keys(dataObject).filter(d => parseCustomDate(d) >= weekAgo).sort((a, b) => parseCustomDate(b) - parseCustomDate(a));

This approach should handle your specific date format correctly. Also, ensure your script has the necessary permissions to access the most recent data in the sheet.