SharePoint Excel Metadata Automation

This documents a Power Automate + Office Scripts solution that extracts metadata from fixed-template Excel workbooks uploaded to a SharePoint document library and writes that metadata back into SharePoint columns.

It works around a specific timing issue: writing to the file via Office Script briefly locks it, which causes a following SharePoint metadata update to fail if you don't account for it. The flow retrieves the file's shared lock ID and passes it into the metadata update call to avoid that race condition; see Get Shared Lock ID.

Most public examples of the extraction pattern also assume the workbook uses a real Excel Table, since that lets the script call getTable() and stop there. This solution handles the messier, more common case: a freeform worksheet where some fields sit in fixed cells and others are lists under a header that could be anywhere in the used range. The script locates those headers dynamically (normalized text matching, not a fixed address) and walks down each column until it hits a blank row, instead of depending on a Table structure the source file doesn't have.

---

What the Flow Does

When a new Excel workbook is uploaded to a SharePoint document library, the flow:

1. Detects the newly added file.
2. Runs an Office Script against the uploaded workbook.
3. Extracts metadata from fixed cells and dynamically located columns.
4. Parses the Office Script output as JSON.
5. Retrieves the SharePoint shared lock ID.
6. Updates SharePoint metadata columns using a SharePoint REST API call.

This reduces manual metadata entry and keeps SharePoint library columns aligned with workbook content.

---

High-Level Architecture

SharePoint File Upload
        ↓
Power Automate Trigger
        ↓
Run Office Script from SharePoint Library
        ↓
Office Script Extracts Workbook Metadata
        ↓
Parse JSON
        ↓
Get Shared Lock ID
        ↓
Update SharePoint Metadata via REST API

---

Prerequisites

• Premium/standard connector: Send an HTTP request to SharePoint (used for both the shared lock ID and the metadata update) is not part of every Power Automate licensing tier. Confirm your plan supports it before building this out.
• Office Scripts permissions: the flow owner needs access to run Office Scripts against the target document library.
• Consistent workbook template: fixed-cell addresses and the worksheet name are hardcoded in the script, so every uploaded workbook must follow the same layout.

---

SharePoint Setup

Main Document Library

This is the library where users upload the Excel workbooks.

Example:

Business Documents

The library should contain the metadata columns that will be populated by the flow.

---

Script Storage Location

The Office Script is stored separately as an .osts file in SharePoint.

Example:

Documents / Automation Assets / Metadata Extraction.osts

The script does not need to be stored inside each Excel workbook. Power Automate runs the centrally stored script against each uploaded file.

---

Metadata Extracted

The script extracts the following metadata:

Reporting Period
Client Name
Opportunity ID
Related Opportunity IDs
Planner Name
Reviewer Comments
Product Name
Product Hierarchy Level 1
Product Hierarchy Level 2
Solution ID

Some fields are read from fixed cells. Others are located dynamically by finding column headers in the worksheet.

---

Extraction Logic

Fixed Cell Extraction

These fields are read from fixed cell addresses:

Reporting Period             → D4
Client Name                  → D3
Planner Name                 → M3
Reviewer Comments            → L1
Product Name                 → I5
Product Hierarchy Level 1    → I4
Product Hierarchy Level 2    → I3

---

Dynamic Column Extraction

The script dynamically searches the used range for these headers:

OID#
OID #
Solution ID

It then reads values underneath those headers.

Logic used:

• First OID value becomes the main Opportunity ID.
• Additional OID values become Related Opportunity IDs.
• Duplicate related IDs are removed.
• Repeated occurrences of the main Opportunity ID are excluded from related IDs.
• Related Opportunity IDs are joined with a single space.
• The first nonblank Solution ID value becomes the main Solution ID.
• Extraction stops when the script reaches a completely blank visual-table row.

The visual table blank-row check scans columns:

D:S

---

Office Script

The full script lives in metadata-extraction.ts in this repo.

At a high level, it:

