How Do You Copy Values From One Spreadsheet To Another Using Gspread Or Some Other Way?

April 21, 2024 Post a Comment

(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 spreads

Solution 1:

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.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

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:

If I misunderstood your question and this was not the direction you want, I apologize.

Solution 2:

1) Import openpyxl library as xl.

2) Open the source excel file using the path in which it is located.

Note: The path should be a string and have double backslashes (\) instead of single backslash (). Eg: Path should be C:\Users\Desktop\source.xlsx Instead of C:\Users\Admin\Desktop\source.xlsx

3) Open the required worksheet to copy using the index of it. The index of worksheet ‘n’ is ‘n-1’. For example, the index of worksheet 1 is 0.

4) Open the destination excel file and the active worksheet in it.

5) Calculate the total number of rows and columns in source excel file.

6) Use two for loops (one for iterating through rows and another for iterating through columns of the excel file) to read the cell value in source file to a variable and then write it to a cell in destination file from that variable.

7) Save the destination file.

here is a py only code example from How to copy over an Excel sheet to another workbook in Python:

    import openpyxl as xl

path1 = 'C:\\Users\\Xukrao\\Desktop\\workbook1.xlsx'
path2 = 'C:\\Users\\Xukrao\\Desktop\\workbook2.xlsx'

wb1 = xl.load_workbook(filename=path1)
ws1 = wb1.worksheets[0]

wb2 = xl.load_workbook(filename=path2)
ws2 = wb2.create_sheet(ws1.title)

for row in ws1:
    for cell in row:
        ws2[cell.coordinate].value = cell.value

wb2.save(path2)

Solution 3:

Using only gspread

For copying the whole sheet to another spreadsheet:

importgspreadclient= gspread.authorize(<put here credentials>)

source_gsheet_url = '<put here url>'
source_worksheet_name = '<put here name>'
dest_gsheet_url = '<put here url>'

ws = gc.open_by_url(source_gsheet_url).worksheet(source_worksheet_name)
ws.copy_to(dest_gsheet_url)

Python Freelancers