Hi,
In googlesheets we have a date column with dates (MM/dd/yyyy) in string format.
I need to apply filter such that dates are less than or equal to 7 days from now.
Can anyone help please.
Hi
You want to apply filter directly in Google sheets or once after reading the data apply filter in that datatable
@Palaniyappan is it possible to directly apply the filters in google sheets?
Or With datatable how to filter?
Can you please guide.
This is possible be reading the Google sheet with the
And to get the filtered datatable
Cheers @Deepa_Madkar
Hi,
I have some dates like in “sales date” column
09/13/2021
09/14/2021
09/15/2021
09/16/2021
09/17/2021
09/20/2021
09/21/2021
I want dates which are less than or equal to 7 days from now.
In the above case it will be 16 sept and above.
So I used one assign activity like below:
NewDate = DateTime.Today.AddDays(-7)
Then I used Filter datatable activity and tried to filter like below:
“sales date” >= NewDate
But executing above function is not giving me any result.
Can you please help? @Palaniyappan
@Deepa_Madkar : when you check > New Date, it will check against the result of New date (which will be [09/23/2021 00:00:00]) as you are assigning the NewDate as DateTime.
While checking > have NewDate.ToString(“MM/dd/yyyy”). it should work. I have attached workflow for you, hope it will be useful. Let me know if that works. TestSequance_Deepa.xaml (8.5 KB)
Hi,
Thanks for your help.
But is it possible to only get those dates which are less than or equal to 7 days.
I mean instead of write line activity which activity can be used to show only datarows satisfying the condition
@Pradeep.Robot
Yes, you can replace it with <= condition instead of > to adjust your changes. I have simply put writeline activity to print the dates which are > 7 (i think thats what you have asked in the question earlier). If you need to do any business logics with that condition, you can put that instead of write line. i am not sure of the logic on what you have to do with the dates <= 7, and if you want to just print the items less than 7, you can print the row item in the else part of my code.
it would be great if you could specify on what logic you want to put in the If Else condition. so that i can modify accordingly.
Basically I want to filter google sheet with records having date less than equal to 7 from current date. Attaching excel sheet for reference
Example.xlsx (6.2 KB)
The sales date column should be filtered and records having dates less than or equal to 7 days from today should be displayed.
I tried but not displaying any data
@Pradeep.Robot
Ok, I tried to extract with your sheet. Most of the fields are coming differently (the values of dates are not correct all 09/13/2021 is been showed as 00/13/2021). It is working for me with the normal excel sheet, In this case - i think you may try to use Gsuite Activities instead of Read range or excel activities. Try to import Gsuite activities and let me know how it works…
As @Palaniyappan mentioned, the below link will be helpful for using the google sheets.
https://docs.uipath.com/activities/docs/google-sheets
I tried with Gsuite activities. I used the read range and tried to apply filter on the datatable obtained from read range activity. It’s not giving me result.
I am really stuck
I understand, I am sharing the project which i have followed with Excel read range activity for you. May be this can help… Let me know if this works Deepa_GoogleSheets.zip (57.3 KB)
Hi @Pradeep.Robot
Thank you for taking your time out to help.
But unfortunately it’s still not working.
No data is getting reflected in the Output sheet
Hey, Sorry got busy with works. Why there is no data in the output sheet? you should be getting the below screenshot in your output with my code i believe.
Issue here is, the input sheet you have provided (Google sheet) is reading the date values as 00/13/2021. So it couldn’t meet the condition. unfortunately, i couldnt install Gsuite in my machine due to security. My advice is to include the Gsuite and read the values with that and you can use the same code logic to extract the information <= 7 days from current day.
If we can able to pass the right date values there, then it should be solved. Let me know.
I assume you are able to get data from sheet to datatable. Use following steps on the input datatable:
- Create datatable and clone original datatable
- Add loop on datatable
3 Add If statement and define condition as:
DateTime.ParseExact(row(“sales date”).ToString,“MM/dd/yyyy”,system.Globalization.CultureInfo.InvariantCulture)>=DateTime.ParseExact(DateTime.Now.AddDays(-7).ToString,“MM/dd/yyyy”,system.Globalization.CultureInfo.InvariantCulture)
`
4. In True condition Invoke method ‘Import Row’ and assign object as cloned datatable
I hope it will help you.