Four ways to embed SharePoint list text, compared

11 October 2026

Tags: Relational databasesSearch and vector storesAIDesktopCommand line

“Water dripping through ceiling tiles above the comms room, Block C.” The new incident has probably happened before, and the fix is somewhere in four years of building faults kept in a SharePoint list called Incidents. That list holds just over 11,000 rows: a Title, a long free-text Description, a Location lookup into a Buildings list, Category, Priority and Status choices, a Reported By person column and a Resolution note added when the job closes. The facilities lead wants one feature in the internal app on Supabase: when a new incident comes in, show the ten most similar past incidents and what fixed them.

Keyword search misses these. Last winter’s fix sits under “condensate pump failure on AHU-3, leak into ceiling void”, which shares one word with the new report. Embeddings find it, so someone already has a Power Automate flow, a Jupyter notebook and the Azure AI Search portal open in three tabs.

Choosing the text to embed

Before choosing a route, decide which columns form the text. For Incidents we embed Title and Description, in that order, as one string per item. Resolution is tempting, but a new incident has no resolution yet, so including it makes past rows and the query text look different in a way that has nothing to do with the fault itself. We show the Resolution beside each match instead.

Choice and lookup columns work better as filters. “Block C” and “Plumbing” are exact values, which a WHERE clause handles precisely, and pushing them into the embedded string blurs similarity: two unrelated faults in the same building score closer than they should.

If Description is an enhanced rich text column, SharePoint stores HTML, and whatever reads it embeds the tags along with the words. Check the column setting before you pay to embed 11,000 rows of <div> tags.

What does a Power Automate embedding flow cost to run and keep correct?

The flow needs no server, so it usually comes first. Its trigger is the SharePoint connector’s “When an item is created or modified”, the next step is the Azure OpenAI connector’s “Get an embedding” action, and the last step writes the vector to a table through the SQL Server connector. It works on the first test item within an hour, and the cost of keeping it correct shows up later.

The trigger fires on every edit. Microsoft’s connector reference says it triggers “each time it is modified”, so when an engineer changes Status from Open to Closed, the flow calls the embedding model again for text that has not changed. You can add the “Get changes for an item or a file (properties only)” action and a condition on whether Description changed, but that action needs versioning turned on for the list, and it is one more step to maintain in every run.

Deletes need a second flow. The “When an item is deleted” trigger exists, and the connector reference says it “can only be used by site collection admins of the site where the list is located”. Many facilities teams do not have that role, so deleted incidents stay in the vector table and keep turning up as matches.

The backfill is a separate job. A flow trigger only sees new edits, so the 11,000 existing rows need a one-off flow that pages through the list past the 5,000-item view threshold. The SharePoint connector is throttled at 600 API calls per connection per 60 seconds, and a backfill that also calls the model per item meets that limit quickly.

Long text and model changes have no home. The connector’s embedding action rejects input over 2,048 tokens, so a long Description needs truncating in an expression. When you move to a newer embedding model, nothing records which rows were embedded with which model. The Azure OpenAI and SQL Server connectors are both premium in Power Automate, so the flow’s owner needs a premium licence too.

What a LangChain or LlamaIndex script runs into

A developer reaches for Python and the framework loaders, which are built for files. LangChain’s SharePointLoader takes a document_library_id, and LlamaIndex’s SharePointReader loads files from a folder or site pages. Neither reads list items, so the Incidents list needs your own Microsoft Graph code anyway.

The issue tracker shows what teams hit even on files. LangChain GitHub issue 19938 reports the loader returning an empty list unless explicit object ids are passed. Issue 22663 describes a .docx failing with a missing MIME type, and its author authenticated by copying a URL by hand, which is fine in a notebook and awkward on a schedule. Both issues are closed now, but you own the loader’s behaviour when the next one appears.

Once you write the Graph code yourself, the list is the hard part. You page past 5,000 items with @odata.nextLink, resolve the Location lookup to a building name, back off on HTTP 429 with the Retry-After header, and add delta queries to avoid a full re-read every night. Then you write the delete handling and model bookkeeping the flow also lacked, and someone has to be on call for all of it.

What the Azure AI Search SharePoint indexer covers

Azure AI Search’s SharePoint in Microsoft 365 indexer covers more than people expect. Microsoft Learn documents incremental indexing, automatic detection of deleted files and list items, text extraction from Office and PDF files, and an optional skillset for enrichment. If what you want to search is Word and PDF files in document libraries, this is a strong option.

For a list like Incidents, read the status labels. The indexer is marked preview, which Microsoft says is not recommended for production workloads, and needs a registration form. Indexing lists and their item field values arrived in the 2026-05-01-preview REST API, as did ACL ingestion for list items. Each list column has to be field-mapped by hand into the index, and the item’s values arrive in a content field as JSON.

The other limit is shape. The result is a search index, so “similar incidents in Block C that are still open” becomes a vector query plus an OData filter, with no join to the Buildings list or to an app’s own tables, and no Postgres rows for a Supabase app.

How does embedding on sync work in SPSync?

SPSync takes a different route: replicate the list into a database on a schedule, with embedding as a configured step of each sync. It reads SharePoint with delta queries, writes the rows, then embeds only rows whose text changed. The pgvector destination writes a vector column with an HNSW index into the same table as the relational columns, and the Azure AI Search destination writes a vector field on each item’s document.

For the facilities team, the destination is their Supabase project’s Postgres. The pgvector destination needs PostgreSQL 15 or newer, and spsync doctor checks the server version and that the vector extension can be created before the first run. Use Supabase’s direct or session connection string, because the first run creates tables and the extension.

Connect the site and pick the lists

In the desktop app, connect the facilities site, tick Incidents and Buildings, and on the destination screen choose Postgres + pgvector. The embedding fields are on the same screen. From the command line, the destination block of the replication config looks like this:

destination:
  kind: pgvector
  connection_string_env: FACILITIES_DB_URL
  dataset_name: facilities
  pgvector_embedding_provider: azure-openai
  pgvector_embedding_model: facilities-embed   # your Azure OpenAI deployment name
  pgvector_embedding_dimensions: 1536             # text-embedding-3-small
  pgvector_embedding_columns: [title, description]
  pgvector_api_key_env: AOAI_API_KEY
  pgvector_azure_endpoint_env: AOAI_ENDPOINT

The column names are destination names, as spsync discover schema shows them, joined in the order listed. The key and endpoint come from environment variables, so no secret sits in the file.

Run the first sync

spsync run facilities.yaml does the full load first. Afterwards the facilities schema holds fac_incidents with location_id and location_value from the lookup, reported_by_user_id joining to sp_users, the system columns sp_item_id, sp_modified and sp_deleted_at, and two new columns: sp_embedding, a vector(1536), and sp_embedding_hash. An HNSW index using cosine distance sits on sp_embedding.

To keep a wide vector column out of every row your Supabase app reads, set pgvector_embedding_target: side_table and the vectors go into fac_incidents_embeddings, keyed on the same primary key. A similarity query then joins the two on the item id.

What happens on the next sync

The hash is a SHA-256 of the embedded text with the model name folded in. Each sync recomputes it for every live row and sends only rows whose hash changed to the model. Closing an incident changes Status, not Title or Description, so it costs one hash comparison and no embedding call. Editing the Description re-embeds that one row.

Deletes follow the replica. A deleted incident keeps its row with sp_deleted_at set, and queries filter on sp_deleted_at IS NULL. On the Azure AI Search destination the document is removed from the index instead.

Model provenance is recorded in sp_embedding_meta: one row per table and embedding column with the model name and dimensions. Change pgvector_embedding_model and the next sync re-embeds every live row, so one column never mixes two models. Change the dimensions and SPSync writes a fresh sp_embedding_v2 column, because pgvector cannot resize a vector column in place.

