Report for files not modified in last 45 days?
Is it possible to create a report to list all files (sheets) in a folder not modified in the last 45 days?
To be clear: I know how to run a report that shows the ROWS modified within the sheets. I want to know when the SHEETS themselves were last modified.
Background: Currently I have a folder with hundred of sheets. They are supposed to be accessed monthly but I suspect many are not. Now I sort the folder by "modified" now to see what is out of date. It's an OK workaround but I'd love a report...
Thoughts?
Comments
-
Hi Mark,
Interesting!
What do you mean with when the sheets themselves were last modified? Do you want the date when it was last viewed, or it's ok with the last modified date on the sheet?
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
The best suggestion I can make requires some extra work on the front end, but when set will work consistently and can be put into place on new sheets rather easily.
Add in a helper column (checkbox will be fine). Use a basic IF statement to check the box next to the most recently modified row.
=IF(Modified@row = MAX(Modified:Modified), 1)
You can then set up your report to look at the entire workspace and use the following criteria:
1. The box in the helper column is checked.
2. Modified is in the last (days) - 45 then check the box to exclude the selected items.
.
The checkbox ensures you only get one entry per sheet instead of seeing every single row on every single sheet that hasn't been modified in the past 45 days.
A few things to keep in mind...
When you put the column into the sheets, it will show that all rows have been updated on the day you added the column. This essentially resets what will be pulled in your report (you won't see your first results until day 46 after your first sheet is updated with the new column).
If there are any TODAY() functions within the sheet, it only needs to be opened and saved then closed out again without any edits actually being made. This means someone could just open the sheet without looking at it, and it won't be pulled in your report.
Cell linking and form entries also update the TODAY() function without the sheet even having to be opened, so if either of those two could apply to a sheet, it won't be pulled for your report so long as one of those is updated every 45 days. The sheet wouldn't even have to be opened at all.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Paul,
I was thinking the same, but I suspect that Mark wants to know when the sheet was accessed, and I think the API is needed for that.
Nicely done regardless!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I re-read the post, and the more I think about it, the more I agree.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives