Search code examples
pythongoogle-sheetsrequestgoogle-sheets-apigspread

Python - GSPREAD - Copy text and format from one google sheet to another one

I have two files in google drive (two google sheets) and I need to move from one file to another the data and the format in a regular mode.

As you can see on the picture bellow, I have different text formats that I need to maintain (bold, text color, etc.):

enter image description here

My first attempt was: Using only google drive sheet functions using IMPORTRANGE. It copies the data very well but I loose the format that I want to mantain on the destination file.

My second attemp has been: Using Python and gspread package copy the data and the format from source google sheet to the destination one. Fo that I have the following code:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
   
source_file_sheet = 'https://docs.google.com/spreadsheets/X'
destination_file_sheet = 'https://docs.google.com/spreadsheets/Y'
service_key = "file.json"
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_file = ServiceAccountCredentials.from_json_keyfile_name(service_key, scope)
sourceSheetName = cod_source_system_file_operational 
destinationSheetName = cod_source_system_file_billing 

client = gspread.authorize(creds_file)
spreadsheet_source = client.open_by_url(source_file_sheet)
spreadsheet_destination = client.open_by_url(destination_file_sheet)
sourceSheetId = spreadsheet_source.worksheet('Sheet1')
destinationSheetId = spreadsheet_destination.worksheet('Sheet2')
body = {
    "requests": [
        {
            "copyPaste": {
                "source": {
                    "sheetId": sourceSheetId,
                    "startRowIndex": 3,
                    "endRowIndex": 10,
                    "startColumnIndex": 0,
                    "endColumnIndex": 5
                },
                "destination": {
                    "sheetId": destinationSheetId,
                    "startRowIndex": 0,
                    "endRowIndex": 10,
                    "startColumnIndex": 0,
                    "endColumnIndex": 5
                },
                "pasteType": "PASTE_NORMAL"
            }
        }
    ]
}
res = destinationSheetId.batch_update(body)
print(res)

But when I run this it gives me the following error:

Traceback (most recent call last):

dict(vr, range=absolute_range_name(self.title, vr['range']))
TypeError: string indices must be integers

How can I solve my problem?

Thanks for your help!

Solution

  • I believe your goal and your current situation as follows.

    • You want to copy the values of the specific sheet in Google Spreadsheet "A" to the specific sheet in Google Spreadsheet "B".
    • You want to copy not only the values, but also the cell format.
    • You want to achieve this using gspread for python.
    • You have already been able to get and put values for Google Spreadsheet using Sheets API.

    Modification points:

    • Unfortunately, "CopyPasteRequest" of the batchUpdate method cannot copy from Google Spreadsheet to other Google Spreadsheet. It seems that this is the current specification.

    • In order to copy not only the values, but also the cell format from Google Spreadsheet "A" to google Spreadsheet "B", I would like to propose the following flow.

      1. Copy the source sheet in the source Spreadsheet to the destination Spreadsheet.
      2. Copy the values with the format from the copied sheet to the destination sheet. And, delete the copied sheet.

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

    Sample script:

    In this sample script, I prepared the script below client = gspread.authorize(credentials) as follows. Before you use this, please set the variables.

    client = gspread.authorize(credentials)

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

srcSpreadsheet = client.open_by_key(sourceSpreadsheetId)
srcSheet = srcSpreadsheet.worksheet(sourceSheetName)
dstSpreadsheet = client.open_by_key(destinationSpreadsheetId)
dstSheet = dstSpreadsheet.worksheet(destinationSheetName)

# 1. Copy the source sheet in the source Spreadsheet to the destination Spreadsheet.
copiedSheet = srcSheet.copy_to(destinationSpreadsheetId)
copiedSheetId = copiedSheet["sheetId"]

# 2. Copy the values with the format from the copied sheet to the destination sheet. And, delete the copied sheet.
body = {
    "requests": [
        {
            "copyPaste": {
                "source": {
                    "sheetId": copiedSheetId,
                    "startRowIndex": 3,
                    "endRowIndex": 10,
                    "startColumnIndex": 0,
                    "endColumnIndex": 5
                },
                "destination": {
                    "sheetId": dstSheet.id,
                    "startRowIndex": 0,
                    "endRowIndex": 10,
                    "startColumnIndex": 0,
                    "endColumnIndex": 5
                },
                "pasteType": "PASTE_NORMAL"
            }
        },
        {
            "deleteSheet": {
                "sheetId": copiedSheetId
            }
        }
    ]
}
res = dstSpreadsheet.batch_update(body)
print(res)

    References: