Creating Google Sheet Imports via Civis API

To leverage Civis Platform's Google Sheet Import using the Civis API, users should follow three main steps:

1. Use the POST/imports endpoint to create a new import configuration. Users should set the function’s parameters to the following:
1. name: <name of your import job>
2. sync_type: ‘GdocImport’
3. is_outbound: False
4. destination:
1. remote_host_id: <your remote host ID> (get this from api.civisanalytics.com/remote_hosts)
2. credential_id: <your database credential> (get this from api.civisanalytics.com/credentials or from the Admin > Credentials dropdown - look for the credential with the Database type)
5. source:
1. remote_host_id: 219
2. credential_id: <your Google credential> (get this from api.civisanalytics.com/credentials or from the Admin > Credentials dropdown - look for the credential with the Google type)
2. Use the POST/imports/{id}/syncs endpoint to configure the Google Sheet import settings. Users should set the function’s parameters to the following:
1. id: <ID of the import configuration>
2. source:
1. googleWorksheet:
1. spreadsheet: ‘<Google Sheet spreadsheet name>’
2. worksheet: ‘<Google Sheet worksheet name>’
3. destination:
1. databaseTable:
1. schema: ‘<Redshift destination schema name>’
2. table: ‘<Redshift destination table name>’
4. advanced_options:
1. maxErrors: <number of errors to accept>
2. existingTableRows: <‘fail’, ‘wipe’, ‘append’, or ‘drop’>
3. Use the POST/imports/{id}/runs endpoint to run the import. Pass in the import configuration ID as the parameter.
4. Optionally, to keep Platform clean, use the PUT/jobs/{id}/archive endpoint to archive the export Platform job. Pass in the import configuration ID and status = True as parameters.

Sample R Code

library(civis)

## Step 1: Use POST/imports (https://civisanalytics.github.io/civis-r/reference/imports_post.html)
create_import_response <- imports_post(
  name = 'My Import',
  sync_type = 'GdocImport',
  is_outbound = FALSE,
  destination = list(remote_host_id=<your_remote_host_id>, credential_id=<your_database_credential_id>),
  source = list(remote_host_id = 219, credential_id = <your_google_credential_id>)
)
import_id <- create_import_response$id # grabs import Platform ID
### Step 2: POST/imports/{id}/syncs to specify Google Sheet export settings (https://civisanalytics.github.io/civis-r/reference/imports_post_syncs.html)
create_sync <- imports_post_syncs(
  id = import_id,
  source = list(
    googleWorksheet = list(spreadsheet = "<Google Sheet Spreadsheet Name>",
                           worksheet = "<Google Sheet Worksheet Name>")
  ),
  destination = list(
    databaseTable = list(schema = "<Redshift destination schema name>",
                         table = "<Redshift destination table name>")
  ),
  advanced_options = list(maxErrors = 0,
                          existingTableRows = 'drop')
)
### Step 3: POST/imports/{id}/runs to actually run the export
imports_post_runs(import_id)
### OPTIONAL - Step 4: PUT/jobs/{id}/archive
jobs_put_archive(import_id, status = TRUE)

Sample Python Code

import civis
client = civis.APIClient()

## Step 1: Use POST/imports (https://civis-python.readthedocs.io/en/latest/api_resources.html#civis.resources._resources.Imports.post)
create_import_response = client.imports.post(
  name = 'My Import Job',
  sync_type = 'GdocImport',
  is_outbound = False,
  destination = {
      "remote_host_id": <your_remote_host_id>, 
      "credential_id": <your_database_credential_id>
  },
  source = {
      "remote_host_id": 219,
      "credential_id": <your_google_credential_id>
  }
)
import_id = create_import_response.id # grabs import Platform ID
### Step 2: POST/imports/{id}/syncs to specify Google Sheet export settings (https://civis-python.readthedocs.io/en/latest/api_resources.html#civis.resources._resources.Imports.post_syncs)
create_sync = client.imports.post_syncs(
  id = import_id,
  source = {
      "googleWorksheet":{
          "spreadsheet":"<Google Sheet Spreadsheet Name>",
          "worksheet": "<Google Sheet Worksheet Name>"
      }
  },
  destination = {
      "databaseTable": {
          "schema": "<Redshift destination schema name>",
          "table": "<Redshift destination table name>"}
  },
  advanced_options = {
      "maxErrors": 0,
      "existingTableRows": "drop"
  }
)
### Step 3: POST/imports/{id}/runs to actually run the export
future = civis.utils.run_job(import_id)
future.result()
### OPTIONAL - Step 4: PUT/jobs/{id}/archive
client.jobs.put_archive(import_id, status = True)