In this tutorial, I am going to show you how to automate Pivot Table in Google Sheets with Google Sheets API in Python. Since there are many things you can do with a Pivot Table, I will divide the tutorial into multiple parts.
What is a Pivot Table?
A Pivot Table is a tool to summarize data in a spreadsheet application. Using a Pivot Table, we will be able to simplify the reporting process to extract useful information from a dataset and keep the information organized.
Lesson 1. Create a Pivot Table using Python
Useful Link: Pivot Table JSON Representation Template
Source Code:
from Google import Create_Service
CLIENT_SECRET_FILE = 'client-secret.json'
API_SERVICE_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
service = Create_Service(CLIENT_SECRET_FILE, API_SERVICE_NAME, API_VERSION, SCOPES)
spreadsheet_id = '<Google Sheets Id>'
# PivotTable JSON Template
request_body = {
'requests': [
{
'updateCells': {
'rows': {
'values': [
{
'pivotTable': {
# Data Source
'source': {
'sheetId': '0',
'startRowIndex': 1,
'startColumnIndex': 0,
'endRowIndex': 702,
'endColumnIndex': 16 # base index is 1
},
# Rows Field(s)
'rows': [
# row field #1
{
'sourceColumnOffset': 1,
'showTotals': True, # display subtotals
'sortOrder': 'ASCENDING',
'repeatHeadings': True,
'label': 'Country List',
}
],
# Columns Field(s)
'columns': [
# column field #1
{
'sourceColumnOffset': 14,
'sortOrder': 'ASCENDING',
'showTotals': True
}
],
# Values Field(s)
'values': [
# value field #1
{
'sourceColumnOffset': 11,
'summarizeFunction': 'SUM',
'name': 'Profit Total:'
}
],
'valueLayout': 'HORIZONTAL'
}
}
]
},
'start': {
'sheetId': '469599211',
'rowIndex': 3, # 4th row
'columnIndex': 2 # 3rd column
},
'fields': 'pivotTable'
}
}
]
}
response = service.spreadsheets().batchUpdate(
spreadsheetId=spreadsheet_id,
body=request_body
).execute()
Lesson 2. Add Calculated Fields
Source Code:
from Google import Create_Service # soruce code is in the description
CLIENT_SECRET_FILE = 'client-secret.json'
API_SERVICE_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
service = Create_Service(CLIENT_SECRET_FILE, API_SERVICE_NAME, API_VERSION, SCOPES)
spreadsheet_id = '<Google Sheets Id>'
# PivotTable JSON Template
request_body = {
'requests': [
{
'updateCells': {
'rows': {
'values': [
{
'pivotTable': {
# Data Source
'source': {
'sheetId': '0',
'startRowIndex': 1,
'startColumnIndex': 0,
'endRowIndex': 702,
'endColumnIndex': 16 # base index is 1
},
# Rows Field(s)
'rows': [
# row field #1
{
'sourceColumnOffset': 1,
'showTotals': True, # display subtotals
'sortOrder': 'ASCENDING',
'repeatHeadings': True,
'label': 'Country List',
}
],
# Columns Field(s)
'columns': [
# column field #1
{
'sourceColumnOffset': 14,
'sortOrder': 'ASCENDING',
'showTotals': True
}
],
# Values Field(s)
'values': [
# value field #1
{
'sourceColumnOffset': 11,
'summarizeFunction': 'SUM',
'name': 'Profit Total:'
},
{
'summarizeFunction': 'CUSTOM',
'name': 'Discounted Sale Price',
'formula': "='Sale Price'*0.4"
},
{
'summarizeFunction': 'CUSTOM',
'name': 'Number 1 Profit Entry',
'formula': '=MAX(Profit)'
}
],
'valueLayout': 'HORIZONTAL'
}
}
]
},
'start': {
'sheetId': '469599211',
'rowIndex': 3, # 4th row
'columnIndex': 2 # 3rd column
},
'fields': 'pivotTable'
}
}
]
}
response = service.spreadsheets().batchUpdate(
spreadsheetId=spreadsheet_id,
body=request_body
).execute()
Lesson 3. Delete a Pivot Table
Source Code:
from Google import Create_Service # soruce code is in the description
CLIENT_SECRET_FILE = 'client-secret.json'
API_SERVICE_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
service = Create_Service(CLIENT_SECRET_FILE, API_SERVICE_NAME, API_VERSION, SCOPES)
spreadsheet_id = '<Google Sheets ID>'
# PivotTable JSON Template
request_body = {
'requests': [
{
'updateCells': {
'rows': {
'values': [
{}
]
},
'start': {
'sheetId': '469599211',
'rowIndex': 3, # 4th row,
'columnIndex': 2 # 3rd row
},
'fields': 'pivotTable'
}
}
]
}
response = service.spreadsheets().batchUpdate(
spreadsheetId=spreadsheet_id,
body=request_body
).execute()
Lesson 4. Add Multiple Pivot Fields
Source Code:
from Google import Create_Service # soruce code is in the description
CLIENT_SECRET_FILE = 'client-secret.json'
API_SERVICE_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
service = Create_Service(CLIENT_SECRET_FILE, API_SERVICE_NAME, API_VERSION, SCOPES)
spreadsheet_id = '<Google Sheets ID>'
# PivotTable JSON Template
request_body = {
'requests': [
{
'updateCells': {
'rows': {
'values': [
{
'pivotTable': {
# Data Source
'source': {
'sheetId': '0',
'startRowIndex': 1,
'startColumnIndex': 0,
'endRowIndex': 702,
'endColumnIndex': 16 # base index is 1
},
# Rows Field(s)
'rows': [
# row field #1
{
'sourceColumnOffset': 1,
'showTotals': True, # display subtotals
'sortOrder': 'ASCENDING',
'repeatHeadings': True,
'label': 'Country List',
},
{
'sourceColumnOffset': 2,
'showTotals': True, # display subtotals
'sortOrder': 'ASCENDING',
'repeatHeadings': True,
'label': 'Product List',
}
],
# Columns Field(s)
'columns': [
# column field #1
{
'sourceColumnOffset': 14,
'sortOrder': 'ASCENDING',
'showTotals': True
}
],
# Values Field(s)
'values': [
# value field #1
{
'sourceColumnOffset': 11,
'summarizeFunction': 'SUM',
'name': 'Profit Total:'
}
],
'valueLayout': 'HORIZONTAL'
}
}
]
},
'start': {
'sheetId': '469599211',
'rowIndex': 3, # 4th row
'columnIndex': 2 # 3rd column
},
'fields': 'pivotTable'
}
}
]
}
response = service.spreadsheets().batchUpdate(
spreadsheetId=spreadsheet_id,
body=request_body
).execute()
Lesson 5. Add Filter To Pivot Table
Source Code:
from Google import Create_Service # soruce code is in the description
CLIENT_SECRET_FILE = 'client-secret.json'
API_SERVICE_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
service = Create_Service(CLIENT_SECRET_FILE, API_SERVICE_NAME, API_VERSION, SCOPES)
spreadsheet_id = '<Google Sheets ID>'
# PivotTable JSON Template
request_body = {
'requests': [
{
'updateCells': {
'rows': {
'values': [
{
'pivotTable': {
# Data Source
'source': {
'sheetId': '0',
'startRowIndex': 1,
'startColumnIndex': 0,
'endRowIndex': 702,
'endColumnIndex': 16 # base index is 1
},
# Rows Field(s)
'rows': [
# row field #1
{
'sourceColumnOffset': 1,
'showTotals': True, # display subtotals
'sortOrder': 'ASCENDING',
'repeatHeadings': True,
'label': 'Country List',
},
{
'sourceColumnOffset': 2,
'showTotals': True, # display subtotals
'sortOrder': 'ASCENDING',
'repeatHeadings': True,
'label': 'Product List',
}
],
# Columns Field(s)
'columns': [
# column field #1
{
'sourceColumnOffset': 14,
'sortOrder': 'ASCENDING',
'showTotals': True
}
],
'criteria': {
1: {
'visibleValues': [
'Canada', 'France'
]
},
11: {
'condition': {
'type': 'NUMBER_BETWEEN',
'values': [
{
'userEnteredValue': '10000'
},
{
'userEnteredValue': '100000'
}
]
},
'visibleByDefault': True
}
},
# Values Field(s)
'values': [
# value field #1
{
'sourceColumnOffset': 11,
'summarizeFunction': 'SUM',
'name': 'Profit Total:'
}
],
'valueLayout': 'HORIZONTAL'
}
}
]
},
'start': {
'sheetId': '469599211',
'rowIndex': 3, # 4th row
'columnIndex': 2 # 3rd column
},
'fields': 'pivotTable'
}
}
]
}
response = service.spreadsheets().batchUpdate(
spreadsheetId=spreadsheet_id,
body=request_body
).execute()
Lesson 6. Collapse/Expand Pivot Field Groups
Source Code:
from Google import Create_Service # soruce code is in the description
CLIENT_SECRET_FILE = 'client-secret.json'
API_SERVICE_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
service = Create_Service(CLIENT_SECRET_FILE, API_SERVICE_NAME, API_VERSION, SCOPES)
spreadsheet_id = '<Google Sheets ID>'
# PivotTable JSON Template
request_body = {
'requests': [
{
'updateCells': {
'rows': {
'values': [
{
'pivotTable': {
# Data Source
'source': {
'sheetId': '0',
'startRowIndex': 1,
'startColumnIndex': 0,
'endRowIndex': 702,
'endColumnIndex': 16 # base index is 1
},
# Rows Field(s)
'rows': [
# Country
{
'sourceColumnOffset': 1,
'showTotals': True, # display subtotals
'sortOrder': 'ASCENDING',
'repeatHeadings': True,
'label': 'Country List',
'valueMetadata': [
{
'value': {
'stringValue': 'Germany'
},
'collapsed': True
},
{
'value': {
'stringValue': 'Canada'
},
'collapsed': True
},
]
},
# Product
{
'sourceColumnOffset': 2,
'showTotals': True, # display subtotals
'sortOrder': 'ASCENDING',
'repeatHeadings': True,
'label': 'Product List',
}
],
# Columns Field(s)
'columns': [
# Months
{
'sourceColumnOffset': 14,
'sortOrder': 'ASCENDING',
'showTotals': True,
'valueMetadata': [
{
'value': {
'stringValue': 'January'
},
'collapsed': True
},
{
'value': {
'stringValue': 'April'
},
'collapsed': True
},
{
'value': {
'stringValue': 'June'
},
'collapsed': True
}
]
},
# Segments
{
'sourceColumnOffset': 0,
'sortOrder': 'ASCENDING',
'showTotals': True,
}
],
# Values Field(s)
'values': [
# value field #1
{
'sourceColumnOffset': 11,
'summarizeFunction': 'SUM',
'name': 'Profit Total:'
}
],
'valueLayout': 'HORIZONTAL'
}
}
]
},
'start': {
'sheetId': '469599211',
'rowIndex': 3, # 4th row
'columnIndex': 2 # 3rd column
},
'fields': 'pivotTable'
}
}
]
}
response = service.spreadsheets().batchUpdate(
spreadsheetId=spreadsheet_id,
body=request_body
).execute()
Lesson 7. Extract Pivot Table Meta Data
Source Code:
import os
from pprint import pprint
from Google import Create_Service
CLIENT_SECRET_FILE = 'client-secret.json'
API_SERVICE_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
service = Create_Service(CLIENT_SECRET_FILE, API_SERVICE_NAME, API_VERSION, SCOPES)
spreadsheet_id = '<Google Sheets ID>'
# response = service.spreadsheets().get(
# spreadsheetId=spreadsheet_id,
# includeGridData=True,
# ranges='PT!C4'
# ).execute()
pt_json = {'source': {'startRowIndex': 1, 'endRowIndex': 702, 'startColumnIndex': 0, 'endColumnIndex': 16}, 'rows': [{'sourceColumnOffset': 1, 'showTotals': True, 'valueMetadata': [{'value': {'stringValue': 'Germany'}, 'collapsed': True}, {'value': {'stringValue': 'Canada'}, 'collapsed': True}], 'sortOrder': 'ASCENDING', 'repeatHeadings': True, 'label': 'Country List'}, {'sourceColumnOffset': 2, 'showTotals': True, 'sortOrder': 'ASCENDING'}], 'columns': [{'sourceColumnOffset': 14, 'showTotals': True, 'valueMetadata': [{'value': {'stringValue': 'January'}, 'collapsed': True}, {'value': {'stringValue': 'April'}, 'collapsed': True}, {'value': {'stringValue': 'June'}, 'collapsed': True}], 'sortOrder': 'ASCENDING'}, {'sourceColumnOffset': 0, 'showTotals': True, 'sortOrder': 'ASCENDING'}], 'criteria': {'14': {'visibleValues': ['April', 'July', 'June', 'March', 'May', 'November', 'October', 'September']}}, 'values': [{'sourceColumnOffset': 11, 'summarizeFunction': 'SUM', 'name': 'Profit Total:'}], 'filterSpecs': [{'columnOffsetIndex': 14, 'filterCriteria': {'visibleValues': ['April', 'July', 'June', 'March', 'May', 'November', 'October', 'September']}}]}
request_body = {
'requests': [
{
'updateCells': {
'rows': {
'values': [
{
'pivotTable': pt_json
}
]
},
'start': {
'sheetId': '469599211',
'rowIndex': 3, # 4th row
'columnIndex': 2 # 3rd column
},
'fields': 'pivotTable'
}
}
]
}
response = service.spreadsheets().batchUpdate(
spreadsheetId=spreadsheet_id,
body=request_body
).execute()
Relevant Links
Great course
Hello,
How would you collapse all pivot groups with a script when you don’t know ahead of time the number of groupings and their names?
Thank you.