Hey - Jared back again with another workflow idea for you!

I use Trello to manage most of my tasks.

There are a lot of tasks I want to run on a recurring basis (every week or month).

For example - maybe I want a Trello card to run a full backup of my computer on a monthly basis.

I could build a Zap for each Task using Schedule by Zapier - but that would create a lot of Zaps to manage - since each Task would need it’s own Zap.

Plus I want to be able to add and delete tasks from this list easily.

Google Sheets will enable me create tasks as a row on a Spreadsheet - but how do I get them to run automatically each Week or Month?

Make Your Sheet.

Step 1 is to make our Google Sheet.

I’ve shared a template here you can use to get started. :)

Just choose File > Make a Copy - and it will create a copy you can alter for your own Zap.

You’ll notice the regular fields you’d expect here when creating a Trello Card like Title, Description, Due Date, Time.

I’ll talk about the Trigger Date and Due Date in a bit.

First - I do want to point out that for many of the other Trello Fields like Member, Board and List - we need to send Trello ID values rather than Names for those fields.

To get those ID values for your Sheet - we can use the Create Card Action in Zapier to find the specific values we need.

For example below - we can see the Board ID value starring with “5f7737...” under the name for the “A new test board”.

We can find ID values for Members and Lists in a similar manner.

Likewise, if we have Custom Labels we want to use in the Zap - the Add Label to Card Action in Zapier can be used to get the IDs for Specific Custom Labels.

Trigger Date and Due Date Values

The most challenging part of our Sheets setup above are the Date Fields (Trigger Date and Due Date).

We want both of these Dates to be dynamic so that they trigger the zap at the correct time and add the correct due date.

To make the dates dynamic - we’ll use Formulas in the Sheet that Google will update on a regular basis as the date changes every day.

The Trigger Date is going to be a formula that will either trigger on the same date each month or same day each week.

To make this as easy as possible for you I’ve included Worksheets for Trigger Date Formulas and Due Date Formulas in the file shared above.

You can just copy/paste from those formulas into your Trigger Sheet for the dates you are wanting to use to Trigger the Zap (or for the Due Date).

Setting Up Our Zap

If you aren’t going to add any Custom Labels to your Zap - then this Zap really only needs 2 Steps.

The Trigger will be a New or Updated Spreadsheet Row in Google Sheets - where we choose our Trigger Sheet and use the Trigger Date as our Trigger Column.

And then we would map in all the fields from our Sheet to the appropriate fields in the Trello Create Card action in Step 2.

Those 2 Steps above are all we need to create the Card on a Recurring Basis.

Note: Please do not be concerned if you see messages that say there is a problem loading List or Member data. 

This is due to the values being dynamic from our Sheet and not being selected directly from the Dropdown in the Trello action.

As long as the ID values are correct on our Sheet - the Zap will work when it runs live.

How this Zap Works on the Sheet

We are using a New or Update Row Trigger on the Google Sheet - with the Trigger Date as the column we are looking at for “Updates”.

In the Trigger Date Column on the Sheet - we are using a Formula that will dynamically update each month or week based on the Formula we have put into the Trigger Date column.

So if we look at the Dates in the Trigger Date column below - we can see for my Monthly Recurring Card the Date is 12/1/2020 (the 1st of the Month).

When the Month Changes to January 2021 - that date will automatically update to 1/1/2021 - and trigger the Zap.

It’s the same for the Weekly Recurring Card. I have that card setup to trigger on Sunday every week (12/20/2020 was the most recent Sunday).

And on the next Sunday - the value in column B will update to 12/27/2020 - and the Zap will trigger for that Row.

Due Date’s can be a bit of a challenge to provide all possible options for.

For most of my Weekly cards that trigger on Sunday’s - I just add the number of days to the Trigger Date until I want them to be due.

For example below - in my “make plan for week” card - It triggers on Sunday each week and for the Due Date I am just adding 2 days to Sunday for it to be due. (=B3+2). That gives the card a Due Date of Tuesday at 8:00 AM (the time is in the Time Column).

The more complex formulas on the Due Date Formulas worksheet are better used for Monthly recurring cards.

Sheets Recalculation Setting

There is one Setting we want to adjust in the Sheet for this to work perfectly.

We would want to go to File > Spreadsheet Settings and then click the Calculation Tab.

Once there - we want to make sure the Recalculation setting is set to On change and every minute.

This will ensure that Google is regularly refreshing our Date values and the Zap triggers when the Trigger Dates are updated.

Adding Custom Labels

I use a lot of custom labels in Trello as well - and that’s why I’ve included 3 Columns on my Sheet for Label IDs.

If you want to add a Custom Label to the Card created in Step 2 of the Zap - we would need to add additional steps.

I always add at least 1 Custom Label - so immediately after my Create Card action - I have an Add Label to Card action at Step 3.

Here - we are using Custom Values for the Board and Card ID’s from Step 2 and then the Label ID from the Sheet.

Since I have 3 possible columns for Labels on the Sheet - I’ve added a Filter Step to make sure Label2 exists before adding the Label at Step 5.

And we can’t see it in the screenshot above - but there is another filter at Step 6 to make sure Label3 exists before trying to add the final Label at Step 7.

Final Considerations on the Sheet

Since this is a New or Updated Spreadsheet Row trigger - we always want to make sure the Zap is OFF before making any changes on our Trigger Sheet.

So if we want to add/delete/change tasks on the Sheet - we would always want to make sure to turn the Zap OFF first and then turn it back after the Sheet has been updated.

Summary

The Zap above can be used to create recurring Trello cards that recur on a Monthly or Weekly basis.

The key to the Zap are the Date Formulas we are using on the Sheet for the Trigger Date and Due Date columns.

They will dynamically update each month/week as the date changes. This in turn causes an update on the Sheet and the Zap to trigger.

While I’ve used Trello in the example above - this same Google Sheets setup would work to trigger any Zap on a recurring basis.

Do you have any other ideas for using this setup?

If so - please share them in the comments below. :)