Trying to append data to google sheets via google sheets API in Python

My intentions are to put the data extracted from places API into google sheets.
I have followed a lot of methods including using gspread library and making a service account and then authorizing it.

I am currently trying with the service account where I have passed on the credentials, built a service request, and then executed it. However, it is throwing me the following error Invalid JSON payload received. Unknown name

What am I doing wrong?

I wanted to append data in google sheets but I am getting an error.

Code

from __future__ import print_function

import requests
import urllib.parse as urlparse
from googleapiclient import discovery
import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


SCOPES = ['https://www.googleapis.com/auth/spreadsheets',
          'https://www.googleapis.com/auth/drive',
          'https://www.googleapis.com/auth/drive.file']

# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = '1_oKFw7gYmUWDUZxZ6Dgo1uLM9Tf_Bc-4bnq4jiJbQUs'
SAMPLE_RANGE_NAME = 'Sheet1'
creds = None

if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
        creds.refresh(Request())
    else:
        flow = InstalledAppFlow.from_client_secrets_file(
            'credentials.json', SCOPES)
        creds = flow.run_local_server(port=0)
    # Save the credentials for the next run
    with open('token.json', 'w') as token:
        token.write(creds.to_json())
        
spreadsheet_id = '***************'
range_ = 'Sheet1!'
value_input_option = 'RAW'        



service = discovery.build('sheets', 'v4', credentials=creds)


value_range_body = ['a']


request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=range_,                
valueInputOption=value_input_option, body=value_range_body)
response = request.execute()

Error

> <HttpError 400 when requesting
> https://sheets.googleapis.com/v4/spreadsheets/1_oKFw7gYmUWDUZxZ6Dgo1uLM9Tf_Bc-4bnq4jiJbQUs/values/Sheet1%21:append?valueInputOption=RAW&amp;alt=json returned "Invalid JSON payload received. Unknown name "": Root element
> must be a message.". Details: "[{'@type':
> 'type.googleapis.com/google.rpc.BadRequest', 'fieldViolations':
> [{'description': 'Invalid JSON payload received. Unknown name "": Root
> element must be a message.'}]}]">

The error is related to how value_range_body is parse. If you want to use the method spreadsheets.values.append, you can base you code in the following sample:

Note: the sample code that I'm using is base on a database, and using OAuth, but the request body should be the same

from __future__ import print_function
import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
# If modifying these scopes, delete the file token.json.
SCOPES = ['https://www.googleapis.com/auth/drive.file', 
'https://www.googleapis.com/auth/drive',
'https://www.googleapis.com/auth/spreadsheets' ]
# The ID and range of a sample spreadsheet.
spreadsheetId = 'sheetID'
def main():
creds = None
if os.path.exists('token.json'):
creds = Credentials.from_authorized_user_file('token.json', SCOPES)
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(
'credentials.json', SCOPES)
creds = flow.run_local_server(port=0)
with open('token.json', 'w') as token:
token.write(creds.to_json())
try:
service = build('sheets', 'v4', credentials=creds)
# 'records_df' is the variable with the data that will be inserted in the Sheet. 
# In your case it will be the data coming from places API
records_db = [['2023-01-18', '1234', 'ABC', 1234], 
['2023-01-17', '5678', 'DEF', 5678], 
['2023-01-16', '9012', 'ABC', 9012]]
# 'range_db' is the range of the where the data will be added.
range_db = "Sheet1"
value_input_option = "RAW"
insert_data_option = "INSERT_ROWS"
# This is the 'value_range_body' or JSON
value_range_body = {
"majorDimension": "ROWS",
"values": records_db,
}
request = service.spreadsheets().values().append(spreadsheetId=spreadsheetId, 
range=range_db, 
valueInputOption=value_input_option, 
insertDataOption=insert_data_option, 
body=value_range_body)
response = request.execute()
except HttpError as err:
print(err)
if __name__ == '__main__':
main()