Filter Google Sheet - Date Range - Chart

Queries and Resources
1

Please help to filter the google sheet data {{ query.data }} which provides array of objects , need to filter this by date range

i am adding a date range filter to chart to display customer and amount received within the date range.

sample data received from - {{ query.data }} is below i want only Grand total of PartyName for date range ...

[{"Date":"06/01/2022","Time":"11:14:41","PartyName":"A.R. CONTRCTOR","DriverName":"SHIV PRAKASH","Kms":"35","Ltrs":"7","FuelRate":"100","DieselCash":"50","DieselOnline":"50","TollCash":"100","Fastag":"100","DriverBatha1":"100","DriverBatha2":"100","HamaliParty":"100","TotalCash":"450","TotalOnline":"150","GrandTotal":"600"},{"Date":"06/02/2022","Time":"11:17:13","PartyName":"KRISHNA GAS","DriverName":"MANOJ","Kms":"","Ltrs":"7","FuelRate":"100","DieselCash":"500","DieselOnline":"200","TollCash":"400","Fastag":"200","DriverBatha1":"250","DriverBatha2":"100","HamaliParty":"300","TotalCash":"1550","TotalOnline":"400","GrandTotal":"1950"},{"Date":"06/04/2022","Time":"11:21:50","PartyName":"ABHI GAS AGENCY","DriverName":"MANOJ","Kms":"30","Ltrs":"6","FuelRate":"100","DieselCash":"300","DieselOnline":"300","TollCash":"200","Fastag":"200","DriverBatha1":"100","DriverBatha2":"200","HamaliParty":"300","TotalCash":"1100","TotalOnline":"500","GrandTotal":"1600"},{"Date":"06/10/2022","Time":"11:37:55","PartyName":"ABHI GAS AGENCY","DriverName":"SHIV PRAKASH","Kms":"30","Ltrs":"6","FuelRate":"100","DieselCash":"100","DieselOnline":"100","TollCash":"100","Fastag":"100","DriverBatha1":"100","DriverBatha2":"100","HamaliParty":"100","TotalCash":"500","TotalOnline":"200","GrandTotal":"700"},{"Date":"06/15/2022","Time":"11:40:55","PartyName":"A.R. CONTRCTOR","DriverName":"MANOJ","Kms":"35","Ltrs":"7","FuelRate":"100","DieselCash":"500","DieselOnline":"200","TollCash":"500","Fastag":"200","DriverBatha1":"1000","DriverBatha2":"1000","HamaliParty":"500","TotalCash":"3500","TotalOnline":"400","GrandTotal":"3900"}]

2

Hi @codenayak ,

according to your data structure I'd decorate the objects with a transformer adding a js date, such as:

data.forEach(d => (d.jsdate = new Date(d.Date + ' ' + d.Time)))
return data

This way you can create another transformer to apply a filter, so you can use it for your chart:

return {{query1.data.filter(d => {
  const start = new Date(dateRange1.value.start).getTime()
  const end = new Date(dateRange1.value.end).getTime()
  const t = new Date(d.jsdate).getTime()
  return t >= start && t <= end
})}}

Hope this help

3

And how do you make this transformer filter the records from the last 30 days?

4

Hi @emieldc, With the example shared above, the data is filtered by a dateRange component which would allow you to select the dates you wanted to filter by in the app! If you wanted to set them to be the last 30 days, you could do something like this:\

const dates = {{ query1.data }};
const today = moment().startOf('day');
    
return dates.filter(x => {
  let date = moment(x.jsdate);
  return today.diff(date, 'days') >= 0 && today.diff(date, 'days') <= 30;
})

How you implement moment.diff() might need to change depending on how you want to define the last 30 days.