Let me see what I can do here.
1 Like
@DirkG I assume you know that the setCell card is very easy use. Month-nr would resolve column, the day-nbr in month would address the row. So a very simple formula would produce exact what cell to set based on the date
But then I would have to create a separate setCell card for each individual cell, right?
Not at all.
This simple script will create the cell you need:
let date = new Date();
let sheet = ‘Ark 8’;
// 65 = ‘A’
let column = String.fromCharCode(65 + date.getMonth()+1);
let day = date.getDate();
let cellNbr = sheet + “!”+ column + day;
return cellNbr;
Put it into the flow with Homey Script, and you are done
The problem I have with scripts is, that I don’t have absolutely no script skills. So I’m not able to adjust or repair such a script and I’m dependent on other users.
Even with this small script, I’m already overwhelmed and don’t know which parts to replace, sorry.
It shouldn’t really be very hard.
I have created a page with very simple and described code here : calculateCellforDate.js - Homey Easy Logger - Confluence
1 Like
Thank you for your support. With the code on the linked page I managed to use the script. However, the value is inserted in the wrong place within the table:
These are the data that I have used:
let sheetName = '2023';
let startingColumnChar = 'B';
let startingRowNbr = 2;
Is there a mistake?
Yes, a mistake. Done by me. Was a little quick at work to reach my train to home. Try the updated code from calculateCellforDate.js - Homey Easy Logger - Confluence
I think that will do. Tested with your input
@DirkG And in your case you can use this line
let sheetName = ‘’ + timeOfJustNow.getFullYear();
and this script will work forever 
No problem, and no hurry!
It’s getting better, but not quite correct yet… 
Now that I know where the first mistake was, I updated the code myself:
let dayInMonth = timeOfJustNow.getDate()-1;
And yes, it works. But will it work also at the first day of a month? And what is exactly the problem? Maybe the timezone of the file? The time zone for Germany (GMT + 01:00) is set.
How is this supposed to work? Do I need to create a file with multiple spreadsheets named by year?
Like this?
The 2024 is not needed before we get to next year, but even then, the script does not need to be changed. Just be shure to create the 2024 sheet before 1.1.2024 …
Often you need to add or subtract 1 when handling dates. Some field starts on 0 other on 1. Just try to add or subtract 1.
Regarding timezones, yes, it migth be important if your script is running just a few minutes past midnight. I will provide a test for you later.
1 Like
@DirkG I have updated the homepage with som additions you can use to verify that time is absolutely correct. Just copy it into a homey script and execute.
1 Like
I have absolutely no idea what the script does, but it works.
Thanks a lot!
Edit
@bwa, the shift to the new month also worked flawlessly.
Thank you very much!
Cheers 
@DirkG Niice… Looks like 2024 is already ready 
As long as you update the sheet name of a year exists it will produce forever
If you change the sheetName line to this :
let sheetName = ‘something-’ + timeOfJustNow.getFullYear();
You will get a more usable tab name in the sheet, it’s more easy to add different content in same spreadsheet as a tab name of ‘2023’ doesn’t tell so much
A sample here :
1 Like
I will update the sheet name, it’s the easiest und understandable way for me to do this.
Because I have absolutely no clue what…
let sheetName = ‘something-’ + timeOfJustNow.getFullYear();
does and how to use it.
Homey script and all the other programming languages are black boxes for me. That’s the reason why I want to keep it as simple as possible.
This line of code will put a prefix your sheetname with the text you put instead of something.
What I could see is that you created a sheet with name of ‘2023’. This is very general, so if you would like to have a more descriptive sheet(tab)name, such as MyCost-2023 for this year and MyCost-2024 for next year, then you would change the line to
let sheetName = ‘MyCost-’ + timeOfJustNow.getFullYear();
Then you must of cource rename the current sheet tab name from 2023 til MyCost-2023 (or whatever you call it )
And if you are perfectly good with 2023 as the sheet(tab)name , just use
let sheetName = timeOfJustNow.getFullYear();
Ah, ok, now I understand. But I’m perfectly good with 2023 as sheet name. Thx!
1 Like
Note that due to daylight time savings, the time might be wrong with one hour, it’s corrected in v1.6.1
1 Like
Testers needed for v1.7.0
It should handle timezones and summertime/daylight changes better. Especially users from other timezones than Oslo, should be nice to get it verified. It’s only out in test (Easy Logger | Homey) so it must be installed by purpose
