Google Sheets provides a convenient way to share data publicly, and sometimes you may want to download this data in CSV format for processing in other applications. In this article, I’ll show you how to construct the URL to download a public Google Spreadsheet as a CSV file, and provide a Python script to automate the process.

Understanding the Google Sheets CSV Export URL

When you have a publicly accessible Google Sheet, you can export it as a CSV by modifying its sharing URL. Here’s the pattern:

Original sharing URL:

1

https://docs.google.com/spreadsheets/d/{FILE_ID}/edit?usp=sharing

CSV export URL:

1

https://docs.google.com/spreadsheets/d/{FILE_ID}/export?format=csv

Getting the File ID

The File ID is the long string of characters between /spreadsheets/d/ and /edit in your sheet’s URL. For example:

  • URL: https://docs.google.com/spreadsheets/d/abc123_def456/edit#gid=0
  • File ID: abc123_def456

Python Script to Download CSV

Here’s a Python script that demonstrates how to download a public Google Sheet as a CSV file:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

import requests
import sys

def download_google_sheet_csv(file_id, output_filename=None):
    """
    Download a public Google Sheet as a CSV file
    
    Args:
        file_id (str): The Google Sheets file ID from the URL
        output_filename (str): Optional output filename. Defaults to {file_id}.csv
    """
    # Construct the export URL
    url = f"https://docs.google.com/spreadsheets/d/{file_id}/export?format=csv"
    
    try:
        # Make the request
        response = requests.get(url)
        response.raise_for_status()  # Raise an exception for bad status codes
        
        # Determine output filename
        if output_filename is None:
            output_filename = f"{file_id}.csv"
        
        # Save the content to a file
        with open(output_filename, 'wb') as f:
            f.write(response.content)
        
        print(f"Successfully downloaded CSV to {output_filename}")
        return True
        
    except requests.exceptions.RequestException as e:
        print(f"Error downloading the file: {e}")
        return False

if __name__ == "__main__":
    # Example usage
    if len(sys.argv) < 2:
        print("Usage: python download_sheet.py <file_id> [output_filename]")
        print("Example: python download_sheet.py abc123_def456 my_data.csv")
        sys.exit(1)
    
    file_id = sys.argv[1]
    output_filename = sys.argv[2] if len(sys.argv) > 2 else None
    
    download_google_sheet_csv(file_id, output_filename)

How to Use the Script

  1. Install required package:

    1

    pip install requests

  2. Run the script:

    1

    python download_sheet.py abc123_def456 my_data.csv

Important Notes

  • The Google Sheet must be publicly accessible (set to “Anyone with the link can view”)
  • This method works without authentication for publicly available sheets
  • For private sheets, you would need to use the Google Sheets API with OAuth 2.0
  • The script uses the requests library, which is a popular HTTP library for Python

Alternative: Using wget or curl

You can also download the CSV directly from the command line:

1
2
3
4
5

# Using wget
wget -O output.csv "https://docs.google.com/spreadsheets/d/{FILE_ID}/export?format=csv"

# Using curl
curl -L "https://docs.google.com/spreadsheets/d/{FILE_ID}/export?format=csv" -o output.csv

Conclusion

Downloading public Google Sheets as CSV files is straightforward once you know the URL pattern. The Python script provided makes it easy to automate this process in your applications. Remember to always respect data ownership and usage terms when working with publicly available data.

If you encounter issues, ensure that:

  1. The sheet is publicly accessible
  2. You’re using the correct file ID