Invalid JSON payload received. Unexpected token. error when bulk uploading with update_cells(cell_list)

[pptlim]

Hello,

I started getting the error when I updated to Python 3.7 two days ago. I use the following code to upload a pandas df to a gsheet - the error happens with any df.

Re the error: this seems to pertain to some cells with value '180901_millbrae_1', may or may not be relevant. This exact same data and code used to work fine.

Any ideas?

Error:

Traceback (most recent call last):

  File "<ipython-input-31-85b52784b441>", line 11, in <module>
    ws.update_cells(cell_list)

  File "/Users/../anaconda/lib/python3.7/site-packages/gspread/models.py", line 782, in update_cells
    'values': values_rect

  File "/Users/../anaconda/lib/python3.7/site-packages/gspread/models.py", line 176, in values_update
    r = self.client.request('put', url, params=params, json=body)

  File "/Users/../anaconda/lib/python3.7/site-packages/gspread/client.py", line 79, in request
    raise APIError(response)

APIError: {
  "error": {
    "code": 400,
    "message": "Invalid JSON payload received. Unexpected token.\nae\", 43344, \"None\", NaN], [\"180901_millb\n                    ^",
    "status": "INVALID_ARGUMENT"
  }
}

Code:

n_rows, n_cols = df.shape
k = 1000
for i in range(n_rows//k + 1):    
    row0 = start_row + i*k
    col0 = start_col
    row1 = start_row + min((i+1)*k, n_rows-1)
    col1 = start_col + n_cols - 1
    cell_list = ws.range(inputRange(row0, col0, row1, col1))
    for cell in cell_list:
        val = df.iloc[cell.row-start_row, cell.col-start_col]
        cell.value = val
    ws.update_cells(cell_list)

Operating System: macOS Sierra 10.12.6
Python version: 3.7.0
gspread version: 3.1.0

[Logic-gate]

Hey,

1 - is val a string?

2 - I think this is your issue: \nae", 43344, "None", NaN], ["180901_millb\n ^", why is there a new line at the end. + where is this from. Are you sure that the original value is correctly formatted

3 - Manually(through gspread) append the values, do they work?

[eanunez]

Hi,
I encountered the same problem. I filled up 'NaN' values with empty string,
df.fillna('', inplace=True)
and the problem was solved.

[burnash]

@eanunez thanks for the solution. Closing the issue.

[Imperssonator]

The solution posted above will convert any float-type columns into object-type, which causes problems for downstream numerical use in Sheets (all the numbers will be interpreted as strings). It seems like there needs to be a way for worksheet.update() to automatically leave NaN cells blank, or something.

[Imperssonator]

I found an alternate solution that preserves the numeric data type of numeric columns:

worksht.update(
    [df.columns.values.tolist()] + [[vv if pd.notnull(vv) else '' for vv in ll] for ll in df.values.tolist()]
)

Probably quite slow if your sheet is large but it works 🤷‍♂️

[naterattner]

For what it's worth, I got this same error due to having infinite values in my dataframe. Replacing those with 'NaN' values and then filling those 'NaN' values with empty strings, as suggested above, did the trick:

df.replace([np.inf, -np.inf], np.nan, inplace=True)
df.fillna('', inplace=True)

[shanehh]

Hi,
I encountered the same problem. I filled up 'NaN' values with empty string,
df.fillna('', inplace=True)
and the problem was solved.

IDK why, but it works!! lol

[manugarri]

the fix is quite easy, we just need to setup the allow_nan flag of python json to False. However it seems like it requires some changes to the code since the code only filters None (not NaN, or Infinity).

This is a very brittle solution to be honest.

[ViniciusARZ]

Hi,
I encountered the same problem. I filled up 'NaN' values with empty string,
df.fillna('', inplace=True)
and the problem was solved.

Worked fine for me too!

[Bonobo791]

This just leaves a big whitespace in the cell above the valule. Any other fixes??