This article teaches how to masterfully navigate “Monday.com, Make (Integromat) and Google Sheets” to carry out complex calculations and manipulate data. While going through this piece, you’ll pick up on how to:
- Use Google sheets for IntegraMAT scenarios to perform sophisticated calculations.
- Adjust and fine-tune due dates based on task frequency types and reference dates.
- Set up, filter and update your Monday.com boards using IntegraMAT.
- Automate updates and calculations to run at specified schedules.
- Derive and adjust due dates, accounting for weekends and holidays.
Knowledge from this article can be applied to manage tasks more effectively, ensuring tasks are done right on schedule!
Monday.com, Make (Integromat) and Google Sheets
Hi, I’m Jim, the money man. Today I’m going to show you how to use Google Sheets in your Make (Integromat) scenarios to do complex calculations. Let’s get started. So this is the board we’re going to use as an example. Basically what I want is for items that have their status done, I want to recalculate the due date. And that’s going to be according to the frequency type and then the reference date. This n value is to further define how this is, how the calculations are going to work. So on a monthly, the reference date gives me the day of the month. On the nth day, it gives me the day of the week. And then this tells me which, in this case, Thursday. So this will be the third Thursday of the month. This will be, the due date will be calculated. All right, so let’s take a look at the spreadsheet that we’re going to use. So in the spreadsheet, what we’re going to do is this is going to be our input values. We’re going to put those here. The calculations are here. We have different calculations based on the frequency type. This reference day is pulling in from the data. And this day of the week is calculated based on the reference date. We were pulling in the n and using it in these calculations. Basically, the fundamental piece is that we are calculating the next due date. And then the adjusted due is going to push out to the next working day. So if this is falling on a weekend or a holiday, which we have our holidays defined here. So if this falls on a weekend or a holiday, then the adjusted date is going to push that out to the next workday.
So let’s take a look at how this is going to work in Make (Integromat). So we’re going to add our Monday modules. And we’re going to read the boards items. So list boards items right here. We’re going to do the calculations. And then we’re going to go ahead and set this. The board is going to be the recurring tasks board. And we’re going to leave this as it is. So it will output all of the values on the board. OK. The next one we’re going to do is the update. And we’re going to use that module right there. Pull that down. And we’ll set this. The board ID is going to be the same board. The item ID we’re going to map from the previous module. And we want this ID right here. We’re going to set these. But I’m going to get the calculations first. So we’ll just click OK for now. Now we’re going to add in our Google Sheet modules. So the first thing we want to do is update a row to output our setting values. And then we’re going to read in that particular cell that has the result. So let’s go in here. We’re going to select the spreadsheet and the recurring task calculations. And the sheet we want is the due date counts. And we’re going to be updating row number two. So the last due date is going to be the due date that’s currently there. And the date frequency type is going to be here. Take the text value there. Put this down. The reference date, colon, is right there. And then n, not always used, of course. But if it’s there, that’s where we’re going to be. OK, so the result and the return value, we’re going to leave blank. OK. And then on this get cell, we’re going to grab the calculated value. So we’re going to use the same spreadsheet and sheet, of course. And then the cell we want is F1. Align that. Great. So now we’re going to, in the update, we’re going to pull in the date, the new calculated due date. And that is going to be equal to the return value from Google Sheets. OK. And then we also want to reset the status column. So we’re going to change the status. We’re going to use a label. And we want it to be not struggled. OK. So that’s the basics of that. We do need to put a filter on this, because we don’t want to update every item. We only want to update those that have a status of done text. We’ll change this to case insensitive and done. And we also want to make sure that the frequency type is valid. So we’re going to just say it exists. So that’s good. All right. Now, one thing I also want to include here is if it’s a new value. We want or. So or the status is equal to not started. And we want to use our frequency type exists again. And we also want to make sure that this has not already been set up. So we don’t want a due date. So the due date does not exist. Now, because of the way Monday works, we also have a condition where, depending on how the new item was set up, it may be that this is actually null. So the other condition we want to check for is if the status does not exist. And you can’t really tell this from the board, but from experience, that is something you want to check for. And then we’re basically going to use these same conditions. So we’re going to do and frequency type exists. And our date has not been set. So due date does not exist. OK. So that has the filter. This has been set up. So everything is good there. Let’s take a look back on our board. We should expect that these two will be calculated. This is a weekly occurring on Friday. So this should be, let’s see, what is the next Friday coming up is the 26th. So that should be calculated the 26th. This is monthly on the 15th. So that should be December 15th. Nth day, this is Thursday, the third Thursday. And it’s already been calculated. Thursday, the third Thursday. And it’s already occurred this month. So the next one is going to be the third Thursday is the 16th of December. So that’s what we should expect. Let’s give it a run and see what happens.
OK, so we’ve got our three different lines that we wanted. Come back to our board and see, did we get the result? Yep, we reset all the statuses. We did calculate the due dates. This is on the 19th. This is December 15th and December 16th. So very good. OK, so the way we want this to run, we don’t have any daily calculations here. So we don’t need this to run every day. But what we do is in the scheduling, we can set this to run. And we have a number of different choices here. But I’m going to have this run on Sundays. So we’ll set this to Sunday. And then we’ll run it at noon. So that’s good. And OK. So now that we have that set up, we can just turn this on. And it will run every Sunday at noon. That’s it. All right, so of course, we need to save our scenario. I want to go back to the spreadsheet and talk a little bit about how this calculation works, in case you’re interested in that. These are the formulas that calculate the inf day for this month. This one is for next month. And then this one is a comparator to figure out which one we want. And then this is the adjustment at the next workday. OK, and then we are in our result. We’re looking up, based on the frequency type, we’re looking up which formula we want to use and pulling that result in. I use Excel for a lot of things. But I use Google Sheets specifically in Make (Integromat) scenarios, where I want to return calculations immediately, because it tends to work more consistently quicker. So even if you’re an Excel person, I would highly recommend that you use Google Sheets when you’re doing these types of calculations.