Datetime.now() error when writing to Google Sheets

Bug Reports
1

What I’m trying to do:
Hi everyone, I hope my message finds you well. I am trying to save some data to google sheets, but I think I came across a small catch.

I am trying to update a particular cell of google sheets with a timestamp using: datetime.now(), but it throws an error. I think this might be a bug.

request failed: Invalid values[3][0]: struct_value { fields { key: “datetime-string” value { string_value: “2022-10-02 18:31:58.137000+0200” } } }

However, when u create a new row using add_row() and use datetime.now() as a column value, it works absolutely fine.
Thanks very much or looking into this :slight_smile:
All the best,
Petr

What I’ve tried and what’s not working:
passing datetime.now() → str can work around the issue, but the format is different (not represented by a number anymore).

Code Sample:

# this works okay with datetime.now()
rows = self.worksheet.add_row(string_='test', integer_ = 3, float_ = 3.14, datetime_=datetime.now())

# this throws an error, 'datetime_' is the column name
 rows = self.worksheet.list_rows(string_='test')
 for row in rows:
      row['datetime_'] = datetime.now()

Clone link:
share a copy of your app

2

I had an issue yesterday with comparing datetime.now() against a datatime column on app_table. I have been doing this before without issues but yesterday, it threw an error relating to naive timezone in datetime.now() whereas the app_table datetime column is timezone aware. So like you, I suspect there may have been some code changes and /or bugs. Anyways, I made my datetime.now timezone aware like so datetime.now(datetime.timezone.utc) and it resolved the issue.

3

Yes it looks like there’s a bug here.

When working with the Google Sheets API, I would generally recommend converting your inputs to strings.
Google sheets generally expects you to update cells as if the user was typing the input value, i.e. by providing a string.

What I’ve tried and what’s not working:
passing datetime.now() → str can work around the issue, but the format is different (not represented by a number anymore).

I’m not sure I follow this. Can you add more details about why using a string fails?

@edmondssesay - it’s a similar but unrelated issue. All naive datetimes between the client and server are stamped with a timezone.
See the documentation here:

1 Like