Syncing Zoom Attendance Reports to Google Sheets

Hey there, Darian here. A few quarters back, I was manually pulling attendance reports for our weekly engineering all-hands. It was a soul-crushing, copy-paste marathon that took up a solid hour every Monday. I finally realized I was wasting about four hours a month on a task a simple script could do in seconds. That’s what led me to this workflow.

This guide isn’t just about code; it’s about reclaiming your time. We’re going to set up a fire-and-forget system that automatically pipes Zoom attendance data directly into a Google Sheet. Let’s get that time back.

Prerequisites

Before we dive in, make sure you have the following ready. We’re moving fast, so having these set up will save you a lot of friction.

• A Zoom Pro account (or higher) with admin access to create a JWT app.
• A Google Cloud Platform (GCP) account to create service credentials.
• Python 3 installed on the machine where this script will run.
• Familiarity with installing Python packages. You’ll need to install libraries like requests, gspread, google-auth-oauthlib, and python-dotenv for this project.

The Guide: Step-by-Step

Step 1: Set Up Your Zoom JWT App

First, we need to give our script a way to talk to Zoom. The best way for a server-to-server integration like this is a JWT (JSON Web Token) app.

1. Navigate to the Zoom App Marketplace and sign in.
2. Click on ‘Develop’ in the top-right corner and select ‘Build App’.
3. Choose ‘JWT’ as the app type and give it a name, something like “GSheets_Attendance_Sync”.
4. Fill in the basic app information. It’s for internal use, so you don’t need to go crazy here.
5. Once created, head to the ‘App Credentials’ tab. You’ll see your API Key and API Secret. Copy these somewhere safe; we’ll need them for our configuration file.
6. Set an expiration time for the token. I usually set it to the maximum for simplicity, but for higher security, you could rotate it.

Step 2: Configure Google Cloud & Service Account

Next, let’s get the Google side ready. We need a service account, which is like a robot user that our script can use to access Google Sheets on our behalf.

1. Go to the Google Cloud Console and create a new project.
2. In your project, navigate to ‘APIs & Services’ > ‘Library’. Search for and enable both the Google Drive API and the Google Sheets API.
3. Now, go to ‘APIs & Services’ > ‘Credentials’. Click ‘Create Credentials’ and select ‘Service account’.
4. Give the service account a name (e.g., “zoom-sheets-writer”) and grant it the ‘Editor’ role for now. You can refine permissions later.
5. After creating the account, find it in the list, click on it, go to the ‘KEYS’ tab, and select ‘Add Key’ > ‘Create new key’. Choose ‘JSON’ and a file will download. This is your golden ticket. Rename it to credentials.json and keep it secure with your project files.
6. Crucial Final Step: Open the Google Sheet you want to write to. Click the ‘Share’ button and share it with the service account’s email address (you can find this email in the service account details in GCP). If you skip this, you’ll get a ‘Permission Denied’ error.

Step 3: The Python Script

Alright, time for the logic. I’ll skip the standard virtualenv setup since you likely have your own workflow for that. Just make sure you’ve installed the necessary libraries I mentioned earlier. We’ll create two files: a configuration file for our secrets and the main Python script.

First, create a file named config.env. This keeps our sensitive keys out of the main script.

ZOOM_API_KEY=your_zoom_api_key_here
ZOOM_API_SECRET=your_zoom_api_secret_here
ZOOM_USER_ID=your_zoom_user_email_or_id
GOOGLE_SHEET_NAME=Your Target Google Sheet Name

Now for the main event, our Python script. I’ll call it sync_script.py. I’ve broken it down with comments to explain what each part is doing.

import os
import requests
import gspread
import jwt
import time
from datetime import datetime, timedelta
from google.oauth2.service_account import Credentials
from dotenv import load_dotenv

# Load environment variables from config.env
load_dotenv('config.env')

# --- Configuration ---
ZOOM_API_KEY = os.getenv('ZOOM_API_KEY')
ZOOM_API_SECRET = os.getenv('ZOOM_API_SECRET')
ZOOM_USER_ID = os.getenv('ZOOM_USER_ID')
GOOGLE_SHEET_NAME = os.getenv('GOOGLE_SHEET_NAME')
GOOGLE_CREDS_FILE = 'credentials.json'

# --- Google Sheets Authentication ---
def get_gspread_client():
    """Authenticates with Google and returns a gspread client."""
    scopes = [
        'https://www.googleapis.com/auth/spreadsheets',
        'https://www.googleapis.com/auth/drive'
    ]
    creds = Credentials.from_service_account_file(GOOGLE_CREDS_FILE, scopes=scopes)
    client = gspread.authorize(creds)
    return client

# --- Zoom API Functions ---
def generate_zoom_jwt():
    """Generates a JWT for Zoom API authentication."""
    payload = {
        'iss': ZOOM_API_KEY,
        'exp': time.time() + 3600  # Token is valid for 1 hour
    }
    token = jwt.encode(payload, ZOOM_API_SECRET, algorithm='HS256')
    return token

def get_past_meetings(jwt_token):
    """Fetches meetings from the last 7 days for the specified user."""
    headers = {'Authorization': f'Bearer {jwt_token}'}
    # Look for meetings that ended in the last 7 days
    yesterday = (datetime.utcnow() - timedelta(days=7)).strftime('%Y-%m-%d')
    
    # URL for past meetings
    url = f"https://api.zoom.us/v2/report/users/{ZOOM_USER_ID}/meetings?from={yesterday}&page_size=100"
    
    response = requests.get(url, headers=headers)
    response.raise_for_status()  # This will raise an error for non-200 responses
    return response.json().get('meetings', [])

def get_meeting_participants(meeting_uuid, jwt_token):
    """Fetches the list of participants for a given meeting UUID."""
    headers = {'Authorization': f'Bearer {jwt_token}'}
    # Note: Double URL encoding is needed for the UUID if it has slashes
    encoded_uuid = requests.utils.quote(requests.utils.quote(meeting_uuid, safe=''))
    url = f"https://api.zoom.us/v2/report/meetings/{encoded_uuid}/participants?page_size=300"
    
    response = requests.get(url, headers=headers)
    response.raise_for_status()
    return response.json().get('participants', [])

# --- Main Logic ---
def main():
    print("Starting Zoom to Google Sheets sync...")
    
    try:
        # Authenticate with Google Sheets
        gspread_client = get_gspread_client()
        spreadsheet = gspread_client.open(GOOGLE_SHEET_NAME)
        # Use the first worksheet by default, or create one if it doesn't exist
        try:
            worksheet = spreadsheet.worksheet("Attendance Log")
        except gspread.WorksheetNotFound:
            worksheet = spreadsheet.add_worksheet(title="Attendance Log", rows="1000", cols="20")
            # Set headers for the new sheet
            worksheet.append_row(["Meeting Topic", "Meeting Start Time", "Participant Name", "Join Time", "Leave Time", "Duration (Minutes)"])

        # Authenticate with Zoom
        zoom_token = generate_zoom_jwt()
        
        # Get recent meetings
        meetings = get_past_meetings(zoom_token)
        if not meetings:
            print("No recent meetings found.")
            return

        print(f"Found {len(meetings)} recent meetings. Processing...")
        
        all_participants_data = []
        for meeting in meetings:
            meeting_uuid = meeting['uuid']
            meeting_topic = meeting['topic']
            meeting_start_time = meeting['start_time']
            
            print(f"  Fetching participants for '{meeting_topic}'...")
            participants = get_meeting_participants(meeting_uuid, zoom_token)
            
            for p in participants:
                # Format data for the sheet
                row_data = [
                    meeting_topic,
                    meeting_start_time,
                    p.get('name', 'N/A'),
                    p.get('join_time', 'N/A'),
                    p.get('leave_time', 'N/A'),
                    p.get('duration', 0) // 60 # Convert seconds to minutes
                ]
                all_participants_data.append(row_data)

        if all_participants_data:
            # In a real scenario, you'd want to avoid duplicates. 
            # For this tutorial, we'll just append.
            print(f"Appending {len(all_participants_data)} new records to the sheet.")
            worksheet.append_rows(all_participants_data, value_input_option='USER_ENTERED')
        else:
            print("No new participant data to add.")

        print("Sync complete.")

    except requests.exceptions.HTTPError as e:
        print(f"An API error occurred: {e.response.status_code} - {e.response.text}")
    except Exception as e:
        print(f"An unexpected error occurred: {e}")

if __name__ == "__main__":
    main()

Pro Tip: The script above appends all participant data it finds. In my production setups, I usually fetch the existing data from the sheet first and create a unique key (like `meeting_uuid + participant_email`) to check for duplicates before writing. This prevents the same attendance log from being added every time the script runs.

Step 4: Automate with a Cron Job

Finally, let’s make this run automatically. A cron job is perfect for this. We can set it to run once a day or once a week. To edit your cron jobs, you’d typically use the command to open the crontab file for editing.

Here’s a cron expression to run the script every Monday at 2 AM. Make sure you are in the directory of the script when you run it, or provide an absolute path to the script itself.

0 2 * * 1 python3 sync_script.py

This simple line will handle the execution for you, ensuring your report is always up-to-date without any manual intervention.

Common Pitfalls (Where I Usually Mess Up)

• Google Sheet Not Shared: The number one error is forgetting to share the Google Sheet with the service account’s email. You will get a `gspread.exceptions.SpreadsheetNotFound` or a 403 permission error. Double-check that share setting!
• Zoom UUID Encoding: Some Zoom meeting UUIDs contain a ‘/’ character, which will break the API URL if not properly encoded. Notice the `requests.utils.quote` call in my code—it’s there specifically to handle this.
• Timezone Mismatches: The Zoom API returns all times in UTC. When you’re debugging or comparing times, make sure you’re aware of this, or you’ll be pulling your hair out wondering why the meetings from “today” aren’t showing up.

Conclusion

And that’s it. You’ve now built a robust, automated pipeline that takes a tedious manual task off your plate. This same pattern—API to API with a Python script as the glue—can be applied to countless other problems. The initial setup takes a bit of time, but the long-term payoff is huge. Now, go enjoy that extra hour you just saved.

– Darian Vance

Darian Vance

Lead Cloud Architect & DevOps Strategist

With over 12 years in system architecture and automation, Darian specializes in simplifying complex cloud infrastructures. An advocate for open-source solutions, he founded TechResolve to provide engineers with actionable, battle-tested troubleshooting guides and robust software alternatives.

🤖 Frequently Asked Questions