Add Sharepoint

📋 Shared Instructions: [shared-instructions.md](${CLAUDE_PLUGIN_ROOT}/shared/shared-instructions.md) - Cross-cutting concerns.

References:

• sharepoint-reference.md - Column encoding, choice fields, lookups, API patterns (CRITICAL)
• api-authentication-reference.md - Graph API auth, token, site ID
• list-management-reference.md - Query, create, extend lists and columns

Add SharePoint

Two paths: existing lists (skip to Step 6) or new lists (full workflow).

Workflow

1. Check Memory Bank → 2. Plan → 3. Setup Graph API Auth → 4. Review Existing Lists → 5. Create Lists → 6. Get Connection ID → 7. Discover Sites → 8. Discover Tables → 9. Add Connector → 10. Configure → 11. Build → 12. Update Memory Bank

---

Step 1: Check Memory Bank

Check for memory-bank.md per shared-instructions.md.

Step 2: Plan

Ask the user:

1. Which SharePoint list(s) do they need? 2. Do the lists already exist on their site, or do they need to create new ones?

If lists already exist: Skip to Step 6.

If creating new lists:

• Ask about the data they need and design an appropriate schema
• Reuse existing lists when possible (don't duplicate)
• Enter plan mode with EnterPlanMode, present the list designs with columns and types
• Get approval with ExitPlanMode

Step 3: Setup Graph API Auth (if creating lists)

See api-authentication-reference.md for full details.

az account show   # Verify Azure CLI logged in

$api = Initialize-SharePointGraphApi -SiteUrl "https://<tenant>.sharepoint.com/sites/<site-name>"
$headers = $api.Headers
$siteId = $api.SiteId

Requires Sites.Manage.All permission.

Step 4: Review Existing Lists (if creating lists)

Always query existing lists first before creating:

$existingLists = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$siteId/lists?`$select=id,displayName,description,list&`$filter=list/hidden eq false" -Headers $headers

See list-management-reference.md for Find-SimilarLists, Compare-ListSchemas, and Get-ListSchema functions.

Present findings to user with AskUserQuestion:

• Lists that can be reused (already exist with matching columns)
• Lists that need extension (exist but missing columns)
• Lists that must be created (no match found)

Step 5: Create Lists (if creating lists)

Get explicit confirmation before creating. Use safe functions from list-management-reference.md:

• New-SharePointListIfNotExists
• Add-SharePointColumnIfNotExists
• Add-SharePointLookupColumn (for cross-list references)

Step 6: Get Connection ID

Find the SharePoint Online connection ID (see connector-reference.md):

Run the /list-connections skill. Find the SharePoint Online connection in the output. If none exists, direct the user to create one using the environment-specific Connections URL — construct it from the active environment ID in context (from power.config.json or a prior step): https://make.powerapps.com/environments/<environment-id>/connections → + New connection → search for the connector → Create.

Step 7: Discover Sites

List available SharePoint sites the user has access to:

npx power-apps list-datasets -a sharepointonline -c <connection-id>

Present the sites to the user and ask which one(s) they want to connect to. If the user already specified a site URL, confirm it appears in the list.

If `npx power-apps list-datasets` fails or returns no results:

• Auth error: Run npx power-apps logout, then retry — the CLI will re-prompt browser login.
• Empty list: Confirm the connection ID is for a SharePoint Online connection and the user has access to at least one site. STOP if the list is empty after confirming.
• Any other non-zero exit: Report the exact error output. STOP.

Step 8: Discover Tables

For each selected site, list the available lists and document libraries:

npx power-apps list-tables -a sharepointonline -c <connection-id> -d '<site-url>'

If `npx power-apps list-tables` fails or returns no results:

• Confirm the site URL from Step 7 is exact (copy from the output — do not retype).
• If still empty, the user may not have access to that site's lists. Ask them to verify permissions in SharePoint.
• Any other non-zero exit: Report the exact error output. STOP.

Present the tables to the user and ask which ones they want to add. Suggest tables that look relevant to their use case (based on memory bank context or the user's stated requirements). If lists were created in Step 5, they should appear here.

Step 9: Add Connector

SharePoint is a tabular datasource -- requires -c (connection ID), -d (site URL), and -t (list name):

npx power-apps add-data-source -a sharepointonline -c <connection-id> -d '<site-url>' -t '<table-name>'

Run the command for each list or library the user selected. The -d (dataset) is the SharePoint site URL from Step 7, -t (table) is the list/library name from Step 8.

Step 10: Configure

Read [sharepoint-reference.md](./references/sharepoint-reference.md) before writing any SharePoint code -- column encoding, choice fields, and lookups have critical gotchas.

Common operations:

// Get items from a SharePoint list
const items = await SharePointOnlineService.GetItems({
  dataset: "https://contoso.sharepoint.com/sites/your-site",
  table: "Your List Name"
});

// Create a new list item
await SharePointOnlineService.PostItem({
  dataset: "https://contoso.sharepoint.com/sites/your-site",
  table: "Your List Name",
  item: {
    Title: "New Item",
    Description: "Item description",
    Status: "Active"
  }
});

// Get files from a document library
const files = await SharePointOnlineService.ListFolder({
  dataset: "https://contoso.sharepoint.com/sites/your-site",
  id: "Shared Documents" // Library name or folder ID
});

// Get file content
const content = await SharePointOnlineService.GetFileContent({
  dataset: "https://contoso.sharepoint.com/sites/your-site",
  id: "file-server-relative-url"
});

Key points:

• dataset is always the full SharePoint site URL
• table is the list display name for list operations
• List column names in the API may differ from display names (spaces become _x0020_, special chars encoded)
• Document library operations use folder/file IDs or server-relative URLs
• Choice columns use string values, not integer picklist codes (unlike Dataverse)

Use Grep to find specific methods in src/generated/services/SharePointOnlineService.ts (generated files can be very large -- see connector-reference.md).

Step 11: Build

npm run build

Fix TypeScript errors before proceeding. Do NOT deploy yet.

Step 12: Update Memory Bank

Update memory-bank.md with: connector added, site URL, lists/libraries configured (or created), build status.

API Authentication Reference

Uses Microsoft Graph API with Azure CLI authentication (az account get-access-token) to manage SharePoint lists and columns.

Prerequisites

Ensure Azure CLI is authenticated before proceeding:

# Verify Azure CLI is logged in
az account show

# If not logged in, run:
az login

Get Access Token

$token = (az account get-access-token --resource https://graph.microsoft.com --query accessToken -o tsv)

Set Up API Headers

$headers = @{
    "Authorization" = "Bearer $token"
    "Content-Type" = "application/json"
}

API Headers Reference

Get Site ID from URL

SharePoint site URLs follow the pattern https://{tenant}.sharepoint.com/sites/{site-name}. Extract the site ID using the Graph API:

function Get-SiteId {
    param(
        [Parameter(Mandatory=$true)]
        [string]$SiteUrl,

        [Parameter(Mandatory=$true)]
        [hashtable]$Headers
    )

    # Parse the URL into hostname and server-relative path
    $uri = [System.Uri]$SiteUrl
    $hostname = $uri.Host
    $serverRelativePath = $uri.AbsolutePath.TrimEnd('/')

    $graphUrl = "https://graph.microsoft.com/v1.0/sites/${hostname}:${serverRelativePath}"
    $site = Invoke-RestMethod -Uri $graphUrl -Headers $Headers
    return $site.id
}

Complete Setup Script

function Initialize-SharePointGraphApi {
    param(
        [Parameter(Mandatory=$true)]
        [string]$SiteUrl
    )

    $token = (az account get-access-token --resource https://graph.microsoft.com --query accessToken -o tsv)

    if (-not $token) {
        throw "Failed to get access token. Make sure you're logged in with 'az login'"
    }

    $headers = @{
        "Authorization" = "Bearer $token"
        "Content-Type" = "application/json"
    }

    $siteId = Get-SiteId -SiteUrl $SiteUrl -Headers $headers

    Write-Host "Connected to site: $SiteUrl" -ForegroundColor Green
    Write-Host "Site ID: $siteId" -ForegroundColor Cyan

    return @{
        Headers = $headers
        SiteId = $siteId
        SiteUrl = $SiteUrl
    }
}

Token Refresh

Access tokens expire after ~1 hour. For long-running scripts:

function Get-FreshToken {
    return (az account get-access-token --resource https://graph.microsoft.com --query accessToken -o tsv)
}

function Invoke-GraphApi {
    param(
        [string]$Uri,
        [string]$Method = "Get",
        [hashtable]$Headers,
        [string]$Body = $null
    )

    try {
        if ($Body) {
            return Invoke-RestMethod -Uri $Uri -Method $Method -Headers $Headers -Body $Body
        } else {
            return Invoke-RestMethod -Uri $Uri -Method $Method -Headers $Headers
        }
    } catch {
        if ($_.Exception.Response.StatusCode -eq 401) {
            Write-Host "Token expired, refreshing..." -ForegroundColor Yellow
            $newToken = Get-FreshToken
            $Headers["Authorization"] = "Bearer $newToken"

            if ($Body) {
                return Invoke-RestMethod -Uri $Uri -Method $Method -Headers $Headers -Body $Body
            } else {
                return Invoke-RestMethod -Uri $Uri -Method $Method -Headers $Headers
            }
        }
        throw
    }
}

Verify Connection

try {
    $site = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$siteId" -Headers $headers
    Write-Host "Connected to: $($site.displayName)" -ForegroundColor Green
    Write-Host "Web URL: $($site.webUrl)" -ForegroundColor Cyan
} catch {
    Write-Host "Connection failed: $($_.Exception.Message)" -ForegroundColor Red
}

Required Permissions

To create lists and manage columns via Graph API, the Azure CLI app registration needs:

• Sites.Manage.All - Create and manage lists, list items, and columns
• Sites.Read.All - Read site and list metadata (minimum for discovery)

List Management Reference

Query Existing Lists

Before creating lists, review what exists on the site:

$existingLists = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$siteId/lists?`$select=id,displayName,description,list&`$filter=list/hidden eq false" -Headers $headers

Write-Host "Found $($existingLists.value.Count) lists:" -ForegroundColor Cyan
$existingLists.value | ForEach-Object {
    $template = $_.list.template
    Write-Host "  - $($_.displayName) ($template)" -ForegroundColor Yellow
}

Get List Schema

function Get-ListSchema {
    param(
        [string]$SiteId,
        [string]$ListId,
        [hashtable]$Headers
    )

    $list = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$SiteId/lists/$ListId/columns" -Headers $Headers

    Write-Host "`nList columns:" -ForegroundColor Cyan
    $list.value | Where-Object {
        -not $_.readOnly -and $_.name -notin @('ContentType', 'Attachments', '_ModerationComments', '_ModerationStatus', 'Edit', 'LinkTitleNoMenu', 'LinkTitle', 'DocIcon', 'ItemChildCount', 'FolderChildCount', '_ComplianceFlags', '_ComplianceTag', '_ComplianceTagWrittenTime', '_ComplianceTagUserId')
    } | ForEach-Object {
        $type = if ($_.text) { "text" } elseif ($_.number) { "number" } elseif ($_.choice) { "choice" } elseif ($_.dateTime) { "dateTime" } elseif ($_.boolean) { "boolean" } elseif ($_.lookup) { "lookup" } elseif ($_.personOrGroup) { "personOrGroup" } else { "unknown" }
        Write-Host "    - $($_.displayName) [$($_.name)] ($type)"
    }

    return $list
}

Check If List/Column Exists

function Test-ListExists {
    param(
        [string]$SiteId,
        [string]$DisplayName,
        [hashtable]$Headers
    )

    $lists = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$SiteId/lists?`$filter=displayName eq '$DisplayName'" -Headers $Headers
    return $lists.value.Count -gt 0
}

function Test-ColumnExists {
    param(
        [string]$SiteId,
        [string]$ListId,
        [string]$ColumnName,
        [hashtable]$Headers
    )

    try {
        $columns = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$SiteId/lists/$ListId/columns" -Headers $Headers
        $match = $columns.value | Where-Object { $_.name -eq $ColumnName -or $_.displayName -eq $ColumnName }
        return $null -ne $match
    } catch {
        if ($_.Exception.Response.StatusCode -eq 404) { return $false }
        throw
    }
}

Find Similar Lists

Search for lists with similar purposes but different names:

function Find-SimilarLists {
    param(
        [string]$Purpose,
        [array]$ExistingLists
    )

    $patterns = @{
        "task" = @("task", "tasks", "todo", "to-do", "action", "actions", "work item")
        "contact" = @("contact", "contacts", "people", "person", "directory", "employee", "staff")
        "project" = @("project", "projects", "initiative", "program")
        "issue" = @("issue", "issues", "bug", "bugs", "incident", "ticket", "request")
        "inventory" = @("inventory", "asset", "assets", "equipment", "stock", "item", "items")
        "event" = @("event", "events", "calendar", "meeting", "schedule")
        "document" = @("document", "documents", "file", "files", "library")
    }

    $searchTerms = $patterns[$Purpose]
    if (-not $searchTerms) { $searchTerms = @($Purpose) }

    $matches = $ExistingLists | Where-Object {
        $listName = $_.displayName.ToLower()
        $listDesc = if ($_.description) { $_.description.ToLower() } else { "" }

        foreach ($term in $searchTerms) {
            if ($listName -match $term -or $listDesc -match $term) {
                return $true
            }
        }
        return $false
    }

    return $matches
}

Compare Existing vs Required Lists

function Compare-ListSchemas {
    param(
        [hashtable]$RequiredLists,  # Purpose name -> array of required column names
        [array]$ExistingLists,
        [string]$SiteId,
        [hashtable]$Headers
    )

    $comparison = @{
        Reusable = @()
        Extendable = @()
        CreateNew = @()
    }

    foreach ($listPurpose in $RequiredLists.Keys) {
        $existing = Find-SimilarLists -Purpose $listPurpose -ExistingLists $ExistingLists | Select-Object -First 1

        if ($existing) {
            $schema = Get-ListSchema -SiteId $SiteId -ListId $existing.id -Headers $Headers
            $existingColumns = $schema.value | Select-Object -ExpandProperty name
            $requiredColumns = $RequiredLists[$listPurpose]
            $missingColumns = $requiredColumns | Where-Object { $_ -notin $existingColumns }

            if ($missingColumns.Count -eq 0) {
                $comparison.Reusable += @{
                    ListPurpose = $listPurpose
                    ListId = $existing.id
                    DisplayName = $existing.displayName
                    Message = "All required columns present"
                }
            } else {
                $comparison.Extendable += @{
                    ListPurpose = $listPurpose
                    ListId = $existing.id
                    DisplayName = $existing.displayName
                    MissingColumns = $missingColumns
                    Message = "Missing columns: $($missingColumns -join ', ')"
                }
            }
        } else {
            $comparison.CreateNew += @{
                ListPurpose = $listPurpose
                RequiredColumns = $RequiredLists[$listPurpose]
            }
        }
    }

    return $comparison
}

Create List

function New-SharePointList {
    param(
        [string]$SiteId,
        [string]$DisplayName,
        [string]$Description = "",
        [hashtable]$Headers
    )

    $listDefinition = @{
        displayName = $DisplayName
        description = $Description
        list = @{
            template = "genericList"
        }
    }

    $body = $listDefinition | ConvertTo-Json -Depth 5
    $result = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$SiteId/lists" -Method Post -Headers $Headers -Body $body
    return $result
}

function New-SharePointListIfNotExists {
    param(
        [string]$SiteId,
        [string]$DisplayName,
        [string]$Description = "",
        [hashtable]$Headers
    )

    if (Test-ListExists -SiteId $SiteId -DisplayName $DisplayName -Headers $Headers) {
        Write-Host "  [SKIP] List '$DisplayName' already exists" -ForegroundColor Yellow
        $existing = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$SiteId/lists?`$filter=displayName eq '$DisplayName'" -Headers $Headers
        return @{ Skipped = $true; List = $existing.value[0] }
    }

    Write-Host "  [CREATE] Creating list '$DisplayName'..." -ForegroundColor Cyan
    $result = New-SharePointList -SiteId $SiteId -DisplayName $DisplayName -Description $Description -Headers $Headers
    Write-Host "  [OK] List '$DisplayName' created (ID: $($result.id))" -ForegroundColor Green
    return @{ Skipped = $false; List = $result }
}

Add Columns

function Add-SharePointColumn {
    param(
        [string]$SiteId,
        [string]$ListId,
        [string]$Name,
        [string]$DisplayName,
        [string]$Type,  # text, number, choice, dateTime, boolean
        [string[]]$Choices = @(),
        [hashtable]$Headers
    )

    $columnDefinition = @{
        name = $Name
        displayName = $DisplayName
        enforceUniqueValues = $false
    }

    switch ($Type) {
        "text" {
            $columnDefinition["text"] = @{
                allowMultipleLines = $false
                maxLength = 255
            }
        }
        "multilineText" {
            $columnDefinition["text"] = @{
                allowMultipleLines = $true
            }
        }
        "number" {
            $columnDefinition["number"] = @{}
        }
        "choice" {
            $columnDefinition["choice"] = @{
                allowTextEntry = $false
                choices = $Choices
                displayAs = "dropDownMenu"
            }
        }
        "dateTime" {
            $columnDefinition["dateTime"] = @{
                format = "dateTime"
            }
        }
        "dateOnly" {
            $columnDefinition["dateTime"] = @{
                format = "dateOnly"
            }
        }
        "boolean" {
            $columnDefinition["boolean"] = @{}
        }
        "lookup" {
            # Lookup columns require a separate API call -- see Add-SharePointLookupColumn
            throw "Use Add-SharePointLookupColumn for lookup columns"
        }
    }

    $body = $columnDefinition | ConvertTo-Json -Depth 5
    Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$SiteId/lists/$ListId/columns" -Method Post -Headers $Headers -Body $body
}

function Add-SharePointColumnIfNotExists {
    param(
        [string]$SiteId,
        [string]$ListId,
        [string]$Name,
        [string]$DisplayName,
        [string]$Type,
        [string[]]$Choices = @(),
        [hashtable]$Headers
    )

    if (Test-ColumnExists -SiteId $SiteId -ListId $ListId -ColumnName $Name -Headers $Headers) {
        Write-Host "    [SKIP] Column '$DisplayName' already exists" -ForegroundColor Yellow
        return @{ Skipped = $true }
    }

    Write-Host "    [CREATE] Adding column '$DisplayName' ($Type)..." -ForegroundColor Cyan
    Add-SharePointColumn -SiteId $SiteId -ListId $ListId -Name $Name -DisplayName $DisplayName -Type $Type -Choices $Choices -Headers $Headers
    Write-Host "    [OK] Column '$DisplayName' added" -ForegroundColor Green
}

Add Lookup Column

Lookup columns reference another list on the same site:

function Add-SharePointLookupColumn {
    param(
        [string]$SiteId,
        [string]$ListId,
        [string]$Name,
        [string]$DisplayName,
        [string]$LookupListId,
        [string]$LookupColumnName = "Title",
        [hashtable]$Headers
    )

    $columnDefinition = @{
        name = $Name
        displayName = $DisplayName
        lookup = @{
            listId = $LookupListId
            columnName = $LookupColumnName
            allowMultipleValues = $false
        }
    }

    $body = $columnDefinition | ConvertTo-Json -Depth 5
    Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$SiteId/lists/$ListId/columns" -Method Post -Headers $Headers -Body $body
}

SharePoint Reference

Critical patterns for working with SharePoint lists in Power Apps code apps. Read this before writing any SharePoint code.

Column Name Encoding - CRITICAL

SharePoint internal column names encode special characters. The generated TypeScript services use these internal names, not display names.

Common encodings:

• Space: _x0020_
• (: _x0028_
• ): _x0029_
• /: _x002f_
• &: _x0026_
• #: _x0023_
• %: _x0025_

Best practice: Use simple column names without spaces or special characters (e.g., ProjectStatus instead of Project Status) to avoid encoding issues.

Choice Columns - Key Difference from Dataverse

SharePoint choice fields store string values, not integer picklist codes. This is fundamentally different from Dataverse.

// CORRECT - SharePoint choices are string values
const item = {
  Title: "My Item",
  Status: "Active",           // String value, not a number
  Priority: "High",           // String value, not a number
  Category: "Engineering"     // String value, not a number
};

// CORRECT - Filter by string value
const activeItems = items.filter(i => i.Status === "Active");

// CORRECT - Use in select dropdown
<select value={formData.Status}>
  <option value="Active">Active</option>
  <option value="Inactive">Inactive</option>
  <option value="Pending">Pending</option>
</select>

// WRONG - Don't use numeric values like Dataverse
{ Status: 0 }      // SharePoint expects "Active", not 0
{ Priority: 1 }    // SharePoint expects "High", not 1

Multi-select choice columns return a semicolon-delimited string from the connector:

// Multi-select choice value
const categories = item.Categories;  // "Engineering;Design;Marketing"
const categoryArray = categories?.split(";") || [];

Lookup Columns - How They Appear

Lookup columns in SharePoint reference items from another list. In the generated services, they appear as objects with Id and Value:

// Reading a lookup column
const item = await SharePointOnlineService.GetItem({
  dataset: siteUrl,
  table: "Tasks",
  id: itemId
});
// item.AssignedTo = { Id: 5, Value: "John Smith" }
// item.Project = { Id: 12, Value: "Website Redesign" }

// Creating/updating with a lookup -- use the ID
await SharePointOnlineService.PatchItem({
  dataset: siteUrl,
  table: "Tasks",
  id: itemId,
  item: {
    AssignedToId: 5,         // Use the Id suffix
    ProjectId: 12            // Use the Id suffix
  }
});

Person/Group columns are special lookup columns that reference the site's User Information List:

// Person column appears as lookup with user info
// item.AssignedTo = { Id: 15, Value: "<person-display-name>" }
// To set: use AssignedToId with the user's site user ID

Common SharePoint API Errors

Column Type Quick Reference

Generated Service Patterns

After running npx power-apps add-data-source -a sharepointonline, the generated SharePointOnlineService.ts provides methods that work across all connected lists. The dataset (site URL) and table (list name) parameters select which list to operate on:

import { SharePointOnlineService } from "../generated/services/SharePointOnlineService";

// List all items
const result = await SharePointOnlineService.GetItems({
  dataset: "https://contoso.sharepoint.com/sites/mysite",
  table: "My List"
});
const items = result.value || [];

// Get single item by ID (SharePoint IDs are integers)
const item = await SharePointOnlineService.GetItem({
  dataset: siteUrl,
  table: "My List",
  id: 42
});

// Create item
const newItem = await SharePointOnlineService.PostItem({
  dataset: siteUrl,
  table: "My List",
  item: { Title: "New Item", Status: "Active" }
});

// Update item
await SharePointOnlineService.PatchItem({
  dataset: siteUrl,
  table: "My List",
  id: 42,
  item: { Status: "Completed" }
});

// Delete item
await SharePointOnlineService.DeleteItem({
  dataset: siteUrl,
  table: "My List",
  id: 42
});

Use Grep to find specific methods in src/generated/services/SharePointOnlineService.ts (generated files can be very large -- see connector-reference.md).