gspread-dataframe

import gspread import pandas as pd from gspread_dataframe import get_as_dataframe, set_with_dataframe import os # --- Gspread Authentication (replace with your actual setup) --- # For service account authentication, ensure 'service_account.json' is in your path # and shared with the client_email in that file. # For a runnable example, we'll mock gspread client/worksheet objects. # In a real application, you would use: # gc = gspread.service_account(filename=os.environ.get('GOOGLE_APPLICATION_CREDENTIALS')) # spreadsheet = gc.open('Your Spreadsheet Name') # worksheet = spreadsheet.worksheet('Sheet1') class MockWorksheet: def __init__(self, data=None): self._values = [list(row) for row in data] if data else [] def get_all_values(self): return self._values def update(self, range_name, values): # Simple mock update for demonstration if range_name == 'A1': # Assume A1 starts the update for r_idx, row in enumerate(values): if r_idx < len(self._values): for c_idx, val in enumerate(row): if c_idx < len(self._values[r_idx]): self._values[r_idx][c_idx] = val else: self._values[r_idx].append(val) else: self._values.append(list(row)) # Create a mock worksheet with some initial data mock_data = [ ['Name', 'Age', 'City'], ['Alice', '30', 'New York'], ['Bob', '24', 'London'], ['Charlie', '35', 'Paris'] ] worksheet = MockWorksheet(mock_data) # Read worksheet into a DataFrame df = get_as_dataframe(worksheet) print("DataFrame from Worksheet:") print(df) # Modify the DataFrame df['Age'] = df['Age'].astype(int) + 1 df['Country'] = ['USA', 'UK', 'France'] # Write DataFrame back to worksheet # Note: resize=True will clear existing data and resize the sheet. # include_index=False by default. set_with_dataframe(worksheet, df, resize=True, include_column_header=True) print("\nUpdated Worksheet (mocked values):") print(worksheet.get_all_values()) # Example of reading with pandas options # df_parsed = get_as_dataframe(worksheet, parse_dates=['birth_date'], skiprows=1, header=None) # print(df_parsed)