Google Sheets API for python2.7 --> "Invalid JSON payload. Root element must be a message"

Question

I've been struggling with this error for a number of weeks now and have tried solutions from previously posted questions relating to the Python API for Google Sheets.

I continuously get an error when I make a "write" request to my spreadsheet through the Google Sheets API for python. The error says that I'm submitting an invalid JSON, but I've tested the JSON structure against the interactive test window (Google APIs Explorer) and the request from there updates my sheet properly.

The code is below

from __future__ import print_function
from apiclient.discovery import build
from httplib2 import Http
from oauth2client import file, client, tools
import datetime
import json

# Call the Sheets API
SPREADSHEET_ID =  #mySheetID
RANGE_NAME = '2018_Raw Data!A3:A367'

months = { 0:"Jan", 1:"Feb",2:"Mar",4:"Apr",5:"May",6:"Jun",7:"Jul",8:"Aug",9:"Sep",10:"Oct",11:"Nov",12:"Dec"}


now = datetime.datetime.now()
date = str(now.day) +"-"+ months[now.month] + "-"+str(now.year)
day_of_year = now.timetuple().tm_yday
myRow = day_of_year+2

print (date)
print (myRow)


BWRange= '2018_Raw Data!B' + str(myRow)
BFRange= '2018_Raw Data!C' + str(myRow)
myBodyWeight=150
myBF="10%"
print (BWRange)
print (BFRange)


BWData = {}
BWData['values']= [[myBodyWeight]]
BWData['majorDimension']="ROWS"
BWData['range']= BWRange
BWJson= json.dumps(BWData)

BFData = {}
BFData['values']= [[myBF]]
BFData['majorDimension']="ROWS"
BFData['range']= BFRange
BFJson= json.dumps(BFData)

print (BWJson)
print (BFJson)


# Setup the Sheets API
SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
store = file.Storage('token.json')
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('credentials.json', SCOPES)
    creds = tools.run_flow(flow, store)
service = build('sheets', 'v4', http=creds.authorize(Http()))


#bw                              
request = service.spreadsheets().values().update(spreadsheetId=SPREADSHEET_ID,range=BWRange, valueInputOption="USER_ENTERED", body=BWJson)
response = request.execute()
pprint(response)

#bf
request = service.spreadsheets().values().update(spreadsheetId=SPREADSHEET_ID, range=BFRange,valueInputOption="USER_ENTERED", body=BFJson)
response = request.execute()
pprint(response)

Error is below:

Traceback (most recent call last):
  File "C:\sheets\mySheets.py", line 65, in <module>
    response = request.execute()
  File "C:\Python27\lib\site-packages\googleapiclient\_helpers.py", line 130, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "C:\Python27\lib\site-packages\googleapiclient\http.py", line 842, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/1demD8sm5-Jvi7ImHcOu03sHaU7PF61ym1eyvjN1bGfw/values/2018_Raw%20Data%21B234?alt=json&valueInputOption=USER_ENTERED returned "Invalid JSON payload received. Unknown name "": Root element must be a message.">

I've reviewed the following posts below:
Python3 google spreadsheet api batchUpdate Json formatting
Invalid JSON Payload error with python google sheets API

Any help is appreciated - thanks!

Answer 1

I think that your request body is correct. So how about this modification?

From :

request = service.spreadsheets().values().update(spreadsheetId=SPREADSHEET_ID,range=BWRange, valueInputOption="USER_ENTERED", body=BWJson)

request = service.spreadsheets().values().update(spreadsheetId=SPREADSHEET_ID, range=BFRange,valueInputOption="USER_ENTERED", body=BFJson)

To :

request = service.spreadsheets().values().update(spreadsheetId=SPREADSHEET_ID,range=BWRange, valueInputOption="USER_ENTERED", body=BWData)

request = service.spreadsheets().values().update(spreadsheetId=SPREADSHEET_ID, range=BFRange,valueInputOption="USER_ENTERED", body=BFData)

Note :

• In this modification, json.dumps() was removed.
• This script supposes that Sheets API is enabled at API console, and your access token can be used for spreadsheets().values().update().

If this didn't work, please tell me. I would like to modify it.