How do you copy values from one spreadsheet to another using gspread or some other way?

Question

(beginner) I'm attempting to copy values from one spreadsheet to another using python. I'm using gspread but I can't seem to figure out how to copy the values from my first spreadsheet to the other. How can I copy values from the first spreadsheet and paste it on the other using python?

Here is the updated code:import gspread

from oauth2client.service_account import ServiceAccountCredentials


scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/spreadsheets','https://www.googleapis.com/auth/drive.file','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('sheetproject-956vg2854670.json',scope)
client = gspread.authorize(creds)






spreadsheetId= "1CkeZ8Xw4Xmho-mrFP9teHWVT-unkApzMSUFql5KkrGI"
sourceSheetName = "python test"
destinationSheetName = "python csv"

client = gspread.authorize(creds)
spreadsheet = client.open_by_key(spreadsheetId)
sourceSheetId = spreadsheet.worksheet("python test")._properties['0']. #gid value in the url
destinationSheetId = spreadsheet.worksheet("python csv")._properties['575313690'] #gid value in the url
body = {
    "requests":  [
        {
            "copypaste": {
                "source": {
                    "sheetId": 0,

                    "startRowIndex": 0,
                    "endRowIndex": 20,
                    "startColumnIndex": 0,
                    "endcolumnIndex": 1
                },
                "destination": {
                    "sheetId": 575313690,
                    "startRowIndex": 0,
                    "endRowIndex": 20,
                    "startColumnIndex": 0,
                    "endcolumnIndex": 1
                },
                "pasteType": "Paste_Normal"
            }
        }
    ]
}
res = spreadsheet.batch_update(body)
print(res)

Answer 1

Your requirements:

• You want to copy the values from a sheet to other sheet in a Google Spreadsheet.
• You want to achieve this using gspread with python.
• You have already been able to get and put values for Google Spreadsheet using Google Sheets API.

In this answer, I would like to propose to use batch_update for copying the values from from a sheet to other sheet in the Spreadsheet. In this case, your goal can be achieved by one API call.

Sample script:

In this sample script, the script of authorization is removed. The sample script for copying values from a sheet to other sheet in the Spreadsheet is shown. So when you use this script, please add the authorization script, and run the script.

spreadsheetId = "###"  # Please set the Spreadsheet ID.
sourceSheetName = "Sheet1"  # Please set the sheet name of source sheet.
destinationSheetName = "Sheet2"  # Please set the sheet name of destination sheet.

client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(spreadsheetId)
sourceSheetId = spreadsheet.worksheet(sourceSheetName)._properties['sheetId']
destinationSheetId = spreadsheet.worksheet(destinationSheetName)._properties['sheetId']
body = {
    "requests": [
        {
            "copyPaste": {
                "source": {
                    "sheetId": sourceSheetId,
                    "startRowIndex": 0,
                    "endRowIndex": 5,
                    "startColumnIndex": 0,
                    "endColumnIndex": 5
                },
                "destination": {
                    "sheetId": destinationSheetId,
                    "startRowIndex": 0,
                    "endRowIndex": 5,
                    "startColumnIndex": 0,
                    "endColumnIndex": 5
                },
                "pasteType": "PASTE_VALUES"
            }
        }
    ]
}
res = spreadsheet.batch_update(body)
print(res)

Note:

• In this sample script, the values of cells of A1:E5 in the sheet of "Sheet1" are copied to the cells of A1:E5 in the sheet of "Sheet2".
• In this case, the range is given by the GridRange.
• This is a simple sample script. So please modify this for your actual situation.

References:

• batch_update
• Method: spreadsheets.batchUpdate
• CopyPasteRequest
• GridRange