To leverage Civis Platform's Google Sheet Export using the Civis API, users should follow three main steps:
- Use the POST/imports endpoint to create a new import configuration. (yes, imports endpoint for a GSheet export - it's weird, carry on) Users should set the function’s parameters to the following:
- name: <name of your export job>
- sync_type: ‘GdocExport’
- is_outbound: True
- source:
- remote_host_id: <your remote host ID> (get this from api.civisanalytics.com/remote_hosts)
- 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)
- destination
- remote_host_id: 219
- 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)
- Use the POST/imports/{id}/syncs endpoint to configure the Google Sheet export settings. Users should set the function’s parameters to the following:
- id: <ID of the import configuration>
- source:
- path: can be left blank/null for Google Sheet exports
- destination:
- googleWorksheet:
- spreadsheet: ‘<Google Sheet spreadsheet name>’
- worksheet: ‘<Google Sheet worksheet name>’
- advanced_options:
- sql_query: ‘<SQL code to grab data to be exported>’
- export_action: <‘newsprsht’,‘newwksht’, ‘updatewksht’, or ‘appendwksht’>
- newsprsht: to create new worksheet/tab inside a new spreadsheet
- newwksht: to create new worksheet/tab inside an existing spreadsheet
- updatewksht: to overwrite an existing worksheet inside an existing spreadsheet.
- appendwksht: to append to the end of an existing worksheet inside an existing spreadsheet.
- Use the POST/imports/{id}/runs endpoint to run the import. Pass in the import configuration ID as the parameter.
- 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 Export',
sync_type = 'GdocExport',
is_outbound = TRUE,
source = list(remote_host_id=<your_remote_host_id>, credential_id=<your_database_credential_id>),
destination = 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(path = NULL),
destination = list(
googleWorksheet = list(spreadsheet = "<Google Sheet Spreadsheet Name>",
worksheet = "<Google Sheet Worksheet Name>")
),
advanced_options = list(sql_query = "<SQL query>",
export_action = "<export action>")
)
### 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 Export',
sync_type = 'GdocExport',
is_outbound = True,
source = {
"remote_host_id": <your_remote_host_id>,
"credential_id": <your_database_credential_id>
},
destination = {
"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 = {"path": ""},
destination = {
"googleWorksheet":{
"spreadsheet":"<Google Sheet Spreadsheet Name>",
"worksheet": "<Google Sheet Worksheet Name>"
}
},
advanced_options = {
"sql_query": "<SQL query>",
"export_action": "<export action>"
}
)
### Step 3: 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)
Comments
0 comments
Please sign in to leave a comment.