Collapse all totals in a pivot table in google sheet by google sheet api and python

Viewed 2297

I created google sheet pivot table through the api in python. But, I am unable to collapse rows with totals. I just cant find solution to do that. I am using batchUpdate function to create pivottable.

Is there any possibility?

google sheets example view

Here is the code I create to do the pivot table.

    def create_pivot_table(self):
    spreadsheet = self.google_drive.SHEETS.spreadsheets()   
    #result = sheet.values().get(spreadsheetId=self.file_id, range='Sheet1!B1:B10').execute()
    requests = []
    # Change the spreadsheet's title.
    # [START sheets_pivot_tables]
    requests.append({
        'updateCells': {
            'rows': {
                'values': [
                    {
                        'pivotTable': {
                            'source': {
                                'sheetId': self._get_sheet_id_by_name('Sheet1'),
                                'startRowIndex': 0,
                                'startColumnIndex': 0,
                            },
                            'rows': [
                                {
                                    'sourceColumnOffset': 8,
                                    'showTotals': True,
                                    'sortOrder': 'ASCENDING'
                                },
                                {
                                    'sourceColumnOffset': 1,
                                    'sortOrder': 'ASCENDING',
                                    'showTotals': True,
                                }
                            ],
                            'values': [
                                {
                                    'summarizeFunction': 'SUM',
                                    'sourceColumnOffset': 16
                                },
                                                                    {
                                    'summarizeFunction': 'SUM',
                                    'sourceColumnOffset': 17
                                },
                                                                    {
                                    'summarizeFunction': 'SUM',
                                    'sourceColumnOffset': 18
                                }
                            ],
                            'valueLayout': 'HORIZONTAL'
                        }
                    }
                ]
            },
            'start': {
                'sheetId': self._get_sheet_id_by_name('PivotTable'),
                'rowIndex': 0,
                'columnIndex': 0
            },
            'fields': 'pivotTable'
        }
    })

    body = {
        'requests': requests
    }

    response = spreadsheet.batchUpdate(spreadsheetId=self.file_id, body=body).execute()
    return response
1 Answers

Check for PivotGroupValueMetadata :

collapsed boolean

True if the data corresponding to the value is collapsed.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/pivot-tables#pivotgroupvaluemetadata

And check :

https://developers.google.com/sheets/api/samples/pivot-tables#edit_pivot_table_columns_and_rows

Collapses the column for each Region, with the exception of "West", hiding the Salesperson group for that region. This is done by setting collapsed to true in the valueMetadata for that column in the Region column group.

Related