Responses into a CSV and upload to SFTP
Fetches all paginated responses from the Informed K12 Campaign Responses API, flattens static + dynamic fields into a CSV, and uploads it to an SFTP server
# ==============================================================================
# Informed K12 Campaign Responses → CSV → SFTP Uploader (PowerShell)
# ------------------------------------------------------------------------------
# Fetches all paginated responses from the Informed K12 Campaign Responses API,
# flattens static + dynamic fields into a CSV, and uploads it to an SFTP server.
#
# Requirements:
# - PowerShell 5.1+ or PowerShell 7+
# - WinSCP .NET assembly (free): https://winscp.net/eng/download.php
# After installing WinSCP, the DLL is typically at:
# C:\Program Files (x86)\WinSCP\WinSCPnet.dll
#
# Usage:
# 1. Fill in the CONFIG section below.
# 2. Open PowerShell and run: .\api_to_sftp.ps1
# ==============================================================================
# ==============================================================================
# CONFIG — fill in your details here
# ==============================================================================
# --- Informed K12 API ---
$CampaignId = "CAMPAIGN_ID" # Change to your Campaign ID
$ApiToken = "YOUR_API_KEY" # Your Informed K12 API token
$ApiUrl = "https://app.informedk12.com/api/v1/campaigns/$CampaignId/responses"
$PageSize = 50 # Informed K12 returns max 50 per page
# Columns that should be stored as numbers (not text) in the CSV
$NumericColumns = @(
"Hourly Rate",
"Hours Not Exceed",
"Stipend",
"Total - Hours",
"Total - Stipend"
)
# --- SFTP ---
$SftpHost = "sftp.example.com"
$SftpPort = 22
$SftpUsername = "your_username"
$SftpPassword = "your_password" # Leave blank "" if using a key file
$SftpKeyFile = "" # Full path to .ppk key file, e.g. "C:\Keys\id_rsa.ppk"
# Leave blank "" if using a password
$SftpRemoteDir = "/uploads/" # Remote folder to upload the CSV into
# --- WinSCP DLL ---
$WinScpDll = "C:\Program Files (x86)\WinSCP\WinSCPnet.dll"
# --- Output ---
$Timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
$CsvFilename = "ik12_responses_$Timestamp.csv"
$LocalTemp = "$env:TEMP\$CsvFilename"
# ==============================================================================
# STEP 1 — Fetch all pages from the Informed K12 API
# ==============================================================================
function Fetch-AllResponses {
Write-Host "[1/3] Fetching campaign responses from Informed K12 (Campaign $CampaignId) ..." -ForegroundColor Cyan
$headers = @{
"accept" = "application/json"
"authorization" = "Bearer $ApiToken"
}
$allData = [System.Collections.Generic.List[object]]::new()
$page = 1
do {
$pagedUrl = "$ApiUrl`?page=$page"
Write-Host " Fetching page $page ..." -ForegroundColor Gray
try {
$response = Invoke-RestMethod -Uri $pagedUrl -Headers $headers -Method Get -ErrorAction Stop
} catch {
Write-Error "API request failed on page $page`: $_"
exit 1
}
$pageData = $response.data
if (-not $pageData) { $pageData = @() }
foreach ($item in $pageData) { $allData.Add($item) }
# Informed K12 returns fewer than $PageSize records on the last page
if ($pageData.Count -lt $PageSize) {
break
}
$page++
} while ($true)
Write-Host " Total records fetched: $($allData.Count)" -ForegroundColor Green
return $allData
}
# ==============================================================================
# STEP 2 — Flatten records and convert to CSV
# ==============================================================================
function Convert-ToCsv {
param([System.Collections.Generic.List[object]]$Records)
Write-Host "[2/3] Converting to CSV ..." -ForegroundColor Cyan
if ($Records.Count -eq 0) {
Write-Error "No records to convert — the API returned no data."
exit 1
}
# Static columns (always present on every response)
$StaticHeaders = @(
"formId",
"currentResponseLink",
"firstSubmittedAt",
"lastSubmittedAt",
"completedAt",
"status",
"currentRecipientName",
"currentRecipientEmail",
"initiatorName",
"initiatorEmail",
"waitingOnStep",
"waitingOnStepLabel"
)
# Collect all unique dynamic field labels across every record
$fieldLabelSet = [System.Collections.Generic.HashSet[string]]::new()
foreach ($item in $Records) {
if ($item.fields) {
foreach ($field in $item.fields) {
[void]$fieldLabelSet.Add($field.label)
}
}
}
$DynamicHeaders = @($fieldLabelSet)
$AllHeaders = $StaticHeaders + $DynamicHeaders
# Build rows
$rows = [System.Collections.Generic.List[string[]]]::new()
foreach ($item in $Records) {
$row = [string[]]::new($AllHeaders.Count)
# Static fields
for ($i = 0; $i -lt $StaticHeaders.Count; $i++) {
$val = $item.($StaticHeaders[$i])
$row[$i] = if ($null -ne $val) { $val.ToString() } else { "" }
}
# Dynamic fields — build a lookup from this record's fields array
$fieldMap = @{}
if ($item.fields) {
foreach ($field in $item.fields) {
$fieldMap[$field.label] = if ($field.value) { $field.value } else { "" }
}
}
for ($i = 0; $i -lt $DynamicHeaders.Count; $i++) {
$label = $DynamicHeaders[$i]
$val = if ($fieldMap.ContainsKey($label)) { $fieldMap[$label] } else { "" }
# Numeric columns: strip commas/spaces and store as a plain number
if ($NumericColumns -contains $label) {
$cleaned = $val.ToString().Replace(",", "").Trim()
$num = 0.0
if ([double]::TryParse($cleaned, [ref]$num)) {
$val = $num.ToString()
} else {
$val = "0"
}
}
$row[$StaticHeaders.Count + $i] = $val.ToString()
}
$rows.Add($row)
}
# Write to temp CSV file
$csv = [System.Text.StringBuilder]::new()
# Header row
$escapedHeaders = $AllHeaders | ForEach-Object { '"' + $_.Replace('"', '""') + '"' }
[void]$csv.AppendLine($escapedHeaders -join ",")
# Data rows
foreach ($row in $rows) {
$escapedCells = $row | ForEach-Object { '"' + $_.Replace('"', '""') + '"' }
[void]$csv.AppendLine($escapedCells -join ",")
}
[System.IO.File]::WriteAllText($LocalTemp, $csv.ToString(), [System.Text.Encoding]::UTF8)
$sizeKb = [math]::Round((Get-Item $LocalTemp).Length / 1KB, 1)
Write-Host " CSV saved: $LocalTemp ($sizeKb KB, $($rows.Count) rows, $($AllHeaders.Count) columns)" -ForegroundColor Green
}
# ==============================================================================
# STEP 3 — Upload CSV to SFTP via WinSCP
# ==============================================================================
function Upload-ToSftp {
Write-Host "[3/3] Uploading to SFTP $SftpHost ..." -ForegroundColor Cyan
if (-not (Test-Path $WinScpDll)) {
Write-Error "WinSCP DLL not found at: $WinScpDll`nDownload WinSCP from https://winscp.net/eng/download.php"
exit 1
}
Add-Type -Path $WinScpDll
$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
Protocol = [WinSCP.Protocol]::Sftp
HostName = $SftpHost
PortNumber = $SftpPort
UserName = $SftpUsername
}
if ($SftpKeyFile -ne "") {
$sessionOptions.SshPrivateKeyPath = $SftpKeyFile
} else {
$sessionOptions.Password = $SftpPassword
}
# Accepts any host key — safe for initial setup.
# For production, replace with your server's fingerprint:
# $sessionOptions.SshHostKeyFingerprint = "ssh-rsa 2048 xx:xx:xx:..."
$sessionOptions.GiveUpSecurityAndAcceptAnySshHostKey = $true
$session = New-Object WinSCP.Session
try {
$session.Open($sessionOptions)
$remotePath = $SftpRemoteDir.TrimEnd("/") + "/" + $CsvFilename
$transferResult = $session.PutFiles($LocalTemp, $remotePath)
$transferResult.Check()
Write-Host " Uploaded to: $remotePath" -ForegroundColor Green
} catch {
Write-Error "SFTP upload failed: $_"
exit 1
} finally {
$session.Dispose()
}
}
# ==============================================================================
# MAIN
# ==============================================================================
$responses = Fetch-AllResponses
Convert-ToCsv -Records $responses
Upload-ToSftp
Remove-Item $LocalTemp -Force -ErrorAction SilentlyContinue
Write-Host ""
Write-Host "Done! '$CsvFilename' uploaded successfully." -ForegroundColor Green
Responses to Google Calendar
function importInformedK12ToCalendar() {
const API_URL = 'https://app.informedk12.com/api/v1/campaigns/“INSERT_CAMPAIGN_ID”/responses?statusGroup=completed';
const API_TOKEN = “YOUR_API_KEY”;
const CALENDAR_ID = “YOUR_CALENDAR_ID”;
const options = {
'method': 'get',
'headers': { 'Authorization': 'Bearer ' + API_TOKEN }
};
const response = UrlFetchApp.fetch(API_URL, options);
const json = JSON.parse(response.getContentText());
// Access the specific calendar instead of the default one
const calendar = CalendarApp.getCalendarById(CALENDAR_ID);
// 2. Iterate through each response in the data array
json.data.forEach(entry => {
const fields = entry.fields;
// Helper function to grab values by their field number
const getVal = (num) => fields.find(f => f.number === num)?.value;
const eventStartDate = getVal(11); // "02/14/2026"
const eventEndDate = getVal(12); // "02/14/2026"
const startTimeStr = getVal(13); // "9:00 AM"
const endTimeStr = getVal(14); // "11:00 AM"
const eventTitle = getVal(19); // "Reason for Use" (e.g., Ninja training)
const location = getVal(10); // "Gym"
const uniqueLink = entry.currentResponseLink;
if (eventStartDate && startTimeStr && endTimeStr) {
const start = new Date(`${eventStartDate} ${startTimeStr}`);
const end = new Date(`${eventEndDate} ${endTimeStr}`);
// --- DEDUPLICATION CHECK ---
// Search for events on this specific day that contain the unique link
const existingEvents = calendar.getEvents(start, end, {search: uniqueLink});
if (existingEvents.length > 0) {
console.log(`Skipping: Event for ${uniqueLink} already exists.`);
return; // Skip to next entry
}
// ----------------------------
calendar.createEvent(eventTitle, start, end, {
location: getVal(10), // Gym
description: `Requested by: ${entry.initiatorName}\nForm Link: ${uniqueLink}`
});
console.log(`Created new event: ${eventTitle}`);
}
});
}