Examples

# ==============================================================================
# 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

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}`);
    }
  });
}

function CampaignResponsesAPIintoSpreadsheetData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("API Pull");
  
  if (!sheet) {
    Logger.log("Error: API Pull sheet not found!");
    return;
  }

  var apiUrl = "https://app.informedk12.com/api/v1/campaigns/CAMPAIGN_ID_HERE/responses";
  var apiToken = “YOUR_API_TOKEN_HERE”;

  var headers = {
    "accept": "application/json",
    "authorization": "Bearer " + apiToken
  };

  var options = {
    "method": "get",
    "headers": headers,
    "muteHttpExceptions": true
  };

  try {
    var allData = [];
    var page = 1;
    var hasMoreData = true;

    while (hasMoreData) {
      var pagedUrl = apiUrl + "?page=" + page;
      var response = UrlFetchApp.fetch(pagedUrl, options);
      var rawResponse = response.getContentText();

      if (response.getResponseCode() !== 200) {
        throw new Error("API Error: " + response.getContentText());
      }

      var jsonData = JSON.parse(rawResponse);
      var data = jsonData.data || [];

      if (data.length < 50) {
        hasMoreData = false;
      } else {
        page++;
      }

      allData = allData.concat(data);
    }

    // Collect dynamic field labels
    var fieldLabels = new Set();
    allData.forEach(item => {
      if (item.fields) {
        item.fields.forEach(field => fieldLabels.add(field.label));
      }
    });

    var fieldHeaders = Array.from(fieldLabels);

    var staticHeaders = [
      "formId",
      "currentResponseLink",
      "firstSubmittedAt",
      "lastSubmittedAt",
      "completedAt",
      "status",
      "currentRecipientName",
      "currentRecipientEmail",
      "initiatorName",
      "initiatorEmail",
      "waitingOnStep",
      "waitingOnStepLabel"
    ];

    var allHeaders = staticHeaders.concat(fieldHeaders);

    // Columns that should be numbers
    var numericColumns = [
      "Hourly Rate",
      "Hours Not Exceed",
      "Stipend",
      "Total - Hours",
      "Total - Stipend"
    ];

    // Build full data matrix in memory
    var result = [];
    result.push(allHeaders);  // headers as first row

    allData.forEach(item => {
      var row = [];

      // Add static headers
      staticHeaders.forEach(header => {
        var value = item[header] || "";
        row.push(value.toString());
      });

      // Add dynamic fields
      var fieldValues = {};
      if (item.fields) {
        item.fields.forEach(field => {
          fieldValues[field.label] = field.value || "";
        });
      }

      fieldHeaders.forEach(label => {
        var value = fieldValues[label] || "";
        row.push(value.toString());
      });

      result.push(row);
    });

    // Clear sheet
    sheet.clear();

    var rowCount = result.length;
    var colCount = result[0].length;
    var range = sheet.getRange(1, 1, rowCount, colCount);

    // Pre-set number formats and sanitize numeric values
    allHeaders.forEach((header, index) => {
      var colRange = sheet.getRange(1, index + 1, rowCount, 1);

      if (numericColumns.includes(header)) {
        colRange.setNumberFormat("0.00"); // numeric format

        for (var r = 1; r < rowCount; r++) { // skip header row
          var val = result[r][index];
          if (val === "" || val === null) continue;
          // Remove commas and spaces
          val = val.toString().replace(/,/g, "").trim();
          var numVal = Number(val);
          result[r][index] = isNaN(numVal) ? 0 : numVal;
        }
      } else {
        colRange.setNumberFormat("@"); // text format
        for (var r = 0; r < rowCount; r++) {
          result[r][index] = result[r][index].toString();
        }
      }
    });

    // Write the full matrix
    range.setValues(result);

    Logger.log("✅ Data loaded successfully with mixed text/number formatting!");
  } catch (error) {
    Logger.log("❌ Error fetching data: " + error.message);
  }
}