Its great that theres an applet to let Monzo write to a spreadsheet via IFTTT. It could be super powerful. However, it writes all of the monetary values with leading currency markers meaning that its formatted as text and no calculations can be done on the cells making it essentially useless as a feature and you might as well be writing to a word document.
Can this be fixed please?
You can still sum them, they aren’t formatted as text.
Ive just tried a simple sum function on a column and it doesnt see any of the entries as numerical because they are text. You can see they are text because theyre left aligned by default and numerical values are aligned to the right. Google sheets seems to read it as text if the currency marker is entered into the cell rather than being added by means of cell formatting.
Loads of people have been doing sums. Here is just one topic full of examples: IFTTT + Google Sheets + Monzo = Then what?
Sounds like you’re doing something wrong rather than the feature being useless.
Ive been using spreadsheets for 20 years and the ‘=sum’ function has always worked on a column of numerical values. You can see the formula i entered in the sheet right there. Im not sure what i could be doing wrong. Its almost definitely because its text. If i remove the leading £ symbol from the cell it works fine.
The screenshot shows that the intended formula you’ve entered “=sum(E2:E18)” has a result of “£0.00”, but I get it to considerably more than that from just looking at the 16 values visible. Unless the 17th value, not visible in the screenshot, is equal to a negative figure to cancel out the sum of E2:E17?
There are no negative numbers in the column as Monzo doesnt seem to record any money deposited into the account in the spreadsheet.
Heres a new screenshot showing the whole column contents
That “£0.00” suggested sum is an alarm-bell to a potential error. Formatting?
I’m no expert, but it looks to me like the IFTTT applet that is taking the Monzo transaction and writing that to the Google Sheets document is at fault here? Have you checked the format of the output? I’m guessing you’re using that and it’s firing on every card transaction you make, and not the Monzo Plus full export, which works perfectly.
As i say the formatting is always going to be text because the leading currency symbol has been entered into the cell rather than added by the cell formatting, so it will recognise the cell as mixed characters or ‘text’. You can tell its text as its left aligned automatically. As soon as i manually remove a symbol im able to format the cells as currency which will right align them by default, the symbol reappears as formatting and the formula counts that cell.
@tbutz Its the IFTTT applet yes but its developed by Monzo, this isnt the monzo export youre correct.
I assume the answer will be no, but you can use ISNUMBER to check how Google Sheets is interpreting the value and whether it is actually a number.
You’ve got a few options here. The easiest but most expensive is to get Plus. The live Google Sheets export does this job so much better than the IFTTT version and it includes all account activity.
If you don’t want Plus you can use IMPORTRANGE to pull the data into a second sheet.
In that second sheet, add a new column to the right of your existing data, then use REPLACE to drop the currency symbol completely or SPLIT if you’d like the symbol to stay in a separate column. Then if you’re still having issues you can use VALUE to convert the text to a number.
That should hopefully do what you need.
EDIT: I’ve just gone back to check my old IFTTT connection (I’ve turned it off now I use Plus) and it is possible to SUM values. The reason why you’ve not been able to do this is because you haven’t set up IFTTT correctly. Go back to IFTTT, edit your applet and use the ‘Formatted Row’ option to select the row format you need in your spreadsheet. This is how I had mine set up and values were inserted correctly.
If you’re still having issues after that then highlight the entire column and make sure the column is formatted as currency.
Thanks for the suggestion but this isnt my applet, its the one monzo have published and it doesnt give the option to edit any of the row. https://ifttt.com/applets/xfRVshTw
The solution would be to ‘roll my own’ as you have done but my initial complaint was that their premade applet doesnt actually create much useful data.
The initial applet works fine, it’s not all the info but you can certainly sum it.
What have you changed?
Again i havent changed anything. If you check the link you can see that you just enable the applet, give your spreadsheet a name and thats it.
The screenshots show that it definitely does not work. Its a brand new spreadsheet and using the ISNUMBER formula above it shows ‘FALSE’ when checking if the cell contains a numerical value.
I’ve used the sheet/applet a lot, I know how it works.
This is what it looks like on mine. The £ symbol is there but it’s still a number that can be summed.
Heres how it looks on mine.
Can you format the whole column?
I can yeah but it still doesnt see it as a number because of the symbol 
Got it. I think that’s unfortunately one of the drawbacks you’re going to face now that IFTTT have free and pro tiers.
I’ve just switched this applet on and I’m going to let a little data populate into my sheet so that I can see exactly what you’re seeing. Back soon!
Is there any chance this is linked to the default spreadsheet settings Sheets?
There’s a “locale” setting that defaults to US. I think I had a problem with this affecting currencies too.
