Use Google Sheet as BigQuery Dataset

README.md

Generating BQ schema from google sheet header row

To generate a new schema:

• Copy the ID header row from your google sheet

• pbpaste | python make_schema.py

• There's your BQ schema!

• Add a new dataset to bigquery

  • Use your spreadsheet link as the file location
  • Paste your schema in the box after clicking "Edit as Text"

make_bq_schema.py

import sys
import re

txt = sys.stdin.read()

headers = txt.split('\t')

print ','.join([
    '{}:STRING'.format(re.sub(r'_+', '_', re.sub(r'[^a-z]', '_', h.lower())))
    for h in headers
    if h
])