Search code examples
pythongoogle-sheetsgoogle-sheets-apigspread

gspread update worksheet with csv batch

This is the code I have that is using gspread and I am trying to use the update_batch(body) method, but I'm running into some errors I don't understand. The message I'm getting is on the line sh.batch_update(body). The error message is 'invalid json payload received unknown name SheetId'. I'm confused about gspread. I was told that the Google drive is set up with a service account and that I should be using gspread, so I tried using the import method: gc.import_csv(spreadsheet_id,content), which works, but it also removes other sheets and it also renames the worksheet name, which I don't want. So, I wanted to try using the update-batch(body) method instead, but I'm getting this message which I don't understand. Thank you for any help.

 Traceback (most recent call last):
 File "C:\pyscrpts\mytest.py", line 76, in main
 sh.batch_update(body)
 File "C:\Users\\AppData\Roaming\Python\Python39\site- 
 packages\gspread\spreadsheet.py", line 131, in batch_update
  r = self.client.request(
File "C:\Users\ \AppData\Roaming\Python\Python39\site- 
packages\gspread\client.py", line 92, in request
raise APIError(response)
gspread.exceptions.APIError: {'code': 400, 'message': 'Invalid 
JSON 
payload received. Unknown name "SheetId" at 
\'requests[0].paste_data.coordinate\': Cannot find field.', 
'status': 'INVALID_ARGUMENT', 'details': [{'@type': 
'type.googleapis.com/google.rpc.BadRequest', 'fieldViolations': 
[{'field': 'requests[0].paste_data.coordinate', 'description': 
'Invalid JSON payload received. Unknown name "SheetId" at 
\'requests[0].paste_data.coordinate\': Cannot find field.'}]}]}
script:


from __future__ import print_function
from datetime import date, timedelta
import pickle
import logging
import os.path
import argparse
import sys
import socket
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from google.oauth2 import service_account
import google.auth.transport.requests
import requests
import gspread
# gspread way ************
# authenticate to Google Sheets with a service account credentials 
json file
# If modifying these scopes, delete the file token.pickle.
SCOPES = 
['https://www.googleapis.com/auth/spreadsheets.readonly']
#SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SPREADSHEET_ID = '11G6hTgQ3nrVDQQj94BR5zeXvK453454sdfsfdsfdsd'
base = "C:/pyscrpts/"
# CREDS FILE FOR GSPREAD 
gc = gspread.service_account(filename= 'c:\pyscrpts\creds.json')

csv_file_path = base + 'updates.csv'
SERVICE_ACCOUNT_FILE = 'c:\pyscrpts\creds.json' 

credentials = 
service_account.Credentials.from_service_account_file( 
SERVICE_ACCOUNT_FILE,scopes=SCOPES)
def main():
#logging.basicConfig(filename='errorlog.log', filemode='w', 
format='%(name)s - %(levelname)s - %(message)s')
# Creating an object
#logger=logging.getLogger(__name__)
# Setting the threshold of logger to DEBUG
#logger.setLevel(logging.DEBUG)
creds = None

service = build('sheets', 'v4', SERVICE_ACCOUNT_FILE)  
sh = gc.open_by_key(SPREADSHEET_ID)
ws = sh.sheet1 
#clear worksheet 
ws.clear()

) 
content = open(csv_file_path,'r', encoding='utf-8').read()
gc.import_csv(SPREADSHEET_ID,content)

#Read csv and form request
with open(csv_file_path, 'r', encoding='UTF8') as csv_file:
    csvContents = csv_file.read()
    body = {
       'requests': [{
           'pasteData': {
               "coordinate": {
                   "SheetId": SPREADSHEET_ID,
                   "rowIndex": "0", 
                   "columnIndex": "0", 
               },
               "data": csvContents,
               "type": 'PASTE_NORMAL',
               "delimiter": ',',
           }
       }]
  }  
sh.batch_update(body)  
#requests = 
service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID, 
body=body)
response = requests.execute()

if __name__ == '__main__':
main()
Solution

  • I believe your goal is as follows.

    • You want to put CSV data to the specification sheet in a Google Spreadsheet using googleapis for python.
    • You have already been able to get and put values to the Spreadsheet using Sheets API.

    Modification points:

    • In your request body, "SheetId": SPREADSHEET_ID, is used. In this case, the property name is sheetId and also, this value is required to be the sheet ID. In your script, the Spreadsheet ID is used as the sheet ID.

    When this is reflected in your script, how about the following modifications? For both modifications, CSV data of csvFilePath is put to the sheet of sheetId.

    Modified script 1:

    This is for googleapis of python.

    service = # Please use your script here.

csvFilePath = "###"  # Please set your CSV file path.
spreadsheet_id = "###"  # Please set your Spreadsheet ID
sheetId = "###"  # Please set your sheet ID.

with open(csvFilePath, "r") as f:
    csvContents = f.read()
body = {
    "requests": [
        {
            "pasteData": {
                "coordinate": {
                    "sheetId": sheetId,
                },
                "data": csvContents,
                "type": "PASTE_NORMAL",
                "delimiter": ",",
            }
        },
    ]
}
service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()

    Modified script 2:

    This is for gspread of python.

    import gspread

client = gspread.oauth(###)

csvFilePath = "###"  # Please set your CSV file path.
spreadsheet_id = "###"  # Please set your Spreadsheet ID
sheetId = "###"  # Please set your sheet ID.
spreadsheet = client.open_by_key(spreadsheet_id)

with open(csvFilePath, "r") as f:
    csvContents = f.read()
body = {
    "requests": [
        {
            "pasteData": {
                "coordinate": {
                    "sheetId": sheetId,
                },
                "data": csvContents,
                "type": "PASTE_NORMAL",
                "delimiter": ",",
            }
        },
    ]
}
spreadsheet.batch_update(body)

    References: