Solution for the date problem

[pipetrunks]

Is your feature request related to a problem? Please describe. I think a lot of people, like me, have encountered the date problem since a datetime object cannot be exported from python to google spreadsheet

Describe the solution you'd like Do a conversion from date number used by python to number used by google spreadsheet (same as used by excel):

from datetime import datetime 
def Time2GSS(datetime_object):
    #datetime_object = datetime.strptime(self.datetime_str, '%d/%m/%Y') # Si quieres puede iniciar en un string
    secs = datetime.timestamp(datetime_object) # Tiempo en segundos
    Difpy_ex_h = 25568 # Días de diferencia entre el punto cero de excel y python (Excel inicia el 0/1/1900 a las 00 y Python el 31/12/1969 a las 19)
    Difpy_ex_s = (Difpy_ex_h * 24 + 19) * 60 * 60 # La diferencia en segundos incluyendo las 19 horas
    Day_ex = (secs + Difpy_ex_s)/(60 * 60 *24) # El número de dias Excel style obtenido en python
    return Day_ex

datetime_str = '23/04/2020'
datetime_object = datetime.strptime(datetime_str, '%d/%m/%Y')
DateGSS = Time2GSS(datetime_object)

Describe alternatives you've considered I have not considered alternatives

Additional context I was already able to send dates from python to google spreadsheet and I don't have the need to convert them from the user interface.

Thanks so much for the API. It is very useful

[burnash]

Thank you very much.

Could you please give more context: what method do you use to update a spreadsheet?

Also, I tried to run the snippet you provided, updated a test spreadsheet, and got this:

In [5]: DateGSS
Out[5]: 43943.708333333336

After I updated the sheet and change the format to "Date" I get this:

Which looks like one day earlier than the initial datetime_str in the example.

[pipetrunks]

Ok. the correct code with comments in english language:

from datetime import datetime def Time2GSS (datetime_object):      secs = datetime.timestamp (datetime_object) # Time in seconds      Difpy_gs_h = 25568 # Days difference between GSS zero point and Python zero point (Excel starts on 0/1/1900 at 00h and Python on 12/31/1969 at 19h) - (With your comment, i verify and GSS have a zero point at 30/12/1989 at 00h)      Difpy_ex_s = (Difpy_ex_h 24 + 19) 60 60 # The difference in seconds including 19 hours      Day_ex = (secs + Difpy_ex_s) / (60 60 * 24) # The number in GSS style days obtained in python      return Day_ex datetime_str = '23/04/2020 ' datetime_object = datetime.strptime (datetime_str, '% d /% m /% Y') DateGSS = Time2GSS (datetime_object)

The images have the zero´s in date format for Excel, Gss and Python .

Now, the past code (with the excel format) is working for me when exporting the date using the following line: IniCoord = 'A' + str(PrevSize + 2) EndCoord = 'H' + str(PrevSize + ActualSize + 1) worksheet.update(IniCoord + ':' + EndCoord, GSSList) worksheet.format("H2:H", {"numberFormat": {"type": "DATE"},"horizontalAlignment": "RIGHT",})

@burnash

[pipetrunks]

Please confirm your zero point, maybe in your country is diferent (I´m in Colombia, South-America). If your fail persist, change the number for 25569:

Difpy_gs_h = 25569

[lavigne958]

Hi the issue is:

• the datetime object in python uses what we call EPOCH as the first date (so the date with the timestamp: 0).
• the SpreadSheet uses a different date for that, which 1990 (or at least the 0 value is just before 1, so it's the last second before switching to 1990).

This is not handled by gspread, even now years after. We could add some datetime management but I am alone and that is a lot of work for a small improvement.

For now I would recommend you:

• use the provided method to render the date to a string format. you cannot send datetime python object as JSON :-(
  • according to that you can even set the right timezone etc for the rendered string
• send the string as user entered format, so the API will parse it and recognized the date format. example:

  sheet.update(
  "A1",
  [["{}".format(my_python_date)]],
  value_input_option=gspread.utils.ValueInputOption.user_entered
  )

[lavigne958]

closing this issue.