Exporting CSV Files from Civis Platform to Google Drive

To export data from Civis Platform to Google Drive as CSV files, you'll need to set up a Google Service Account and add it as a Custom Credential in Platform. This credential allows Civis to securely connect to your Google Drive and transfer files. In this guide, you'll learn how to set up the credentials and write Python code to automate the export process.

Prerequisite Actions

1. Create a Google Cloud Service account by following Google's instructions at: https://developers.google.com/workspace/guides/create-credentials#service-account
2. Download the JSON credentials file from Google Cloud Console
3. Upload the credentials to Civis Platform (one-time setup)

Required Google Service Account Permissions

Your Google Service Account needs the following permissions:

• Drive API access enabled in Google Cloud Console
• Permission to write to the destination Google Drive folder
• The Google Service Account email must be shared with any folders/files it needs to access

Step 1: Upload Google Credentials to Platform

Run this code locally to upload your Google credentials to Platform:

# Run locally

KEY_PATH <- "path_to_your_credentials"

key_id <- civis::write_civis_file(KEY_PATH, expires_at = NULL)

my_key <- jsonlite::fromJSON(KEY_PATH)

# Create Platform credential

civis::credentials_post(

  type = "Custom",

  name = "Drive_Service_Account",

  username = my_key$client_email,

  password = key_id

)

Step 2: Create Python Script in Platform

Create a new Python script in Platform with the following code:

import subprocess

import sys

import civis

import tempfile

import json

import os

import googleapiclient.discovery

from google.oauth2 import service_account

from googleapiclient.http import MediaFileUpload

from googleapiclient.errors import HttpError

# Install required package

def install(package):

    subprocess.check_call([sys.executable, "-m", "pip", "install", package])

install('google-api-python-client')

def google_credentials() -> service_account.Credentials:

    """Return a Google API service account credential object."""

    service_account_info = json.loads(os.environ["DRIVE_SERVICE_ACCOUNT_PASSWORD"])

    credentials = service_account.Credentials.from_service_account_info(

        service_account_info

    )

    return credentials

def write_file_to_google_drive(file_name: str, folder_id: str) -> None:

    """Write file to Google Drive folder."""

    drive_service = googleapiclient.discovery.build(

        "drive", "v3", credentials=google_credentials(), cache_discovery=False

    )

    file_metadata = {"name": file_name, "parents": [folder_id]}

    media = MediaFileUpload(file_name)

    

    drive_service.files().create(

        body=file_metadata, media_body=media, supportsAllDrives=True

    ).execute()

# Export data to CSV

civis.io.civis_to_csv(

    sql="SELECT * FROM schema.table",  # Your SQL query

    database="Your Database",          # Your database name

    filename="output.csv"              # Local file name

).result()

# Upload to Google Drive

with tempfile.TemporaryDirectory() as tmpdirname:

    output_file_name = "output.csv"

    output_drive_folder = "your_folder_id"  # Google Drive folder ID

    write_file_to_google_drive(

        output_file_name, output_drive_folder

    )

Step 3: Configure the Script

1. Attach your Google Drive Service Account credential to the script in Platform settings (instructions here)
2. Update the following variables in the code:
   • SQL query
   • Database name
   • Output file name
   • Google Drive folder ID (found in the folder's URL)

Running the Script

The script will:

1. Query your data and save it as a CSV file
2. Upload the CSV file to the specified Google Drive folder
3. Clean up temporary files automatically

Relevant Documentation:

• Export to Google Sheet
• Adding a Google Service Account for Google Doc Imports/Exports
• CSV Exports - Civis API