“Invalid JSON Payload received” when trying to use google sheets' spreadsheets().batchUpdate()

0
votes

I am writing a python script that first creates a sheet in google sheets, then uploads a CSV into it, then generates tables for slides from the sheet. I am using sheets API, and I can successfully create a new sheet. However, when trying to upload the CSV into it, I get an "invalid JSON payload" error, saying that PasteDataRequest is not found. I'm taking it directly from the docs PasteDataRequest docs, batchUpdate docs

And this is how the script looks like

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

SCOPES = (
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/presentations',
)
store = file.Storage('.//script//storage.json')
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('.//script//credentials.json', SCOPES)
    creds = tools.run_flow(flow, store)
HTTP = creds.authorize(Http())
SHEETS = discovery.build('sheets', 'v4', http=HTTP)
SLIDES = discovery.build('slides', 'v1', http=HTTP)

print('Creating Sheet')
csvInInbox = './/Inbox//Report.csv'
reportSheet = {
    'properties': {
        'title': 'CRM results report - ' + str(datetime.now())
    }
}
sheetsRsp = SHEETS.spreadsheets().create(body=reportSheet,fields='spreadsheetId').execute()
sheetId = sheetsRsp.get('spreadsheetId')
print('DONE\nSpreadsheet ID: {0}'.format(sheetsRsp.get('spreadsheetId')))

print('\nUploading CSV into sheet ' + sheetId)

sheetReq = [{'PasteDataRequest': {
        'coordinate': {
        'sheetId': sheetId,
        'rowIndex': 0,
        'columnIndex': 0
      },
      'data': csvInInbox,
      'type': "enum" "(PASTE_NORMAL)",
      'delimiter': ",",
    }
}]
sheetsRsp = SHEETS.spreadsheets().batchUpdate(body = {'requests': sheetReq},spreadsheetId=sheetId).execute()
print("DONE\n")

It seems that according to the docs, thats the correct request. Can anyone tell what am I doing wrong here? It's the first time I'm using APIs so maybe I forgot something.

Error message:

"Invalid JSON payload received. Unknown name "PasteDataRequest" at 'requests[0]': Cannot find field."
1
pythongoogle-sheetsgoogle-apigoogle-sheets-api

1 Answers

1
votes

I believe your goal as follows.

  • You want to create new Google Spreadsheet and put the values to the 1st tab.
  • You want to achieve this using googleapis with Python.
  • You have already been able to get and put values for Spreadsheet using Sheets API.

For this, how about this answer?

Modification points:

  • csvInInbox = './/Inbox//Report.csv' cannot be directly used for the request body like 'data': csvInInbox. It is required to read the file content.

  • sheetId of sheetId = sheetsRsp.get('spreadsheetId') is the Spreadsheet ID. So when this is used, an error occurs. In your case, the sheet ID of 1st tab in new Spreadsheet is 0. So you can use this.

  • Your request body is required to be modified. Please modify as follows.

    • From

        sheetReq = [{'PasteDataRequest': {
          'coordinate': {
          'sheetId': sheetId,
          'rowIndex': 0,
          'columnIndex': 0
        },
        'data': csvInInbox,
        'type': "enum" "(PASTE_NORMAL)",
        'delimiter': ",",
      }
  }]

    • To

        sheetReq = [{
      "pasteData": {
          "coordinate": {
              "sheetId": 0,
              "rowIndex": 0,
              "columnIndex": 0
          },
          "data": csvInInbox,
          "type": "PASTE_NORMAL",
          "delimiter": ","
      }
  }]

When above points are reflected to your script, it becomes as follows.

Modified script:

From:

print('Creating Sheet')
csvInInbox = './/Inbox//Report.csv'
reportSheet = {
    'properties': {
        'title': 'CRM results report - ' + str(datetime.now())
    }
}
sheetsRsp = SHEETS.spreadsheets().create(body=reportSheet,fields='spreadsheetId').execute()
sheetId = sheetsRsp.get('spreadsheetId')
print('DONE\nSpreadsheet ID: {0}'.format(sheetsRsp.get('spreadsheetId')))

print('\nUploading CSV into sheet ' + sheetId)

sheetReq = [{'PasteDataRequest': {
        'coordinate': {
        'sheetId': sheetId,
        'rowIndex': 0,
        'columnIndex': 0
      },
      'data': csvInInbox,
      'type': "enum" "(PASTE_NORMAL)",
      'delimiter': ",",
    }
}]
sheetsRsp = SHEETS.spreadsheets().batchUpdate(body = {'requests': sheetReq},spreadsheetId=sheetId).execute()
print("DONE\n")

To:

print('Creating Sheet')
csvInInbox = './/Inbox//Report.csv'
with open(csvInInbox) as f:
    csvInInbox = f.read()
reportSheet = {
    'properties': {
        'title': 'CRM results report - ' + str(datetime.now())
    }
}
sheetsRsp = SHEETS.spreadsheets().create(body=reportSheet, fields='spreadsheetId').execute()
sheetId = sheetsRsp.get('spreadsheetId')
print('DONE\nSpreadsheet ID: {0}'.format(sheetsRsp.get('spreadsheetId')))
print('\nUploading CSV into sheet ' + sheetId)
sheetReq = [{
    "pasteData": {
        "coordinate": {
            "sheetId": 0,
            "rowIndex": 0,
            "columnIndex": 0
        },
        "data": csvInInbox,
        "type": "PASTE_NORMAL",
        "delimiter": ","
    }
}]
sheetsRsp = SHEETS.spreadsheets().batchUpdate(body={'requests': sheetReq}, spreadsheetId=sheetId).execute()
print("DONE\n")

Note:

  • Please check whether the file of csvInInbox = './/Inbox//Report.csv' is existing, again.

References: