Skip to content
Connect to Google Sheets
  • AI Chat
  • Code
  • Report

    • Connect to Google Sheets

    This workspace contains example code snippets and instructions for connecting to data in Google sheets from inside Workspace. For a detailed walkthrough, you can consult this comprehensive tutorial on connecting to Google Sheets.

    Read Google Sheets data

    Install and import the right package

    %%capture
!pip install gspread
import pandas as pd
import gspread
from google.oauth2 import service_account
import json
import os

    Set up a client with scoped credentials

    For this code to work, you need to have an environment variable GOOGLE_JSON available in your workspace that contains a Google service account JSON with the right permissions. The steps to configure such a service account and securely store it in Workspace are described in this documentation article.

    google_json = os.environ["GOOGLE_JSON"]
service_account_info = json.loads(google_json)
credentials = service_account.Credentials.from_service_account_info(service_account_info)
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds_with_scope = credentials.with_scopes(scope)
client = gspread.authorize(creds_with_scope)

    Read spreadsheets data into a DataFrame

    Make sure to update the spreadsheet URL with the URL of your own spreadsheet!

    spreadsheet = client.open_by_url('https://docs.google.com/spreadsheets/d/1yjDGNBg6nZkQVpkmiY3iz0H5xggDxPFBh2HEPHIcGj8/edit#gid=712927798')
worksheet = spreadsheet.get_worksheet(0)
records_data = worksheet.get_all_records()
records_df = pd.DataFrame.from_dict(records_data)
records_df

    Analyze the spreadsheets data in Python

    median_funding_df = records_df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df

    Write results to Google Sheets

    # Convert the median_funding_df DataFrame to a list of lists
medians_list = median_funding_df.values.tolist()

# Add the header row to the list
medians_list.insert(0, median_funding_df.columns.tolist())

# Check if the 'median_by_country' sheet already exists
sheet_titles = [sheet.title for sheet in spreadsheet.worksheets()]
sheet_name = "median_by_country"
if sheet_name not in sheet_titles:
    # Create a new sheet named 'median_by_country' in the Google Sheet
    medians_sheet = spreadsheet.add_worksheet(title=sheet_name, rows=len(medians_list), cols=len(medians_list[0]))
else:
    medians_sheet = spreadsheet.worksheet(sheet_name)

# Write the medians_list to the new sheet
medians_sheet.clear()
medians_sheet.insert_rows(medians_list, row=1)

print("Done!")