Skip to content

Instantly share code, notes, and snippets.

@florentroques
Last active July 27, 2020 17:34
Show Gist options
  • Save florentroques/0f8e34a1c4186bcc6751289e57746d43 to your computer and use it in GitHub Desktop.
Save florentroques/0f8e34a1c4186bcc6751289e57746d43 to your computer and use it in GitHub Desktop.
# 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment