export_gsheet_worksheets_to_json.py

# 1. Install Python
# Linux: apt-get install python3 (python should already be installed)
# MacOS: brew install python3 (check homebrew if you do not have it yet)
# Windows: download Python from Microsoft Store (you can try with chocolatey package manager also)
# 2. Install PIP, Python package manager
# Linux: apt-get install pip3
# MacOS: brew install pip3 (should be installed with Python3 normally, run it in any case)
# Windows: 
# 3. Install script dependencies
# pip3 install pygsheets pandas

import pygsheets
import pandas as pd
from os import path, devnull, remove


GOOGLE_SHEETS_FILE_ID = 'to be filled in'

google_client = pygsheets.authorize()
spreadsheet = google_client.open_by_key(GOOGLE_SHEETS_FILE_ID)
wks_list = spreadsheet.worksheets()
dirname = path.dirname(__file__)

for wks in wks_list:
    fname = wks.title + '.tsv'

    tsvfpath = path.join(dirname, './data/' + wks.title + '.tsv')
    jsonfpath = tsvfpath.replace('.tsv', '.json')

    if (path.exists(jsonfpath)):
        continue
    
    #saving worksheet as .tsv file in data folder
    wks.export(
    	pygsheets.ExportType.TSV,
    	path=path.join(dirname, './data/'),
    	filename=wks.title
	)

    data_frame = pd.read_csv(tsvfpath, sep='\t')
    data_frame.to_json(tsvfpath.replace('.tsv', '.json'),
                       orient='records', indent=2)
    remove(tsvfpath)