Source Code: Combine Google Sheets worksheets using Google Sheets API in Python

Buy Me a Coffee? Your support is much appreciated!

PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn

Source Code: 

from Google import Create_Service

def run_batchUpdate_request(service, google_sheet_id, request_body_json):
    try:
        response = service.spreadsheets().batchUpdate(
            spreadsheetId=google_sheet_id,
            body=request_body_json
        ).execute()
        return response
    except Exception as e:
        print(e)
        return None

CLIENT_SECRET_FILE = '<CLIENT FILE DIR>'
API_SERVICE_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
GOOGLE_SHEET_ID = '<GOOGLE SHEETS ID>'

service = Create_Service(CLIENT_SECRET_FILE, API_SERVICE_NAME, API_VERSION, SCOPES)

gsheets = service.spreadsheets().get(spreadsheetId=GOOGLE_SHEET_ID).execute()
sheets = gsheets.get('sheets')

"""
Deleter master worksheet
"""
master_sheet_id = [sheet['properties']['sheetId'] for sheet in sheets if sheet['properties']['title'] == 'master']
if master_sheet_id:
    delete_sheet_request = {
        'requests': [
            {
                'deleteSheet': {
                    'sheetId': master_sheet_id[0]
                }
            }
        ]
    }
    run_batchUpdate_request(service, GOOGLE_SHEET_ID, delete_sheet_request)

"""
Create master sheet
"""    
add_master_sheet_request = {
    'requests': [
        {
            'addSheet': {
                'properties': {
                    'title': 'master'
                }
            }
        }
    ]
}

run_batchUpdate_request(service, GOOGLE_SHEET_ID, add_master_sheet_request)

"""
To combine datasets from worksheets
"""
rows = []

for sheet in sheets:
    if sheet['properties']['title'] != 'master':
        dataset = service.spreadsheets().values().get(
            spreadsheetId=GOOGLE_SHEET_ID,
            range=sheet['properties']['title'],
            majorDimension='ROWS'
        ).execute()
        rows.extend(dataset['values'][1:])

columns = dataset['values'][0]
rows.insert(0, columns)

service.spreadsheets().values().update(
    spreadsheetId=GOOGLE_SHEET_ID,
    valueInputOption='USER_ENTERED',
    range='master!A1',
    body={'majorDimension': 'ROWS', 'values': rows}
).execute()