Automate a sql query of public information to merge with Smartsheet
Hello!
I'm seeking steer on an automation.
I am manually mining publicly available data (ClinicalTrials.gov) using a query of this database - - and I merge the results into my sheet via a data shuttle upon attachment to the sheet. At present, I do this about once a month (or whenever I think about it).
I would like to automate this process to take place nightly - and I think I can do this using a third party app (Power Automate) - but I'm not having any luck (and I'm not able to find anyone at my institution with this experience).
Because I don't know what I don't know - I thought I'd reach out to the Smartsheet Community to see if anyone else is successfully automating database queries from Smartsheet, and if so, how?
Thanks for any ideas,
Meredith
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
Answers
-
You're not aloneβI also started from scratch with no prior experience using the PostgreSQL connector in Power Automate or accessing the AACT database. But I was able to get everything working to automatically retrieve clinical trial data and output it to Excel, which I can later merge into Smartsheet via Data Shuttle or API.
The image below is the SQL Query in the Playground, which I automated using Power Automate.
What I Did β Step-by-Step:
1. Scheduled Flow (Testing at 10:00, 11:00 AM JST)
I used the "Recurrence" trigger in Power Automate to test the flow daily at 10:00 and 11:00 AM Japan Standard Time.
The image shows that the 10:00 AM and 11:00 AM schedule triggers are working, as expected.
2. Query AACT Public Database (PostgreSQL)
I used Power Automateβs PostgreSQL "Get rows" action.
- Connection setup:
- Server:
aact-db.ctti-clinicaltrials.org - Database:
aact - Authentication: Basic (username & password from your AACT account)
- Server:
- Table name:
studies - Select Query: nct_id, brief_title, overall_status, study_type, phase, enrollment, start_date, source
- Filter Query: study_type eq 'INTERVENTIONAL' and overall_status eq 'COMPLETED'
- Order By:
nct_id - Top Count:
10
3. Parse the Results (Parse JSON)
Once the data is pulled, I used the "Parse JSON" action. I used a schema that matches the study fields I care about (e.g.,
nct_id,brief_title,start_date, etc.).As for the schema, you can use the example output from the PostgreSQL "Get rows" action.to create one.
{
"type": "ParseJson",
"inputs": {
"content": "@body('Get_rows')",
"schema": {
"type": "object",
"properties": {
"value": {
"type": "array",
"items": {
"type": "object",
"properties": {
"nct_id": {
"type": "string"
},
"brief_title": {
"type": "string"
},
"official_title": {
"type": "string"
},
"overall_status": {
"type": "string"
},
"phase": {
"type": "string"
},
"study_type": {
"type": "string"
},
"enrollment": {
"type": "integer"
},
"enrollment_type": {
"type": "string"
},
"source": {
"type": "string"
},
"start_date": {
"type": "string"
},
"completion_date": {
"type": "string"
},
"completion_date_type": {
"type": "string"
},
"primary_completion_date": {
"type": "string"
},
"primary_completion_date_type": {
"type": "string"
},
"verification_date": {
"type": "string"
}
}
}
}
}
}
},
"runAfter": {
"Get_rows": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "eb875e21-1ed1-4ac1-92ed-2abfada6a319"
}
}4. Write Results to Excel
Next, I used "Add a row into a table" (Excel Online Business connector) to output the results to a structured Excel file hosted in OneDrive. I set up my Excel table with headers, as shown in the image below.
Ensure that you create a table by selecting the cells and then using the menu, Insert > Table. I created an original Excel file by downloading the results for the ACCT playground. (In the image, the first 10 rows are from the download, the next 10 are from the manual test, and the last 10 are from the schedule trigger, 10:00 AM JST)
Each row from the database is added to Excel through a loop (
Apply to each).
{
"type": "Foreach",
"foreach": "@body('Parse_JSON')?['value']",
"actions": {
"Add_a_row_into_a_table": {
"type": "OpenApiConnection",
"inputs": {
"parameters": {
"source": "me",
"drive": "b!A8yUIuweNEuY_8yivd1DDiUQpLg7Xq1EkEpaLAK0sMPaLbPhwt5cQKVGPKFlXBsQ",
"file": "01GN4LQ2AK5M5UAES3SFELAMS4X4EVX3OA",
"table": "{D043FC29-9579-477D-AA3F-F3D99095773A}",
"item/nct_id": "@items('Apply_to_each')?['nct_id']",
"item/brief_title": "@items('Apply_to_each')?['brief_title']",
"item/overall_status": "@items('Apply_to_each')?['overall_status']",
"item/study_type": "@items('Apply_to_each')?['study_type']",
"item/phase": "@items('Apply_to_each')?['phase']",
"item/enrollment": "@items('Apply_to_each')?['enrollment']",
"item/start_date": "@items('Apply_to_each')?['start_date']",
"item/source": "@items('Apply_to_each')?['source']"
},
"host": {
"apiId": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness",
"connection": "shared_excelonlinebusiness",
"operationId": "AddRowV2"
}
},
"metadata": {
"01GN4LQ2DG37SUQ7S7T5F3KRRNR5FQMJKZ": "/Automate/AACT Database Query.xlsx",
"operationMetadataId": "caf7705c-f5bb-434d-9b64-319dac8ff4b2",
"tableId": "{D043FC29-9579-477D-AA3F-F3D99095773A}",
"01GN4LQ2AK5M5UAES3SFELAMS4X4EVX3OA": "/Automate/AACT Database Request.xlsx"
}
}
},
"runAfter": {
"Parse_JSON": [
"Succeeded"
]
},
"metadata": {
"operationMetadataId": "acd8880b-e2f2-4010-a5f6-0043ed557031"
}
}This approach:
- Keeps your trial data fresh and centralized
- Can be easily scheduled to run at any frequency (e.g., daily, weekly, etc.).
- Provides clean, structured data you can connect to Smartsheet via Data Shuttle.
- Can also be adapted to push directly to Smartsheet via API if needed
Let me know if you'd like me to share a sample flow export or JSON schemaβIβd be happy to help others replicate this!
- Connection setup:
