When writing to google sheet, I get Object of type Decimal is not JSON serializable-CodePudding

I'm trying to write a dateframe to a google sheet. I followed the instructions in the sheets Python API page, but I'm getting a strange error.

The dataframe as decimal values, like this

USD        ORDERS_AVG     DATE
316.60     123.0          2022-05-05 12:30:47.624000-07:00

With the following code, I'm trying to write on a google sheet:

try:
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
    SERVICE_ACCOUNT_FILE = 'key.json'

    creds = None
    creds = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES)

    SAMPLE_SPREADSHEET_ID = 'xxxxxxxxxxxxxxxxxxxxxxxxxx'
    service = build('sheets', 'v4', credentials=creds)
    sheet = service.spreadsheets()
except HttpError as err:
    print(err)

if df is not None and sheet:
    lol = df.values.tolist()
    try:
        clear_request = service.spreadsheets().values().clear(
            spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Data!A2:S")
        clear_response = clear_request.execute()
        print(clear_response)
        request = sheet.values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                                        range="Data!A2", valueInputOption="USER_ENTERED", body={"values": lol}).execute()
        print(request)
    except HttpError as err:
        print(err)

But unless I convert the whole dataframe to string using .astype(str), I get the following error:

Traceback (most recent call last):
  File "dev2.py", line 107, in <module>
    request = sheet.values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
  File "/home/alainmore/anaconda3/envs/slot_alerts_env/lib/python3.8/site-packages/googleapiclient/discovery.py", line 1094, in method
    headers, params, query, body = model.request(
  File "/home/alainmore/anaconda3/envs/slot_alerts_env/lib/python3.8/site-packages/googleapiclient/model.py", line 160, in request
    body_value = self.serialize(body_value)
  File "/home/alainmore/anaconda3/envs/slot_alerts_env/lib/python3.8/site-packages/googleapiclient/model.py", line 273, in serialize
    return json.dumps(body_value)
  File "/home/alainmore/anaconda3/envs/slot_alerts_env/lib/python3.8/json/__init__.py", line 231, in dumps
    return _default_encoder.encode(obj)
  File "/home/alainmore/anaconda3/envs/slot_alerts_env/lib/python3.8/json/encoder.py", line 199, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "/home/alainmore/anaconda3/envs/slot_alerts_env/lib/python3.8/json/encoder.py", line 257, in iterencode
    return _iterencode(o, 0)
  File "/home/alainmore/anaconda3/envs/slot_alerts_env/lib/python3.8/json/encoder.py", line 179, in default
    raise TypeError(f'Object of type {o.__class__.__name__} '
TypeError: Object of type Decimal is not JSON serializable

The thing is that I don't want to convert decimal values to strings. I need to keep numbers as numbers, and dates as dates, in the Google Sheet.

What am I doing wrong?

------EDIT 1

if I print df.values.lolist(), I get this:

[Decimal('91.21'), Decimal('4.1'), Timestamp('2022-05-05 14:30:11.601000-0700', tz='America/Los_Angeles')], 
[Decimal('122.04'), Decimal('2.23'), Timestamp('2022-05-05 14:30:11.601000-0700', tz='America/Los_Angeles')], 
[Decimal('118.83'), Decimal('5.00'), Timestamp('2022-05-05 14:30:11.601000-0700', tz='America/Los_Angeles')], 
[Decimal('1591.61'), Decimal('28.00'), Timestamp('2022-05-05 14:30:11.601000-0700', tz='America/Los_Angeles')], 
[Decimal('97.43'), Decimal('3.02'), Timestamp('2022-05-05 14:30:11.601000-0700', tz='America/Los_Angeles')]]

Sample dataframe:

x = datetime.datetime.now()  

data = {'USD':[2219.48, 392.01, 16211.63, None],
        'ORDERS':[167.00, 10.00, 572.00, None],
        'DATE':[x,x,x,x]}
df = pd.DataFrame(data)

CodePudding user response：

Well, this was a really REALLY dumb solution. It seems the google sheet I was writing to was set with a locale for Colombia, where the use a decimal comma, instead of a decimal point... so values like 53.00 were considered as a string... after changing the locale, I was able to save the dataframe as strings and google sheets automatically recognized numeric values with decimals.