Get Sheets rows that match Email AND Date

[gmsalomao2]

I want to retrieve all the rows from a Google Sheets file, but only if the columns match these conditions:

• Email matches the ones I got in a previous node
• Type = “Treino feito”
• Date is in the last 30 days

But I’m dealing with two problems:

1. When I add another column to the filter in Sheets node, it stops filtering the Emails. It returns every email.
2. I don’t know how to filter the Dates in the Sheets node. Only with a Filter node, after I’ve retrieved all the rows I don’t need.

Right now I’m solving this by filtering later. But I’d love if I could request only what I need.

I’ve also tried to create a JSON list of dates with a JavaScript node, but without success. I would use the dates to filter in the Sheets node and then filter the type and emails later. At least it would return less data.

[MutedJam]

1. When I add another column to the filter in Sheets node, it stops filtering the Emails. It returns every email.
2. I don’t know how to filter the Dates in the Sheets node. Only with a Filter node, after I’ve retrieved all the rows I don’t need.

Hi @gmsalomao2, I suspect you will have to filter via a separate node here. The reason for this is that the filters defined in the Google Sheets node will be combined using OR rather than AND which seems to be what you have in mind. Imagine the following Google Sheets spreadsheet:

Now a filter such as Email = [email protected], ID = 6 would return all rows where either of these conditions is true:

The IF/Filter node on the other hand will allow you to combine multiple conditions using AND. So I think your current approach of combining the two nodes (first filtering the email in the Google Sheets node, then filtering your date in the Filter node) will already be the most suitable one considering these constraints.

For more advanced filtering of your data, you could consider using a full SQL database instead of Google Sheets. This would give you much more flexible query options and allow you to retrieve only the data you really need.

[gmsalomao2]

Thank you for the explanation.
Then I think the best solution for now would be to filter the last 30 days of data.

I could do that by generating a list of the dates and filtering with the Sheets node. But I wasn’t able to generate it correctly.

Do you know what is wrong with my code?

[gmsalomao2]

Solved it with ChatGPT.
It have me this code to create a JSON output with the dates I needed:

// Get last 7 days
function getDateXDaysAgo(daysAgo) {
  const currentDate = new Date();
  const pastDate = new Date(currentDate);
  pastDate.setDate(currentDate.getDate() - daysAgo);
  return pastDate.toISOString().split('T')[0];
}

// Create array of objects
const outputArray = [];
for (let i = 1; i <= 7; i++) {
  outputArray.push({
    index: i,
    data: getDateXDaysAgo(i)
  });
}

// Return the array as output
return outputArray;

Now I filter using the dates, which gives me much less data to work with and consumes less resources.