Google Sheets as a REST API in PowerShell

$UserKey = "YOUR_USER_KEY"
$Sheet   = "Products"
$ApiKey  = "sk_your_api_key_here"
$Base    = "https://sheetsapi.gkit.mreshank.com/api/spreadsheets"
 
$headers = @{ Authorization = "Bearer $ApiKey" }
$url     = "$Base/$UserKey/$Sheet?limit=20&sort=name"
 
$result  = Invoke-RestMethod -Uri $url -Headers $headers -Method Get
 
Write-Host "Total: $($result.meta.total)"
$result.data | ForEach-Object { Write-Host "$($_.name) - $($_.price)" }

function Get-SheetData {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)] [string] $UserKey,
        [Parameter(Mandatory)] [string] $Sheet,
        [string]  $ApiKey,
        [int]     $Limit    = 20,
        [int]     $Offset   = 0,
        [string]  $Search,
        [string]  $Sort,
        [string]  $Fields
    )
 
    $base   = "https://sheetsapi.gkit.mreshank.com/api/spreadsheets"
    $params = [System.Web.HttpUtility]::ParseQueryString("")
    $params["limit"]  = $Limit
    $params["offset"] = $Offset
    if ($Search) { $params["search"] = $Search }
    if ($Sort)   { $params["sort"]   = $Sort   }
    if ($Fields) { $params["fields"] = $Fields  }
 
    $url     = "$base/$UserKey/$Sheet?$($params.ToString())"
    $headers = if ($ApiKey) { @{ Authorization = "Bearer $ApiKey" } } else { @{} }
 
    Invoke-RestMethod -Uri $url -Headers $headers -Method Get
}

# Search for electronics, sort by descending price
$resp = Get-SheetData -UserKey "YOUR_USER_KEY" -Sheet "Products" `
    -ApiKey "sk_your_api_key_here" `
    -Search "category:electronics" `
    -Sort "-price" `
    -Limit 50
 
$resp.data | Select-Object name, price, category | Format-Table

function Get-AllSheetData {
    param(
        [string] $UserKey,
        [string] $Sheet,
        [string] $ApiKey,
        [int]    $PageSize = 100
    )
 
    $all    = [System.Collections.Generic.List[object]]::new()
    $offset = 0
 
    do {
        $resp = Get-SheetData -UserKey $UserKey -Sheet $Sheet `
            -ApiKey $ApiKey -Limit $PageSize -Offset $offset
        $all.AddRange($resp.data)
        $offset += $PageSize
    } while ($offset -lt $resp.meta.total)
 
    return $all.ToArray()
}
 
$products = Get-AllSheetData -UserKey "YOUR_USER_KEY" -Sheet "Products"
Write-Host "Fetched $($products.Count) products"

$resp = Get-SheetData -UserKey "YOUR_USER_KEY" -Sheet "Products" -Limit 500
$resp.data | Export-Csv -Path "products.csv" -NoTypeInformation
Write-Host "Exported $($resp.meta.total) rows to products.csv"

param(
    [string] $UserKey  = $env:SHEETS_USER_KEY,
    [string] $ApiKey   = $env:SHEETS_API_KEY,
    [string] $OutPath  = "C:\Data\products.csv"
)
 
try {
    $resp = Invoke-RestMethod `
        -Uri "https://sheetsapi.gkit.mreshank.com/api/spreadsheets/$UserKey/Products?limit=500" `
        -Headers @{ Authorization = "Bearer $ApiKey" }
 
    $resp.data | Export-Csv -Path $OutPath -NoTypeInformation -Force
    Write-EventLog -LogName Application -Source "SheetsSync" `
        -EntryType Information -EventId 1 `
        -Message "Synced $($resp.meta.total) products to $OutPath"
} catch {
    Write-EventLog -LogName Application -Source "SheetsSync" `
        -EntryType Error -EventId 2 -Message $_.Exception.Message
}

$action  = New-ScheduledTaskAction -Execute "pwsh.exe" `
    -Argument "-File C:\Scripts\sync-products.ps1"
$trigger = New-ScheduledTaskTrigger -RepetitionInterval (New-TimeSpan -Minutes 15) `
    -Once -At (Get-Date)
Register-ScheduledTask -TaskName "SheetsSync" -Action $action -Trigger $trigger `
    -RunLevel Highest -Force

try {
    $resp = Invoke-RestMethod -Uri $url -Headers $headers -Method Get -ErrorAction Stop
    return $resp
} catch [Microsoft.PowerShell.Commands.HttpResponseException] {
    $statusCode = $_.Exception.Response.StatusCode.value__
    Write-Warning "SheetsAPI returned $statusCode"
    if ($statusCode -eq 429) {
        $retryAfter = $_.Exception.Response.Headers["Retry-After"] ?? 10
        Start-Sleep -Seconds $retryAfter
        # retry once
        return Invoke-RestMethod -Uri $url -Headers $headers -Method Get
    }
    throw
}