Any method to poll many sheets, without using a collector sheet which requires constant updating?
Each of my projects has a Task List. And I need to send open tasks to my users who have open tasks. I accomplish this with a collector sheet that uses COUNTIFS to look at each Task List and identify who has open tasks. That sheet then has workflow to send an email to each person with at least one open tasks. That email has a link to a report, which uses Current User filter to show the user's open tasks.
Two problems with this:
- Each time I create a new project, I need to add the project's COUNTIFS formulas to the collector sheet.
- Those COUNTIFS forumulas need to reference each user who might have an open task, so every time someone new comes onto a project, we need to make sure that they are added to the COUNTIFS.
The alternative to this is to have one big task list for all of our projects. But that has its own issues, like we need to create a bunch of filters and providing project-specific drop-down options becomes impossible. By this I mean that a Task List has a Category drop-down column, and those categories should be project specific, which I can do with one sheet per project, but when all projects are in one sheet, that drop down gets really busy really fast.
The only solution I have come up with, and I have not tested it yet, is to create a workflow in each project Task Sheet that pushes (copies) its open tasks to a holding sheet each day at a certain time. That holding sheet is used as the filter to identify who should get the "you have open tasks" email. But that collector sheet will need to be emptied or contain some control to not alert people of today's open tasks based on yesterday's (or last week's) data.
Is there any other way to collect information from all of those sheets in order to trigger the workflow rule?
(I really wish we could trigger workflow rules from Reports, that would solve all of my problems!)
Answers
-
Hi @James Keuning ,
Not sure if this works in your Smartsheet Design, but you can have a report that references the workspace as it's data source. This way will automatically add any new sheets that are moved into that workspace to be included within that report. From there you could add a filter based on sheet name containing "Task List" if your naming convention is standardized, or if there's a unique column in the Task Lists apart from your other sheets, you can build a filter on that. Hope that helps!
-
Thanks Chris. I do use a report, that is what my users see. The problem is that I need to send email alerts to anyone who has an open task on that report, but we can't alert from reports.
For now I run an automation to send every open task from every task sheet to one collector sheet, and I include the system Created Date. Then I have an automation that sends an alert to everyone who has an open task where today's date = TODAY. That automated email contains a link to the report.
Since the automation is part of the task list template, there is no setup work beyond using the template.
