Wrote to Homey Support, and there is (for now) no way of having a “;” in the text without splitting it into different cells. FYI.
I would like to use this to make some kind of a smart screen. And for doing this easy i would like it to post the value in the same cell everytime. Is is possible to get it to overwrite?
I have a question regarding the way Homey appends the data to Google Sheet.
Because I want to have multiple values in one line, I use a comma to separate those values:
To get those values in separate columns, I use the SPLIT function in another tab:
However, every time there’s a new entry, somehow a whole row with formula’s is deleted. You can see this in the picture below. Instead of reading the values A6 and B6 (after A5 and B5 as shown in the picture above), it reads the values A7 and B7. This happens every time and because of it, I cannot see new data (in this case the data of May 19th) splitted up into columns:
My question: how did you solve this issue, or is this a bug in the app that needs to be fixed?
use semi- colon ( ; ) not comma ( , ) as a fieldsepparator
Hi @JPe4619,
Thanks for your answer. I immediately tested it, I’m now able to receive the data without needing to split it into columns, which is great.
But unfortunately, all references to these fields are still adjusted/deleted when a new entry is listed, as shown in my example in my previous message.
don’t know if it is possible to use formulas like this, don’t even understand them, I store my values directly and that works for me.
Well, these were just to split the values, which I don’t need anymore. The IF statement is only there to hide 0.00 numbers when there is no data yet.
But next to these, I have very basic formula’s that calculate the daily usage by subtracting yesterdays value from todays. But if you put them in a separate tab with a reference to the fields where the data will be appended to, these formula’s get changed/deleted somehow. I don’t understand why this is, because they’re in another tab, so I would assume it should not be affected.
I presume you also make calculations based on the values you append to your sheet? How have you made these calculations? Maybe I’m just doing something wrong here.
sorry, no calculations in the sheets. just use it for logging, formatting I do inside Homey.
I have found a workaround by importing it from another spreadsheet with the function IMPORTRANGE.
However, I’m still interested if others have found a solution, or if it could get fixed in the app itself.
Probably a n00b question but I haven’t been able to fix it so here goes
My dates/times are saved in Sheets like this “Jun 17, 2023 2:16:40 PM”.
Homey is set to (location find automatically); Timezone Europe/Stockholm and I get 24h time in the “about” section of Homey.
In Sheets, I’ve tried going to custom date/time settings - but it won’t convert to 24h clock. I’ve tried converting the cell but for some reason it won’t work. Sheets is set to Sweden under file/settings.
Is the app using a non standard date format? I’d like to be able to set it to YYMMDD HH:mm:ss
Possible?
Ps. Also, when going in to see if there are any settings in the app on Homey I just get “Something went wrong, please try again. TypeError: Cannot convert null value to object”
With BLL app you can format the time&date just how you like it. Maybe it’s usable
This is the same as the time function in expressions.
(Known) bug. For now, just use the settings in the web app my.homey.app
It’s in the search function’s 1st result btw
For date/time, use the date formatter tho, not the time, time is for durations
Wrong screenshot indeed, but I’m not gonna explain how it all works
Not sure why Peter’s even replying if not to help. So Arie, please point me in the direction of the “date formatter”. I thought Homeys user base was mostly based in countries using 24h clocks, so it’s weird that its even set to 12h to begin with.
I’ve tried setting BLL Locale/Timezone to Sweden. Still getting AM/PM.
Yeah sorry, Google Sheets doesn’t support BLL coding (yet ), so changing those options only have effect on BLL supported apps.
Could you share what and how you are exporting data to Google Sheets?
Because, with the limited knowlage i have right now, all i can think of, is that you add an extra column and add the datetime to it.
What you could do, is add the following flowcard, and include the resulting tag/value into the GoogleSheet when you append data:
These settings are for when you create your own Excel file through a flowcard of the BLL app, not for usage in Google Sheets
Or when you use the Excel generater in an app, like the Simple (Sys) Log app, or BLL itself.
Thanks for your reply. Good to know about BLL.
I’m just using the Append to document with timestamp.
I’m looking into triggering a timestamp via Sheets API but the less work the better… : )
Well, if you get the date from the dateformatter card, its considert a text, not a date, and you could just add it in those append cards. You would need to use AdvancedFlows tho, because you need to use the tag/token.
There was a real simple solution
Only downside is that the time tag doesn’t contain seconds, but it’s ok considering the application.
Well, you can still use a tag from the BLL app to include seconds and to have the format just how you want it.
Just place this card between your 2 card, and use the resulting tag:
But i guess this might be fine also