Search code examples
pythonapigoogle-sheetsgoogle-sheets-apiworksheet

Google Sheets API for Python: Empty charts when duplicating a worksheet

Suppose I have two spreadsheets of almost the same structure:

Spreadsheet A: [Sheet1(Charts and data summary)][Sheet2 (Data)][Sheet3(Data)]...

Spreadsheet B: [Sheet1(Empty)][Sheet2 (Data)][Sheet3(Data)]...

A.Sheet1 contains manually created charts and aggregated data from A.Sheet2 and A.Sheet3.

Spreadsheet B has the same worksheet ordering and worksheet names as in Spreadsheet A. The task is to copy Sheet1 from A to B so that all charts and formulas should be mapped to the data from corresponding worksheets of B of the same name.

So I'm using the following code to copy entire Sheet1 from A to B:

def duplicate_worksheet(from_spreadsheet_id,
                        sheet_id,
                        to_spreadsheet_id,
                        credentials,
                        title=None):

    service = discovery.build('sheets', 'v4', credentials=credentials)

    copy_sheet_to_another_spreadsheet_request_body = {
        'destination_spreadsheet_id': to_spreadsheet_id
    }
    request = service.spreadsheets().sheets().copyTo(spreadsheetId=from_spreadsheet_id,
                                                     sheetId=sheet_id,
                                                     body=copy_sheet_to_another_spreadsheet_request_body)

    response = request.execute()
    if title:
        new_sheet_id = response['sheetId']
        new_title = title
        rename_worksheet(to_spreadsheet_id, new_sheet_id, new_title, credentials)

After copying, B.Sheet1 looks like this: enter image description here

Here we can see that aggregated tables copied successfully but 2 charts are empty (Instead, No data message is shown]). The spreadsheet Itself is missing all specified chart data ranges and links, which are supposed to persist after copying from A. So my question is how to "deepcopy" a worksheet and keep all chart data and links? (Of course in case If It's possible by using Google API)

Solution

  • The chart is being copied empty, due to the Method: spreadsheets.sheets.copyTo endpoint only copies one sheet to the other spreadsheet, therefore if one chart is built using data from another sheet, it will lose it.

    As a workaround, you could edit the empty chart's properties with the properties of your original chart, so it looks as it were a direct copy of it as you are desiring.

    You will need to use the Method: spreadsheets.batchUpdate endpoint. In Python your request will look like this:

    body = {
    'requests': requests
}
response = service.spreadsheets().batchUpdate(
    spreadsheetId=spreadsheet_id,
    body=body).execute()