• Reads the worksheet named by the worksheetName parameter (defaults to YourWorksheetName) and returns a structured failure (success: false, with an error message) if the worksheet is missing or empty, instead of throwing.
• Reads the fixed-cell fields directly (see the mapping above).
• Locates the OID#/OID # and Solution ID headers anywhere in the used range (case/whitespace-insensitive match), then walks down each column collecting values until it hits a blank visual-table row.
• Deduplicates related Opportunity IDs and excludes repeats of the main Opportunity ID.
• Returns a single JSON string containing scriptVersion, success, worksheetUsed, metadata, and debugInfo.

---

Power Automate Flow

1. Trigger

Use:

SharePoint → When a file is created (properties only)

Configure:

Site Address: Your SharePoint site
Library Name: Your document library

---

2. Run Office Script

Use:

Excel Online (Business) → Run script from SharePoint library

This action requires both:

Workbook = uploaded Excel file
Script = stored .osts Office Script file

---

Workbook Configuration

Use the uploaded file from the trigger:

Workbook Location: SharePoint site or group location
Workbook Library: Your document library
Workbook: Identifier from the trigger

Important:

Use Identifier, not ID

---

Script Configuration

Point to the .osts file stored in SharePoint:

Script Location: SharePoint site or group location
Script Library: Documents or Automation Assets
Script File: Metadata Extraction.osts

---

Worksheet Parameter

Set the worksheet parameter to:

YourWorksheetName

Update this if your workbook template uses a different worksheet name.

---

Parse JSON

Add:

Data Operations → Parse JSON

The script returns a JSON string containing:

{
  "scriptVersion": "Dynamic Metadata Extraction v4",
  "success": true,
  "worksheetUsed": "YourWorksheetName",
  "metadata": {
    "ReportingPeriod": "Q2 2026",
    "ClientName": "Example Client",
    "OpportunityID": "123456",
    "RelatedOpportunityIDs": "456789 987654",
    "PlannerName": "Example Planner",
    "ReviewerComments": "Example comments",
    "ProductName": "Example Product",
    "ProductHierarchyLevel1": "Example Level 1",
    "ProductHierarchyLevel2": "Example Level 2",
    "SolutionID": "98765"
  },
  "debugInfo": {
    "OIDHeaderFound": true,
    "OIDHeaderAddress": "YourWorksheetName!H22",
    "OIDHeaderText": "OID #",
    "SolutionIDHeaderFound": true,
    "SolutionIDHeaderAddress": "YourWorksheetName!F22",
    "SolutionIDHeaderText": "Solution ID",
    "AllOIDValues": [
      "123456",
      "456789",
      "987654"
    ],
    "UniqueRelatedOIDValues": [
      "456789",
      "987654"
    ],
    "AllSolutionIDValues": [
      "98765"
    ],
    "MainOpportunityID": "123456",
    "RelatedOpportunityIDs": "456789 987654",
    "MainSolutionID": "98765",
    "UsedRangeAddress": "YourWorksheetName!A1:S50",
    "LastUsedColumnIndex": 18
  }
}

---

Get Shared Lock ID

The normal SharePoint Update file properties action can fail if the workbook is locked by Excel Online or Office Scripts.

To avoid this, retrieve the shared lock ID before updating metadata.

Add:

SharePoint → Send an HTTP request to SharePoint

Rename it:

Get shared lock ID

Method

GET

URI

Replace YourLibraryName with the actual SharePoint library name.

_api/web/lists/getbytitle('YourLibraryName')/items(@{triggerBody()?['ID']})/File/Properties?$select=vti_x005f_sourcecontrollockid

Headers

{
  "Accept": "application/json;odata=nometadata",
  "Content-Type": "application/json;odata=nometadata; charset=utf-8"
}

---

Update SharePoint Metadata

Add another:

SharePoint → Send an HTTP request to SharePoint

Method

POST

URI

Replace YourLibraryName with the actual SharePoint library name.

_api/web/lists/getbytitle('YourLibraryName')/items(@{triggerBody()?['ID']})/validateUpdateListItem()

Headers

{
  "Accept": "application/json;odata=nometadata",
  "Content-Type": "application/json;odata=nometadata; charset=utf-8"
}

Body

Replace the FieldName values with the actual SharePoint internal column names from your library.