SPSync embeds the joined text as one vector per row, which suits a Description of a few paragraphs. A column holding pages of text is really a document, and splitting it into passages is a job for your own pipeline reading the replica.

The four routes side by side

Choosing OpenAI, Azure OpenAI or a local model

All three providers use the same config keys and change detection, so the choice comes down to where the text goes and who pays. Switching later costs one full re-embed, since the model name is part of every row’s hash.

Azure OpenAI is the usual answer for a Microsoft 365 organisation, because the text goes to a resource in your own Azure subscription. Where it is processed depends on the deployment type as well as the resource’s region; the post on keeping SharePoint data in region for AI explains Global, Data Zone and Standard deployments. pgvector_embedding_model is your deployment name, and pgvector_azure_endpoint_env names the endpoint variable. OpenAI’s public API uses the same keys without the endpoint, for a team with no Azure OpenAI resource.

The local provider runs fastembed on the machine doing the sync. There is no API key and no endpoint, and after the model weights download once on first use, no text leaves that machine. The default model is BAAI/bge-small-en-v1.5 at 384 dimensions:

  pgvector_embedding_provider: local
  pgvector_embedding_model: BAAI/bge-small-en-v1.5
  pgvector_embedding_dimensions: 384

A local model is the right call in three situations we see often. The list holds text your compliance team will not send to any external API, such as HR cases or patient feedback. You want to try semantic search on a laptop before anyone raises a purchase order. Or the text is short English prose where a small model matches well enough, which fault descriptions usually are. Two constraints apply. Your app has to embed the search text with the same model at query time, and the English default model is a poor fit for multilingual lists.

What does a similarity query look like afterwards?

“Find incidents like this one” for incident 10482 is one SQL statement. It compares against the stored vector of the incident the engineer has open, so the query itself makes no embedding call:

SELECT sp_item_id, title, location_value, resolution
FROM facilities.fac_incidents
WHERE sp_deleted_at IS NULL
  AND sp_item_id <> 10482
  AND status = 'Closed'
ORDER BY sp_embedding <=> (
  SELECT sp_embedding FROM facilities.fac_incidents WHERE sp_item_id = 10482
)
LIMIT 10;

<=> is pgvector’s cosine distance, which matches the index SPSync builds, and the subquery follows the pgvector README’s pattern for the nearest neighbours of a row. For free text typed into a search box, the app embeds the text with the same model and passes the vector in place of the subquery. In Supabase, wrap the statement in a Postgres function and call it with supabase.rpc, and add row-level security on fac_incidents if different teams should see different buildings. SPSync carries no SharePoint item permissions into the replica, so the database’s own roles and policies decide who sees which rows.

One pgvector behaviour matters once you add filters. The HNSW index finds candidates first and the WHERE clause filters them afterwards. The pgvector README gives the example of a filter matching 10% of rows returning only 4 rows on average with default settings. Set hnsw.iterative_scan (pgvector 0.8.0 and later) so the index keeps scanning until it has enough rows.

Where a flow or the indexer still fits

A flow is fine for a small list that rarely changes and never loses items, such as a few hundred policy summaries edited a handful of times a year. Re-embedding on every edit costs little at that size, and the team already knows Power Automate.

The Azure AI Search indexer is the better fit when the content is files. SPSync replicates document-library metadata and does not read file contents, so if the question is “find the contract clause like this one”, the indexer’s document cracking does work SPSync does not do. You can run both: the indexer for documents, and a replica for lists, where the questions mix similarity with filters and joins.

The embeddings and semantic search use case and the pgvector, Azure AI Search and Supabase destination pages cover setup in more depth, and licensing explains the editions. Keeping a SharePoint vector index current as items change goes deeper on re-embedding after edits and deletes. For counts, read Why Copilot cannot count the rows in your SharePoint list, and for plain relational data, what it costs to keep a SharePoint export script running.