Extract bundles from google sheets only if date included in a range indicated on another sheet

[clarisse_ink]

Hello Everyone,

I hope you can help me with my current issue. I am trying to extract bundles from a timesheet on googlesheet but only if the date of the line is between the invoice period established in another sheet.

the Date on the timesheet, the starting and finishing date of the period are in the same format already.

I tried to filter inside the “search Row” but it does not work

Could you help me understand what i have done wrong? I tested with no formatting of the date in the filter or with without success.

I am now stuck, all help will be appreciated for my first formal use of Make.

[damato]

Hi @clarisse_ink. Welcome to the community.

Dealing with dates on the Google Sheets app is a pain.

There are 2 things I would recommend you do in this case:

1. Get all the rows from the Timesheet in your 3rd module without any Filter and use the Filter on the link to the next module to apply your >= and <= criteria. The upside is that you’ll have full control over the columns (see #2 below). The downside is that debugging it is harder.
2. Only use parseDate() on both fields of the Filter, applying it to the value from Timesheet and from Invoice Data. What you get are only strings from a spreadsheet and that function will convert them to date objects, allowing you to use Date comparisons. formatDate() turns them into strings again, so don’t use it.

Hope that helps (and also that the Google Sheets app will be fixed someday).

@damato

[samliew]

Welcome to the Make community!

Change the filter operators to DATE (Earlier/Later than), not TIME!

You might not need to use any functions at all, since Make will try to coerce both text values into date type variables! If that doesn’t work, you can try just using the parseDate function.

You can also learn more about this in these Make Academy units:

Expanding my first scenario

• Adding a Router
• Adding a Filter
• More options with Filters
• Overview of scheduling
• Run once

An introduction to data types and data structures

• A brief introduction to Data Types
• How to map Data Items
• An introduction to Collections and Arrays

Hope this helps you move forward. Let me know if there are any further questions or issues.

— @samliew
_{P.S.: investing some effort into the tutorials in the Make Academy will save you lots of time and frustration using Make!}