HI @Andrew_Luhhu! Thank you!
In case I'm not on vacation, it's less than 10. However, several times a year, there can be more. Sometimes there can be no overdue at all.
This feels like a job for Airtable to me. With their flexible "views," you can easily accomplish this!
@terbiy The most of the time this should work.
Build a zap that triggers daily.
Have the zap create the new worksheet.
Then you want a "Google Sheets -> Find Many Spreadsheet Rows"
Assuming your task list sheet has a date column, you can search by that with the today's date to bring back all (up to 10) the tasks due that day.
You can then use a "Google Sheets -> Create Spreadsheet Row(s)" step to write these rows to your new workbook (you'll need to map the new worksheet ID at this step).
Edit: This doesn't take care of the overdue tasks, though, we might need some more creative thinking for that.
Sounds good, @terbiy — let me know if you have a question about Airtable or if you'd like some additional detail. It should be able to accommodate everything you're trying to do. Cheers!
@Andrew_Luhhu, @TheDavidJohnson, thank you for the ideas! Still, I need some extra help.
The step that I can't overcome now is how to get all the tasks for today held in Todoist and paste them either in Google Sheets or Airtable?
Beforehand thank you!
@terbiy - yep you've hit upon one of Zapier's weaknesses - it doesn't really do "Find all" type scenarios.
My advice would be to add them to the Google Sheet as soon as they are created with their due date.
You can then remove them from the Google Sheet when they are marked complete.
The only scenario this doesn't cover is if the due date changes after the task is created. Does that happen in your use case?
@Andrew_Luhhu that's a good idea, if the tasks are added to Google Sheets as they are created then you know that each one will be there.
Airtable. Let us know what you think!
@Andrew_Luhhu's recommendation is definitely spot on.
I'm not sure how sophisticated we might want to get with this approach, but if you wanted to iterate through all the tasks that are not yet completed on a daily basis, then you could take this approach:
- As tasks are created, push them to Google Sheets or Airtable (as @Andrew_Luhhu suggested). Include a status column (completed='Y' or 'N', for example)
- When a task is completed (assuming that you're a Todoist Premium user with access to that trigger), the status could be updated via a Zap, as @Andrew_Luhhu pointed out.
- With another Zap (triggered on an interval basis, such as daily), run a "find" on each task shown as not yet complete in your Google Sheet (I'd still prefer Airtable for this, but we'll stick with Google Sheets for simplicity). With a little testing, you could discover exactly which data points the "Find Task" search step would return, but the hope would be that it would return the due date. If so, you could then update your Google Sheet with the new due date. (This might also be used to return the current "Complete" or "Incomplete" status from Todoist if you're not using Todoist Premium, but this would need to be tested as well.) To actually execute this step, you would need to use some version of this looping process to loop through all the tasks that are not yet showing as complete. It would begin by checking your Google Sheet for the number of rows to come up with a count, then perform the "Find Task" search once for each row. This is where Airtable would really shine (over Google Sheets), because it's super simple to set up a view in Airtable that only includes the tasks that are not yet complete. That would eliminate the need to move the tasks around from one column to another, etc.
- Once the last item has been returned from step 3, you could have your Zap trigger a notification to you to go check out the current list. This could be an email, a Slack notification, or whatever suits your fancy.
- If you used Airtable to manage your analysis ("... from the perspective of time, place, risk and its mitigation."), you could easily set up views to help you sort, filter, and prioritize based not only upon the "overdueness" (if you'll pardon the term), but through some sort of numeric or other score that you assign for each of the factors you're analyzing. It could keep that information in a single table for you so that you always had a nice view representing the hierarchy you prefer, and then you could adjust the values each day for each incomplete or overdue task as needed.
I'll admit the above is a little crazy to set up at first, but it could save a ton of time if I'm understanding the end goal correctly.
@Andrew_Luhhu, @Danvers, @TheDavidJohnson, first, thank you for your contributions and significant involvement!
I try to implement suggestions one by one. My ultimate aim is regular process improvement, so the automation becomes a little bit like a moving target. Here is what happened since my last post.
Following the @TheDavidJohnson recommendation, I've started using the Airtable service. The initial problem was the necessity to copy all tasks for a sheet dedicated to the current day by hand. In Airtable, I introduced the normalisation and moved all tasks descriptions to the other table. Due to the high number of recurring tasks, all I have to do now most of the time is to write several letters and choose a suggested option. To work only with the tasks for today, I use a filter and don't need to create a new sheet every day.
I feel that I've reached the significant improvement, but it looks even further from the possibility to automate!
Next, I'll respond to messages.
@Andrew_Luhhu, yes, I have due date changes appearing regularly. I also use a lot of recurring tasks, once completed, do the next occurrences count as created?
@Danvers, I've switched to Airtable, so there is no need for the Google Sheets practices improvement now. Thank you for your questions! I've described my new workflow based on the Airtable at the beginning of the reply.
@TheDavidJohnson, yes, these steps look indeed huge, but I'm using my process for several years, mostly every day, so the save is rather vivid. As I mentioned earlier, I've switched to Airtable, thank you so much for the recommendation! Also, thank you so much for the detailed instruction!
Here is the picture of my planning table now. The tasks are in Russian, but their content does not matter in this case. Task, Place, Risk and Risk mitigation columns are all links to other tables with corresponding names.
To enhance my current process as described above, I think I can make a Zap that will automatically add the description of the newly created tasks to the Tasks table, so I don't have to copy it manually. At the same time, I can't imagine the way to automate the creation of the planning table, but what I have now is much better than working with Google Sheets.
And yes, I have Todoist Premium subscription and we can rely on this.
Glad you're making progress @terbiy
As to your question to me - I'm not actually sure as I don't use Todoist very often. You may want to test or double check with Zapier support.
@terbiy Re: Todoist, if you are asking if recurring tasks still trigger your Zap, the answer is yes. Of course, if I'm misunderstanding the question, let me know and I'll see how else I can help out here!
@jesse, yes this is exactly what I was asking about! Thank you!
Fantastic, @terbiy! Your use of filters is exactly the direction I had imagined would be beneficial to you. I'm thrilled that you're seeing time savings already!
Feel free to reach back out if you hit any speed bumps with the further enhancements! I'll be excited to see where you go with this! 
I thought that I've almost done it to the solution, but stopped in an unexpected place.
When building action on "New Completed Task in Todoist" event, I need to enter the required Project field. But projects in my approach are very dynamic, now I have 54 of them, so it seems like an inconvenient way to handle new tasks this way: to create 54 zaps. Should I start a new thread for this issue or continue this one?
@terbiy - Any way you can create a lookup table for each project's ID and map it variably?
bouncing off of what @Andrew_Luhhu suggested if you don't want to edit the zap each time you make a project you could have a spreadsheet "lookup table" that maps project ID to whatever you need.
And you could even have a zap that triggers off of a new project and writes a line to that spreadsheet so there's no manual input needed.
@Andrew_Luhhu, thank you for your reply! Hm, I'm not aware of the mapping functionality in Zapier. Could you please provide a link to some materials to read more about it?
@terbiy - following on from what @PaulKortman said - there's a Lookup table function built into the Formatter by Zapier app (which you can learn about in the link he shared).
What @PaulKortman was suggesting is that, because you have a lot of projects, you could use Google Sheets to create a lookup table. Have the project name on in Column A, and the Project ID in Column B, then use a Google Sheets Find row step to get the project ID that matches the name of the project. Does that help?
@Andrew_Luhhu, @PaulKortman, @Danvers, thank you for the ideas. I've tried to introduce a look-up table, but seem to either get it wrong or not to be able to use it.
On the initial "When this happens..." step I need to specify what project to observe for the new incomplete tasks. And what I want here is to say that I wish this zap to get triggered on the new task in any project. But this option is not available, and I need to choose only one.
The look-up table tool from utils is only available on "Do this..." step so I can't utilize it. Did I get it right or there is something that I've missed?
Right now, it's not possible to set up the Todoist Completed task trigger without selecting a Project, sorry about that!
We do have an open feature request to allow folks to watch for completed tasks across all projects and I've added your vote for that addition. It's not possible for us to build it right now as the Todoist API (the bit of code that allows Zapier to 'talk' to them) requires a project to be specified when looking for completed tasks. If this changes in the future and we're able to make the change, we'll send you an email to let you know!
Hm, I know these kinds of things, I'm a front-end developer! I tried to use the ”*” symbol as done in Bash, and what's surprising, I had even received the successful test on it. Unfortunately this happened only once and saving failed on attempts to test Airtable.
I also experimented with possible predefined values like ”all”. I concatenated the ids of several projects and separated them with comma. I used multiselection in Todoist client to see how the address gets formed, but multiselection doesn't influence it.
Thank you for adding my vote, @Danvers! This feature would be great, indeed. Maybe I will still implement the trigger for several projects to try the overall solution.
