With the way you’ve set your IF trigger - the tab with the calculations in need to be in the very left most tab; (Or alternatively add [Tab Name]! before the F2 cell reference.
Yep, it’s the left most tab. But I will try the [Tab Name] in the formula, worth a try.
If you change the date in B2 to the day before your last salary, does cell D2 update?
Yep.
in your spreadsheet settings - is your “Location” Set to UK?
Yep, that’s caught me out a few times. Why there is no global setting in Drive I’ll never know!
Thank you for the suggestions so far. Applet failed multiple times and turned off overnight again last night.
I did the simple A1 - update cell test and the Applet showed as failed the moment it was created. But changing the value cause the trigger to fire and money was moved based on the cell value.
I’m leaning toward IFTTT having an integration issue with Google Sheets, especially as it fails at creation regardless of Sheet contents or location.
The test sheet using the original payday sweep formula is failing every minute without fail. But this time the sheet settings are set to only update on change, and not time-based.
And as far as I know, Google Sheet formulas only action on change, and don’t ‘look’ for changes at timed intervals.
Right I have a couple of theories which I think there may be solutions for. Will update shortly but…
Possibilities:
Immediate Failure on creating -> Caused by a Blank Cell - a non value breaks the integration.
Multiple repeated failures - updates to the sheet cause this cell to update, even if it updates to the same value - causing failures of the trigger
Repeated failures when a “0” is present -> this time the Action doesn’t work as it can’t work with Zero values. This is what causes the applet to disconnect.
First bullet is solved by changing the formula to not display blank, but only Zero (but then causes Bullet 3).
Second bullet may be solved by switching on “change only” calculation, but will still on all other updates, EXCEPT when you pay arrives, so same problem.
Bullet 3 will have the same problem as 2, I think.
I’ve just made a 2nd integration that “adds a new row to a spreadsheet” with the same trigger - so if this one works and the other doesn’t, I at least can see why.
In terms of a solution - I think some Filter code will solve problem 3. I’ll come back with an update in a couple of hours!
One change I did make was to the “Action Amount” format. Instead of it displaying blank when no amount, I set it to show “0.00” and ensured format was “Number” and not automatic. That was around 9.18… so could be as simple as a formatting issue after all this.
My two test applets I just made stopped failing every minute after I changed the formula to display “0” if no value present.
I think this has the possibility to fail imeddiately after the action changes (i.e. it’s swept away your money and goes back to Zero), but if it’s not repetive failure that should be OK.
Edit: Yep, that should fix it, the change back to zero will cause a fail, but hopefully it should only do it once:
Brilliant! I’ll keep an eye on it and see how it performs. It seems to have settled nicely now. Thank you for your time looking at this, really appreciated.
I’m super late to the party with this but finally I have time to start playing.
I’ve started by importing everything from the locked original sheet and I’ve done some basic formulas. Now I’m onto the more advanced stuff which I need some help with please
I want to have a sheet that shows all my direct debits so I can total them. So…
Date | Name | Amount
So my formula needs to…
Grab column A, D & G (date, name, amount)
From my ‘Monzo transactions copy sheet’
And select all rows where column C (type) equals ‘Direct Debit’
And month, from column A (date) is equal to current Month
I think I’ve gone too advanced to quick though so that’s why I’m stuck
@ordog - here’s an example with “QUERY” - it’s a bit of a beast. Maybe someone can do something simpler.
You need two cells to help with the date - set up your sheet like this:
In cell A4, enter use the following:
=QUERY('Monzo Transactions'!A:P,"select B,E,H where (D = 'Direct Debit') and B < date '"&text(B2,"yyyy-mm-dd")&"' and B >= date '"&text(A2,"yyyy-mm-dd")&"'",1)
Change ‘Monzo Transactions’ for whatever your import sheet is called.
And just check the columns you’re after - in my sheet it is B/E/H (Date, Name, Amount) and D (Transaction Type).
If yours differ change to the appropriate column.
This Query search is using Google’s QUERY API, and is basically telling the spreadsheet to return (Select) the values found in columns B E and H, if (where) Column D = Direct Debit, and the date range is within the date ranges specified.
Dates need to be formatted in a certain way to work, hence why it looks messy
In your sheet, you want to put these formula into cell A2:
Woah that’s awesome thank you so much for taking the time to do this for me!
As you said I just needed to change some of the columns but it’s spot on.
I’ve now taken this and managed to pull out all of last months direct debits too by using
First day of last month =EOMONTH(TODAY(),-2)+1
And last day of last month =EOMONTH(TODAY(),-1)
I’m going to carry on messing now
Edit: Last months direct debits included Paypal payments which aren’t technically direct debits so I wanted to exclude those. This was done by modifying the statement with the following:
where (C = 'Direct Debit' and D != 'Paypal')
I’m really getting into this now. I’ve never known how much as a bare minimum I need per month to live so I’m looking forward to working all this out and more…
Honestly the QUERY tool is so powerful - takes some learning everytime you may use it - but it can do so much.
You can use it to sum all of your common merchants, if you wanted, and lots of other things - count the number of transactions at Merchant, and total the spend.
You can change the order of the data too, just by changing the order of the column refs.
There’s a great page here by Google with some examples:
So far so good. I think I’m picking it up quite well but it’s going to take a few months of refining as there are no doubt things that I will have forgotten about.
After I’ve got various sheets pulling in the raw data I need, I’ll then be looking at charts and things to visually represent my spending better, compare them month on month, see where I can save, predict if I’m on track for my targets and so on… all in a fancy dashboard.
Lastly I’ll be creating some IFTTT triggers to move my money around automatically once I trust everything is working as it should.
