I have a Google Worksheet with which I'm doing pivot tables. I would like to add pivot tables(14) in new sheets that don’t exist yet (One pivot table by sheet), on the same spreadsheet. Currently I'm creating new sheets and recovering their IDs by hand. I wish it was automatic. Here my pivot table :

    request_body = {
        "requests" : [
            {
                "addSheet": {
                    'properties': {
                        'title': 'test',
                        'tabColor' : {
                            'red' : 0.44,
                            'green' : 0.99,
                            'blue' : 0.50
                        },
                    }
                },
            }
        ],
        "sheets":[{
            'data': [{
                "rowData" : {
                    "values" : [
                        {
                            "pivotTable" : {
                                #Data Source
                                "source" : {
                                    "sheetId": "0",
                                    "startRowIndex" : 0,
                                    "startColumnIndex" : 0,
                                },
                                #Row Field(s)
                                "rows" : [ 
                                    #field 2
                                    {
                                        "sourceColumnOffset" : 5, #Type Preneur
                                        "showTotals" : False,
                                        "sortOrder" : "ASCENDING"
                                    },
                                    #field 3
                                    {
                                        "sourceColumnOffset" : 3, #Enseigne
                                        "showTotals" : False,
                                        "sortOrder" : "ASCENDING"
                                    }
                                ],
                                #Columns Field(s)
                                "columns" : [
                                    #Field 1
                                    {
                                        "sourceColumnOffset" : 6, #Nature impayés
                                        "sortOrder" : "ASCENDING", 
                                        "showTotals" : False
                                    },
                                    #Field 2
                                    {
                                        "sourceColumnOffset" : 18, #Année
                                        "sortOrder" : "ASCENDING",
                                        "showTotals" : True
                                    },
                                    #Field 3
                                    {
                                        "sourceColumnOffset" : 19, #Item - Entry Type
                                        "sortOrder" : "ASCENDING",
                                        "showTotals" : False  
                                    }
                                ],
                                "criteria" : {
                                    6:{
                                        'visibleValues' : ['Solde Créditeur']
                                    },
                                    19:{
                                        'visibleValues' : [
                                            'DEPRD', 'DEPRE', 'ECHEA', 'FACTU'
                                        ]
                                    },
                                    5: {
                                        'visibleValues' : [
                                            "Inconnu", "Indépendant","Antenne / Wifi", "SL", "Franchisé", "Ephémère", "Copro", "Carrefour", "-"
                                        ]
                                    },
                                },                                        
                                #Values Field(s)
                                "values" : [
                                    {
                                        "sourceColumnOffset" : 21, #PNS BRUT PPSO
                                        "summarizeFunction" : "SUM",
                                        "name" : ""
                                    }
                                ],
                                "valueLayout" : "HORIZONTAL"
                            }
                        }
                    ],
                    'dataSourceId' : id_fichier
                },
                'fields' : 'pivotTable'
            }]
        }]
    }

    #Ecriture du 1er TCD
    response = service_sheet.spreadsheets().batchUpdate(
        spreadsheetId = Id_fichier_source,
        body = request_body
    ).execute()

Do you have any ideas ?

CodePudding user response：

You can easily create a new spreadsheet using the spreadsheets().create() method.

An example:

spreadsheet = {'properties': {'title': "Testing-01"}}
spreadsheet = ss_service.spreadsheets().create(body=spreadsheet,
                                            fields='spreadsheetId').execute()
ss_id = spreadsheet.get('spreadsheetId')
print('Spreadsheet ID: {0}'.format(ss_id))

After that you simply have to add the generated ID to your function

Update

You can add a new sheet to an existing Spreadsheet using the addSheets request. To note, you set its own ID so that it can then be referenced later.

So, the steps will be:

1. Create and take the ID of the Spreadsheets.
2. Create with the AddSheet method all the new tabs that are needed. Set an ID for each new sheet added.
3. Set these IDs inside updateCells > range > sheetID.

For reference, you can use these examples

Documentation:

• Google Sheets API PyDoc