{
  "formValues": [
    {
      "FieldName": "Reporting_x0020_Period",
      "FieldValue": "@{body('Parse_JSON')?['metadata']?['ReportingPeriod']}"
    },
    {
      "FieldName": "Client_x0020_Name",
      "FieldValue": "@{body('Parse_JSON')?['metadata']?['ClientName']}"
    },
    {
      "FieldName": "Opportunity_x0020_ID",
      "FieldValue": "@{body('Parse_JSON')?['metadata']?['OpportunityID']}"
    },
    {
      "FieldName": "Related_x0020_Opportunity_x0020_IDs",
      "FieldValue": "@{body('Parse_JSON')?['metadata']?['RelatedOpportunityIDs']}"
    },
    {
      "FieldName": "Planner_x0020_Name",
      "FieldValue": "@{body('Parse_JSON')?['metadata']?['PlannerName']}"
    },
    {
      "FieldName": "Reviewer_x0020_Comments",
      "FieldValue": "@{body('Parse_JSON')?['metadata']?['ReviewerComments']}"
    },
    {
      "FieldName": "Product_x0020_Name",
      "FieldValue": "@{body('Parse_JSON')?['metadata']?['ProductName']}"
    },
    {
      "FieldName": "Product_x0020_Hierarchy_x0020_Level_x0020_1",
      "FieldValue": "@{body('Parse_JSON')?['metadata']?['ProductHierarchyLevel1']}"
    },
    {
      "FieldName": "Product_x0020_Hierarchy_x0020_Level_x0020_2",
      "FieldValue": "@{body('Parse_JSON')?['metadata']?['ProductHierarchyLevel2']}"
    },
    {
      "FieldName": "Solution_x0020_ID",
      "FieldValue": "@{body('Parse_JSON')?['metadata']?['SolutionID']}"
    }
  ],
  "sharedLockId": "@{body('Get_shared_lock_ID')?['vti_x005f_sourcecontrollockid']}",
  "bNewDocumentUpdate": true
}

---

SharePoint Internal Column Names

The REST API body must use SharePoint internal column names, not display names.

Example:

Display Name: Client Name
Internal Name: Client_x0020_Name

To find an internal column name:

1. Open the SharePoint library.
2. Go to Library settings.
3. Select the column.
4. Look at the URL.
5. Copy the value after Field=.

Use that value in the FieldName property.

---

Metadata Column Requirements

Metadata columns populated by this flow should generally not be required at upload time.

If a metadata column is required before the automation runs, the trigger may fail before the flow can populate the field.

Recommended approach:

• Keep flow-populated metadata columns optional.
• Create a SharePoint view for missing metadata.
• Use that view to monitor failed or incomplete records.

---

Troubleshooting

Workbook Not Found

Check:

• The workbook field uses Identifier.
• The selected workbook library is correct.
• The file is an .xlsx.
• The flow owner has access to the workbook.

---

Header Not Found

If dynamic extraction does not find OID or Solution ID values, check the debugInfo section from the script output:

{
  "OIDHeaderFound": false,
  "SolutionIDHeaderFound": false
}

If needed, add more accepted header variants to the script:

const oidHeader = findFirstHeader([
    "OID#",
    "OID #",
    "Opportunity ID"
]);

---

Known Limitations

1. Fixed-cell fields require the workbook template layout to remain consistent.
2. Dynamic fields depend on recognizable header text.
3. Person columns require special handling.
4. SharePoint internal column names must be verified.
5. If a header (OID#/OID # or Solution ID) isn't found on the worksheet, the script still returns success: true with that field blank — it doesn't distinguish "header missing" from "header found but no rows underneath." If a field is unexpectedly empty, check debugInfo.OIDHeaderFound / debugInfo.SolutionIDHeaderFound in the flow run history rather than assuming the workbook had no data.

---

Recommended Production Cleanup

After testing:

1. Remove or reduce debugInfo if output size is a concern.
2. Keep scriptVersion for traceability.
3. Confirm all SharePoint internal column names.
4. Confirm all target column types.

---

Final Outcome

This automation populates SharePoint metadata from uploaded Excel workbooks by combining:

• SharePoint file-created triggers
• Office Scripts
• JSON parsing
• Dynamic worksheet header detection
• SharePoint REST API metadata updates
• Shared lock ID handling

The result is a more reliable metadata capture process for Excel-based document libraries, especially where files use fixed templates but do not contain structured Excel Tables.