Package downloads, appends, duplicates from template, or replaces a Google Sheet with a pandas dataframe. A dictionary is used to determine the sheet(s) to work on.
How To Use
1. Install the module using pip:pip install drive-dataframe-uploader
-
Import the module & instantiate the GoogleDrivePython class:
import drive_dataframe_uploader as ddu dl = ddu.DataFrameLoader()
-
Store the full path to your google service account in a variable:
service_account = '/Users/luyanda.dhlamini/Projects/client_secret.json'
-
Create a dictionary with the Google Spreadsheet sheet name & doc key to work on:
# Dictionary format: { sheet_name_string : { 'doc_key' : spread_sheet_id_string } } sheet_name = 'Sheet1' # This is the name of the spreadsheet sheet to work on. doc_key = '1ZqLUWuANFa8rCRF2tpIgCNl-IWsmw2MKWxPVpmfUM0s' # The document id found in the spreadsheet's link. data_dictionary = { sheet_name : { 'doc_key' : doc_key } }
-
Use the get_existing() function to download the data from a Spreadsheet into a pandas DataFrame:
# Create & save a text file into the local directory existing_data_dictionary = dl.get_existing( data_dictionary = data_dictionary, service_account_json_path = service_account) existing_df = existing_data_dictionary[sheet_name]['dataframe'] print( existing_df.head() ) # Change the first value of the downloaded dataframe. existing_df.at[ 0, existing_df.columns[0] ] = 'Changed' # Store the dictionary in the data_dictionary data_dictionary[sheet_name]['dataframe'] = existing_df # Print the data_dictionary to assess if the dataframe has been stores in the data_dictionary print( data_dictionary )
-
Use the update_data_overwrite() function to upload & overwrite the data in a Spreadsheet using the data_dictionary created above. All tabular data contained in the target sheet is deleted:
# Pass in the data_dictionary created above to overwrite data in the existing Sheet1. # If the passed in sheet cannot be found, a new sheet is created. data_dictionary2 = dl.update_data_overwrite( data_dictionary = data_dictionary, service_account_json_path = service_account)
-
Use the update_data_append() function to upload & append to the data in a Spreadsheet using the data_dictionary created above. All tabular data contained in the target sheet is first downloaded, empty rows & columns are removed. New data is concated vertically to existing data:
# Pass in the data_dictionary created above to append to data in the existing Sheet1. # If the passed in sheet cannot be found, a new sheet is created. data_dictionary3 = dl.update_data_append( data_dictionary = data_dictionary, service_account_json_path = service_account)