Best answer

How to move existing tasks from Todoist to Google Sheets?

  • 1 October 2019
  • 32 replies
  • 2626 views

Userlevel 3

Hello!

I use Todoist almost every day, and in the mornings I transfer all the tasks manually to Google Sheet to analyze the possibility of their accomplishment from the perspective of time, place, risk and its mitigation.

What I want Zapier to do is to:

  1. Create a new sheet in specific workbook (done now).
  2. Move all the overdue and today tasks into the rows of the first column starting from the second one: one for one row.

My search through the available functionality wasn't successful. If you have any ideas, this would be great to find them out!


icon

Best answer by terbiy 25 October 2019, 09:45

View original

32 replies

Userlevel 7
Badge +12

@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!


Userlevel 5

@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:

  1. 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)
  2. 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.
  3. 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.
  4. 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.
  5. 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.


Userlevel 7
Badge +10

@terbiy checkout https://zapier.com/help/create/format/create-lookup-tables-in-zaps for what @Andrew_Luhhu was talking about.


Userlevel 7
Badge +9

@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!


Userlevel 7
Badge +12

@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?


Userlevel 7
Badge +12

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!


Userlevel 5

This feels like a job for Airtable to me. With their flexible "views," you can easily accomplish this!


Userlevel 5

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!


Userlevel 5

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! 😁


Userlevel 5

Interesting. I'm just now catching up on all of this and it seems like there are a few ideas worth pursuing. The one thing I'm wondering, @terbiy is this: when the "Project" changes, is that something that causes a task to be "updated" as well?

My thinking runs along these lines: If you're updating Airtable when a task gets "Updated," then you might be able to cause the latest "Project" name to be written to Airtable. This way, you'll have that data handy when you need to use it in a Zap like the one where you got stuck.

I hope this makes sense! It might be possible that I'm missing something here, but it seems feasible that something along these lines might be possible.


Userlevel 7
Badge +10

Hi



Userlevel 7
Badge +10

@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.


Userlevel 7
Badge +10

@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.

Screen Shot 2019-10-09 at 10.06.53.pngYou 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?


Userlevel 7
Badge +10

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.


Userlevel 7
Badge +10

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.


Userlevel 7
Badge +10

@terbiy I've run into similar situations before where you have to specify some bit of information in the trigger step.

I've found a workaround that isn't ideal but still keeps the automation in place. I split the zap into two zaps, one which does all the work (Zap 😎, and the other (Zap A) which is the trigger on each project. You would still need to copy Zap A for each project, but if you needed to change anything in the process it would only be one zap (Zap 😎 that would need to be changed.


Zap A

  • Trigger is the Todoist Completion (pic a project, copy this zap for each project, only the project setting will need to change)
  • Action Step: Webhook, choose a custom Webhook, like so and then set up the Data Pass Through to Yes like so

##Zap B

  • Trigger is a webhook - use the URL from this trigger in the Action Step in Zap A
  • Do whatever it is you want to do with this information... aka copy it to sheets etc.


You will end up with as many Zap A's has you have projects, and the only thing that changes is the project. You will have only one Zap B as all Zap A's call Zap B with the data they were given. Your Zap B does all the heavy work.


Userlevel 7
Badge +10

@terbiy feel free to mention me here or send a dm if you need additional assistance. Also let us know if it worked


Userlevel 3

Thank you,



Userlevel 3

Thank you,



Userlevel 3

@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.

Screen Shot 2019-10-11 at 08.57.34.pngTo 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.


Userlevel 3

@PaulKortman, thank you for the indeed great suggestion! I've created a project zap and the main action zap to give it a try. This way looks promising.


Userlevel 3

@TheDavidJohnson, thank you for your reply! I'll answer it a little bit later!🙂


Userlevel 7
Badge +10

@terbiy - Any way you can create a lookup table for each project's ID and map it variably?


Userlevel 3

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.


Userlevel 3

@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!


Reply