Copy & paste values using google sheet api-CodePudding

I want to copy "'data'!A2:D2" to "'data'!K2:N2" but I'm getting the following error. The script works with the batch update but cannot work it out with copy and pasting.

{"error": {
    "code": 400,
    "message": "Invalid JSON payload received. Unknown name \"requests\": Cannot find field.",
    "status": "INVALID_ARGUMENT",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.BadRequest",
        "fieldViolations": [
          {
            "description": "Invalid JSON payload received. Unknown name \"requests\": Cannot find field."
          }]}]}}

The code I'm using.

import json
import gspread
import requests
from oauth2client.service_account import ServiceAccountCredentials

scope = [
    "https://spreadsheets.google.com/feeds",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive",
]

creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
sh = client.open("kite")

spreadsheet_id = sh.id
sheet_id = sh.worksheet("data").id

headers = {
    "Authorization": "Bearer "   creds.get_access_token().access_token,
    "Content-Type": "application/json",
}

reqs = [
    {
        "copyPaste": {
            "source": {
                "sheetId": sheet_id,
                "startRowIndex": 1,
                "endRowIndex": 2,
                "startColumnIndex": 0,
                "endColumnIndex": 4,
            },
            "destination": {
                "sheetId": sheet_id,
                "startRowIndex": 1,
                "endRowIndex": 2,
                "startColumnIndex": 10,
                "endColumnIndex": 14,
            },
            "pasteType": "PASTE_VALUES",
            "pasteOrientation": "NORMAL",
        }
    }
]

r = requests.post(
    f"https://sheets.googleapis.com/v4/spreadsheets/{spreadsheet_id}/values:batchUpdate",
    headers=headers,
    data=json.dumps({"requests": reqs}),
)

Reference: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#CopyPasteRequest

CodePudding user response：

The copyPaste request can be used with spreadsheets.batchUpdate method. So, when your script is modified, please modify as follows.

From:

f"https://sheets.googleapis.com/v4/spreadsheets/{spreadsheet_id}/values:batchUpdate",

To:

f"https://sheets.googleapis.com/v4/spreadsheets/{spreadsheet_id}:batchUpdate",

Note:

In your script, gspread is used. When gspread is used, your script can be also modified as follows.

  client = gspread.authorize(creds)
  sh = client.open("kite")
  sheet_id = sh.worksheet("data").id
  reqs = [
      {
          "copyPaste": {
              "source": {
                  "sheetId": sheet_id,
                  "startRowIndex": 1,
                  "endRowIndex": 2,
                  "startColumnIndex": 0,
                  "endColumnIndex": 4,
              },
              "destination": {
                  "sheetId": sheet_id,
                  "startRowIndex": 1,
                  "endRowIndex": 2,
                  "startColumnIndex": 10,
                  "endColumnIndex": 14,
              },
              "pasteType": "PASTE_VALUES",
              "pasteOrientation": "NORMAL",
          }
      }
  ]
  res = sh.batch_update({"requests": reqs})

Reference:

Method: spreadsheets.batchUpdate