How to fix Python at Object of type datetime is not JSON serializable error

Question

I use data mining by twitter. So I get value create_at from twitter to save in file excel after that send file excel to google sheet but It can't to send it.
It have error this :

response = service.spreadsheets().values().append(
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\site- 
packages\googleapiclient\discovery.py", line 830, in method
headers, params, query, body = model.request(
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\site- 
packages\googleapiclient\model.py", line 161, in request
body_value = self.serialize(body_value)
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\site- 
packages\googleapiclient\model.py", line 274, in serialize
return json.dumps(body_value)
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\json\__init__.py", line 231, 
in dumps
return _default_encoder.encode(obj)
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\json\encoder.py", line 199, in 
encode
chunks = self.iterencode(o, _one_shot=True)
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\json\encoder.py", line 257, in 
iterencode
return _iterencode(o, 0)
File "C:\Users\What Name\AppData\Local\Programs\Python\Python38-32\lib\json\encoder.py", line 179, in 
default
raise TypeError(f'Object of type {o.__class__.__name__} '
TypeError: Object of type datetime is not JSON serializable

Or may be problem have this code ?

xlApp = win32.Dispatch('Excel.Application')
wb = xlApp.Workbooks.Open(r"F:\work\feen\WU	witter.xlsx")
ws = wb.WorkSheets('Sheet1')
rngData = ws.Range('A1').CurrentRegion()

gsheet_id = 'sheet_id'
CLIENT_SECRET_FILE = 'credentials2.json'
API_SERVICE_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

service = Create_Service(CLIENT_SECRET_FILE,API_SERVICE_NAME,API_VERSION,SCOPES)
response = service.spreadsheets().values().append(
  spreadsheetId=gsheet_id,
  valueInputOption='RAW',
  range='data1!A1',
   body=dict(
      majorDimension='ROWS',
      values=rngData
  )
).execute()

wb.Close(r"F:\work\feen\WU	witter.xlsx")

user_11551468 · Answer

Answer:

In order to fix the Object of type datetime is not JSON serializable error, you need to convert all instances of datetime objects in your object to string.

There are other errors in your code, however, meaning this alone will not make your program run.

Converting the `datetime` objects to `string` objects:

In python, you can natively convert JSON data to string using json.dumps() with a default conversion of string.

You can do this by adding this line before the service.spreadsheets().values().append() call:

//rngData at this point has already been assigned
rngData = json.dumps(rngData, indent = 4, sort_keys = True, default = str)

NB: This on its own will not fix your code!

Other Matters:

When making calls to the Google Sheets API, it is very important that you make the requests in the way that the servers are expecting to receive those requests. That is to say, it is important to follow the documentation for making requests.

I am on a linux machine, and so I can not test the output format of win32.Dispatch().Workbooks.Open().Worksheets().Range().CurrentRegion(), but if the Microsoft Documentation on the Worksheet.Range property of Excel is anything to go by, I can safely assume that it's output isn't in the format required by the spreadsheets.values.append method:

array (ListValue format):

The data that was read or to be written. This is an array of arrays, the outer array representing all the data and each inner array representing a major dimension. Each item in the inner array corresponds with one cell.

For output, empty trailing rows and columns will not be included.

For input, supported value types are: bool, string, and double. Null values will be skipped. To set a cell to an empty value, set the string value to an empty string.

I'm not 100% sure if the output is the same, but to try and emulate what you're trying I used the python package xlrd to get the values from the Excel file you provided like so:

workbook = xlrd.open_workbook("twitter.xlsx")
sheet = workbook.sheet_by_index(0)
data = [sheet.row_values(rowx) for rowx in range(sheet.nrows)]

And, as in the screenshot you provided in a comment (seen below):

I had the same response. Scrolling up, the error was due to a bad request:

googleapiclient.errors.HttpError:


specifically, Invalid value at 'data.values'. You will need to adhere to the Google Sheets API request specification for this method.
References:

Method: spreadsheets.values.append | Sheets API - Response body
Worksheet.Range property (Excel) | Microsoft Docs
REST Resource: spreadsheets.values | Sheets API - Resource: ValueRange
xlrd documentation - xlrd 1.1.0 documentation

How to fix Python at Object of type datetime is not JSON serializable error

1 Answers

Answer:

Converting the datetime objects to string objects:

Other Matters:

References:

Converting the `datetime` objects to `string` objects: