Exporting Reddit Saved Posts to a Google Sheet

Hey there, Darian Vance here. Let’s talk about a real-world problem I ran into. For years, my ‘Saved’ section on Reddit was a black hole of interesting articles, useful code snippets, and links I’d never find again. It was a goldmine of information with zero organization. I finally got fed up with the endless scrolling and built this little pipeline to bring some order to the chaos. Now, all my saved posts get automatically exported to a searchable, sortable Google Sheet. It’s saved me countless hours, and today I’m going to walk you through how I set it up.

Prerequisites

• A Reddit account (obviously).
• A Google Cloud Platform (GCP) account.
• Python 3 installed on your system.
• Familiarity with installing Python packages. You’ll need praw, gspread, and python-dotenv.

The Guide: Step-by-Step

Step 1: Configure the Reddit API

First, we need to tell Reddit that our script is allowed to access our account data. This is done by creating a “script” application.

1. Log in to your Reddit account and go to the app preferences page.
2. Scroll down and click “are you a developer? create an app…”.
3. Fill out the form:
   • name: gsheet-exporter (or anything you like)
   • type: select ‘script’
   • redirect uri: http://localhost:8080 (this won’t be used, but it’s required)
4. Click “create app”. You’ll now have your credentials. Keep this page open; you’ll need the personal use script ID (your client_id) and the secret (your client_secret).

Step 2: Set Up Your Google Cloud Project & Service Account

This is the part that trips most people up, but it’s straightforward if you follow the steps. We need to create a service account—a special type of Google account for applications—that our script can use to edit the sheet.

1. Go to the Google Cloud Console and create a new project.
2. In your new project, navigate to “APIs & Services” > “Library”.
3. Search for and enable both the Google Drive API and the Google Sheets API.
4. Next, go to “APIs & Services” > “Credentials”. Click “Create Credentials” and select “Service Account”.
5. Give it a name (e.g., “reddit-sheets-bot”), click “Create and Continue”, then “Done” (no need to grant roles here).
6. Find your newly created service account in the list and click on it. Go to the “Keys” tab, click “Add Key” > “Create new key”. Choose JSON as the key type and click “Create”. A file named something like project-name-12345.json will be downloaded.
7. Rename this file to credentials.json for simplicity.
8. Finally, create a new Google Sheet. Look inside your credentials.json file for the client_email address. It will look like an email. Share your Google Sheet with this email address, giving it “Editor” permissions.

Pro Tip: That last step is crucial. If you don’t share the sheet with the service account’s email, your script will fail with a `SpreadsheetNotFound` error. I’ve made this mistake more times than I care to admit.

Step 3: Prepare Your Local Environment

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 (e.g., `pip install praw gspread python-dotenv`).

Your project directory should look like this:

• /your_project_folder
  • main.py (We’ll create this next)
  • credentials.json (The file you downloaded from Google)
  • config.env (Where we’ll store our secrets)

Create a file named config.env and add your Reddit credentials. This keeps them out of your code.

# config.env
REDDIT_CLIENT_ID='YOUR_CLIENT_ID_HERE'
REDDIT_CLIENT_SECRET='YOUR_SECRET_HERE'
REDDIT_USER_AGENT='GSheet Exporter by u/YourUsername'
REDDIT_USERNAME='YOUR_REDDIT_USERNAME'
REDDIT_PASSWORD='YOUR_REDDIT_PASSWORD'
GOOGLE_SHEET_NAME='Name of Your Google Sheet'

Step 4: The Python Script

Alright, let’s get to the code. Create a file named main.py. The script will authenticate with both services, fetch your saved posts, and then write them to the Google Sheet.

Here’s the full script. I’ve broken it down with comments to explain each part.

import os
import praw
import gspread
from dotenv import load_dotenv

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

def authenticate_reddit():
    """Authenticates with Reddit using credentials from config.env."""
    try:
        reddit = praw.Reddit(
            client_id=os.getenv('REDDIT_CLIENT_ID'),
            client_secret=os.getenv('REDDIT_CLIENT_SECRET'),
            user_agent=os.getenv('REDDIT_USER_AGENT'),
            username=os.getenv('REDDIT_USERNAME'),
            password=os.getenv('REDDIT_PASSWORD'),
            check_for_async=False
        )
        # Validate authentication
        print(f"Successfully authenticated with Reddit as: {reddit.user.me()}")
        return reddit
    except Exception as e:
        print(f"Error authenticating with Reddit: {e}")
        return None

def authenticate_google():
    """Authenticates with Google Sheets using the JSON credentials file."""
    try:
        gc = gspread.service_account(filename='credentials.json')
        sheet_name = os.getenv('GOOGLE_SHEET_NAME')
        spreadsheet = gc.open(sheet_name)
        worksheet = spreadsheet.get_worksheet(0) # Get the first sheet
        print(f"Successfully authenticated with Google and opened sheet: '{sheet_name}'")
        return worksheet
    except gspread.exceptions.SpreadsheetNotFound:
        print("Error: Spreadsheet not found. Did you share it with the service account's client_email?")
        return None
    except Exception as e:
        print(f"Error authenticating with Google: {e}")
        return None

def fetch_saved_posts(reddit_instance):
    """Fetches up to 1000 of the user's saved posts."""
    saved_posts = []
    print("Fetching saved posts from Reddit...")
    # Reddit's API limits this to around 1000 items
    for item in reddit_instance.user.me().saved(limit=None):
        # We need to check if the item is a submission (post) or a comment
        if isinstance(item, praw.models.Submission):
            saved_posts.append({
                'title': item.title,
                'url': item.url,
                'subreddit': item.subreddit.display_name,
                'created_utc': item.created_utc,
                'permalink': f"https://reddit.com{item.permalink}"
            })
    print(f"Found {len(saved_posts)} saved posts.")
    return saved_posts

def update_google_sheet(worksheet, posts):
    """Clears the sheet and writes the new posts."""
    if not posts:
        print("No posts to update.")
        return

    print("Updating Google Sheet...")
    try:
        # Clear the sheet first to avoid duplicates
        worksheet.clear()
        
        # Define headers
        headers = ['Title', 'URL', 'Subreddit', 'Created Timestamp', 'Permalink']
        worksheet.append_row(headers)
        
        # Prepare data for batch update
        rows_to_add = []
        for post in posts:
            rows_to_add.append([
                post['title'],
                post['url'],
                post['subreddit'],
                post['created_utc'],
                post['permalink']
            ])

        # Write all data in a single API call for efficiency
        worksheet.append_rows(rows_to_add, value_input_option='USER_ENTERED')
        print("Google Sheet updated successfully.")
    except Exception as e:
        print(f"An error occurred while updating the sheet: {e}")

def main():
    """Main function to run the export process."""
    print("Starting Reddit to Google Sheet export process...")
    
    reddit = authenticate_reddit()
    if not reddit:
        return # Exit if Reddit auth fails

    worksheet = authenticate_google()
    if not worksheet:
        return # Exit if Google auth fails
        
    posts = fetch_saved_posts(reddit)
    update_google_sheet(worksheet, posts)
    
    print("Process finished.")

if __name__ == "__main__":
    main()

Pro Tip: Notice how the `update_google_sheet` function first clears the sheet and then writes the headers and all the data. In my production setups, this “full sync” approach is more reliable than trying to append new rows and figure out what’s already there. It guarantees the sheet is always a perfect mirror of your latest saved posts (up to the API limit).

Step 5: Automation

Running this manually is fine, but the real value comes from automation. On a Linux-based system, you can use cron to schedule it. For example, to run the script every Monday at 2 AM, you would set up a cron job like this:

0 2 * * 1 python3 script.py

On Windows, you can use the Task Scheduler to achieve the same result. This way, your sheet stays up-to-date without you ever having to think about it.

Common Pitfalls

Here’s where I usually mess up when I’m in a hurry, so you can avoid the same headaches:

• The Google Sheet Share: I’ll say it again—forgetting to share the Google Sheet with the service account email from credentials.json is the number one error. You’ll get a `SpreadsheetNotFound` error.
• Reddit API Limit: The official Reddit API only allows you to retrieve about 1000 of your most recent saved items. This is a limitation on their end, not a bug in the script. Just be aware that it won’t back up your entire history if you have more than that.
• Incorrect Environment Variables: A typo in your config.env file can lead to authentication failures. Double-check your IDs and secrets.

Conclusion

And there you have it—a robust, automated way to archive your saved Reddit posts into a much more useful format. From here, you can use Google Sheets’ powerful sorting and filtering tools to actually find what you’re looking for. Feel free to expand on this script; you could add a column for post flair, score, or number of comments. The foundation is there. Happy scripting!

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