Unable to insert datetime object

[p-doyle]

Environment info
Operating System: Windows 10
Python version: 2.7.12
gspread version: 2.0.0

Steps to reproduce

1. worksheet.insert_row([datetime.datetime.now()])

Traceback:

Traceback (most recent call last):
  File "D:/gspreadinserttest.py", line 21, in <module>
    sh.insert_row([datetime.datetime.now()])
  File "C:\Python27\lib\site-packages\gspread\v4\models.py", line 685, in insert_row
    'values': [values]
  File "C:\Python27\lib\site-packages\gspread\v4\models.py", line 112, in values_update
    r = self.client.request('put', url, params=params, json=body)
  File "C:\Python27\lib\site-packages\gspread\v4\client.py", line 67, in request
    endpoint, json=json, params=params, data=data, files=files
  File "C:\Python27\lib\site-packages\requests\sessions.py", line 566, in put
    return self.request('PUT', url, data=data, **kwargs)
  File "C:\Python27\lib\site-packages\requests\sessions.py", line 494, in request
    prep = self.prepare_request(req)
  File "C:\Python27\lib\site-packages\requests\sessions.py", line 437, in prepare_request
    hooks=merge_hooks(request.hooks, self.hooks),
  File "C:\Python27\lib\site-packages\requests\models.py", line 308, in prepare
    self.prepare_body(data, files, json)
  File "C:\Python27\lib\site-packages\requests\models.py", line 458, in prepare_body
    body = complexjson.dumps(json)
  File "C:\Python27\lib\json\__init__.py", line 231, in dumps
    return _default_encoder.encode(obj)
  File "C:\Python27\lib\json\encoder.py", line 201, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "C:\Python27\lib\json\encoder.py", line 264, in iterencode
    return _iterencode(o, 0)
  File "C:\Python27\lib\json\encoder.py", line 178, in default
    raise TypeError(repr(o) + " is not JSON serializable")
TypeError: datetime.datetime(2018, 3, 11, 13, 26, 34, 292000) is not JSON serializable

I know it is possible to convert to a string before inserting however in previous gspread versions I could insert a datetime object and it would format the datetime using the formatting from the google sheet. Is there anyway this kind of functionality could be replicated in 2.0.0?

[burnash]

First of all, thanks for trying a new version :)

It's a good point. Currently, there's no such functionality.

You're right, in the previous version (0.6.2), you could just pass an arbitrary object and it'll be converted to a string representation with the help of unicode function. Here the code responsible for the construction of an update xml feed in the version 0.6.2:

gspread/gspread/models.py

Lines 460 to 487 in a6fefba

	def _create_update_feed(self, cell_list):
	feed = Element('feed', {'xmlns': ATOM_NS,
	'xmlns:batch': BATCH_NS,
	'xmlns:gs': SPREADSHEET_NS})
	id_elem = SubElement(feed, 'id')
	id_elem.text = construct_url('cells', self)
	for cell in cell_list:
	entry = SubElement(feed, 'entry')
	SubElement(entry, 'batch:id').text = cell.element.find(
	_ns('title')).text
	SubElement(entry, 'batch:operation', {'type': 'update'})
	SubElement(entry, 'id').text = cell.element.find(_ns('id')).text
	edit_link = finditem(lambda x: x.get('rel') == 'edit',
	cell.element.findall(_ns('link')))
	SubElement(entry, 'link', {'rel': 'edit',
	'type': edit_link.get('type'),
	'href': edit_link.get('href')})
	SubElement(entry, 'gs:cell', {'row': str(cell.row),
	'col': str(cell.col),
	'inputValue': unicode(cell.value)})
	return feed

This is where it happens:'inputValue': unicode(cell.value).

In the new version, the implicit conversion is no longer needed, since API v4 uses JSON. In 2.0.0 the values go to the Sheets API as is:

gspread/gspread/v4/models.py

Lines 511 to 551 in a6fefba

	def update_cells(self, cell_list, value_input_option='RAW'):
	"""Updates cells in batch.
	:param cell_list: List of a :class:`Cell` objects to update.
	:param value_input_option: Determines how input data should be
	interpreted. See `ValueInputOption`_
	in the Sheets API.
	.. _ValueInputOption: https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
	Example::
	# Select a range
	cell_list = worksheet.range('A1:C7')
	for cell in cell_list:
	cell.value = 'O_o'
	# Update in batch
	worksheet.update_cells(cell_list)
	"""
	values_rect = cell_list_to_rect(cell_list)
	start = rowcol_to_a1(cell_list[0].row, cell_list[0].col)
	end = rowcol_to_a1(cell_list[-1].row, cell_list[-1].col)
	range_label = '%s!%s:%s' % (self.title, start, end)
	data = self.spreadsheet.values_update(
	range_label,
	params={
	'valueInputOption': value_input_option
	},
	body={
	'values': values_rect
	}
	)
	return data

And that's where this age old problem arises. On the one hand, the idea of implicitly casting everything to string is not my favorite. On the other hand, for certain types having an implicit cast can improve end-user experience.

One option is to implement something like default parameter in json.dump:

If specified, default should be a function that gets called for objects that can’t otherwise be serialized. It should return a JSON encodable version of the object or raise a TypeError. If not specified, TypeError is raised.

But I haven't yet figured out how to squeeze it in properly.

[cgmorton]

I'm not sure how helpful this will be, but I was able to write a date to the sheet by converting it to a count of the number of days since the Google Sheets epoch date of "1899-12-30" (https://developers.google.com/sheets/api/guides/concepts).

Something like: cell.value = (datetime.datetime(2018, 3, 11) - datetime.datetime(1899, 12, 30)).days

[aramirez087]

@cgmorton thanks so much I was looking for a solution all over the web, this worked on pyton 2.7:

(date.today() - date(1899, 12, 30)).days

[lavigne958]

The main issue here is:

• HTTP requests sent to Google API only accepts strings or unicode (aka some text in some languages)
• Should we convert everything to a string or not ?

I would agree that everything should be converted to its string representation as in all cases we can't send a datetime object over to the spreadsheet, and sending the string representation of a datetime object is working fine and SpreadSheet can convert it to a date format seamlessly.

I first fix would be: make sure everything sent over is a string or raise otherwise. this would prevent the raise of an underlying library that stops because it cannot read this json object.

[MAlhussaini]

You could use this function, it worked for me:

Imports:

import gspread
import datetime

Lets have a variable now as datetime object:

now = datetime.datetime.now()

then pass now to the following function as string.

wb.values_update(
    'sheet1!A2',
    params={
        'valueInputOption': 'USER_ENTERED'
    },
    body={
        'values': [[str(now)]]
    }
)

This way your entered datetime will be recognized by google sheet as if it was entered by a person. If you want to know more about how to use gspread follow this link.

Entering multiple values could be done as well by replacing [[str(now)]]

with:

[[str(now) ,'b','c','d']]