Google Sheets integration data format

Configuration
,
1

Hello,
I’m using the new integration for Google Sheets to store some sensor data and i’d like to write also the current date i’m using the template “{{ now().date() }}” but into file i see it is writed '07/10/2022 and the column into file it is considered as text.

There is a way to keept the format as date douring the storing of data?

service: google_sheets.append_sheet
data:
  config_entry: 74b80eba175c89bb481bdf1ab1186571
  worksheet: Consumi
  data:
    Data: "{{ now().date() }}"
    Id: Totale
    KWh: "{{states.sensor.consumo_totale_oggi.state}}"
    Costo: "{{states.sensor.costo_consumo_oggi_totale.state}}"

image

Thanks.

2

I suspect that it’s not going to be possible to do that. YAML pretty much only understands strings and numbers (both integer and floating point). What I’m finding frustrating is the auto generated ‘created’ column (nice of them to do that so we don’t have to ourselves!) is not a datetime object. Since that’s coming from code, they should totally be able to force it to a proper datetime see WTH: New Google Sheets integration should insert creation time as proper time that I just created about this.

3

@michelebossa
Check this comment on an issue: Google sheet integration append data to the first row · Issue #79731 · home-assistant/core · GitHub

I think you’ll figure it out from there. I learned more from that single comment than i learned in 4 hours of trial and error and struggle with poor documentation.

1 Like
4

Thank @MrBill it is worked “{{ now().strftime(”%m/%d/%Y") }}"

5

Is that really sticking it into the sheet as a proper date? It’s still going in as a text string for me :-/ using the exact example that was provided in that GitHub issue.

6

i have this
image

i see format option is Automatic

7

Make that date column (the second one) a bit larger. Does the date sit on the left or on the right? If it’s on the left, then it’s coming in as text, not a date (you can check this to see if there is an ' [apostrophe] at the front of the cell data). If it’s on the right, then it’s actually being detected as a date. I do have all my columns currently set to Automatic, that’s the default with Google sheets. I am, however, finding that the date information is all going in as text, not dates :frowning:

8

@tykeal it is text as you’ve noted. While not a perfect ideal solution, one quick “fix” is to record a google sheets macro that selects the entire column and changes it to date. Has no effect on cells that that have already been converted but converts any new cells from text to date.

9

I can confirm it is a text at moment i have made as suggested a marco triggered when open the doc to auto format the column

image
image1753×436 36.9 KB

10

@michelebossa Thanks for that last screenshot. I couldn’t figure out how to make the macro run automatically. With your screenshot I found it!..

11

Is there a way to reformat this to a correct time/date ?
As when i delete the one i made red, i can import to multiple apps like grafana.
But i did see you created a macro, does the macro fix the time like in this screenshot ?
Like from this: 2022-10-17 18:04:34.795513 to 2022-10-17 18:04:34

image

12

I think it is a standard format time of file to have also milliseconds try to chage the macro.

image
image1009×267 27 KB

13

thanks, sadly it doesnt work.
when i run the macro on a test sheet it doesnt change

image

Looks like A2 is text an no number… so basically all my sheets are like this…
is there a way to convert that ?

Or to regex everything out after the . value.

Like if i manual delete .161337 it converts to a value.
I did see there are regex codes for it, do you know a regex code to get rid of all value’s starting rom the . ?

14

Hehe i found a way to finally get rid of that.

Well what you do is press ctr+H on anf put this in search
(\d{6}) and leave replace with empty

select search with regex and untick identical letters/capitals
and press replace all in the tabs you want.

now you need to do 1 more thing, depending on what data you have.
so where we put this (\d{6}) u change it to this: . the DOT
now untick everything and press replace all but select only the tabs you want.
And now the time is corrected and change the text to a value, as all old data is text.

SO basically it removes this value .161337 behind all old data