json2gsheet.py

import os
import json
import pygsheets
import pandas as pd
from pandas.io.json import json_normalize

OAUTHFILE = ""  # path to the credential file

def read_json_files(path):
  """Reads multiple JSON files from the given path
     and make the necessary changes in them 
  """
    json_data = []
    for file in os.walk(path):
        json_files = file[2]

    for file in json_files:
        json_path = os.path.join(path, file)
        with open(json_path,  'r') as f:
            try:
                data = json.load(f)
                data['eligible_depts'] = ', '.join(data['eligible_depts'])
                json_data.append(data)
            except json.decoder.JSONDecodeError:
                print(json_path)

    return json_data

def main(path):
    gc = pygsheets.authorize(service_file=OAUTHFILE)
    json_data = read_json_files(path)
    df = pd.DataFrame.from_dict(json_normalize(json_data), orient='columns')
    sh = gc.open_by_key(KEY)
    wks = sh.sheet1
    wks.set_dataframe(df, (1, 1), fit=True)
    print(wks.get_col(1))


if __name__ == "__main__":
    path = "" # path to json folders
    main(path)