Leveraging The Sharepoint Rest Api To Inspect List Columns

What are List Columns and Why Inspect Them?

SharePoint lists serve as containers for data, with columns defining the type of information stored for each item. Columns have names, data types, and other configurable settings. As an administrator or developer, you may need to retrieve details about list columns or modify column configurations programmatically.

Key reasons for inspecting SharePoint list columns via the REST API include:

• Auditing columns during governance reviews
• Propagating columns to other lists site-wide
• Synchronizing columns with external data sources
• Building custom SharePoint interfaces and solutions

Using the REST API for Column Inspection

SharePoint provides a REST API with endpoints for working with list columns without needing direct database access. Key capabilities include:

• Querying metadata like column names, data types, and settings
• Modifying column configurations
• Deleting columns
• Expanding list items to reveal column values

Common authentication methods for the SharePoint REST API include OAuth, API keys, and standard credentials. Apps and solutions can programmatically inspect columns site-wide given sufficient permissions.

Retrieving List Column Metadata

A GET request to the list endpoint with the “columns” select filter retrieves details for columns in that list. For example, sending a GET request to:

https://contoso.sharepoint.com/sites/team/_api/web/lists/getbytitle('Orders')/columns?$select=Title,DataType,Required,DefaultFormula,Hidden

Would return column metadata with Title, Data Type, Required flag, Default Formula, and Hidden status for the “Orders” list. We can see multiple columns and their configurations in the response.

Example Request and Response

Request:

GET https://contoso.sharepoint.com/sites/team/_api/web/lists/getbytitle('Orders')/columns?$select=Title,DataType,Required,DefaultFormula,Hidden

Response:

{
  "value": [
    {
      "Title": "Requestor",
      "DataType": "User",  
      "Required": true,
      "DefaultFormula": null,
      "Hidden": false
    }, 
    {
      "Title": "Priority",
      "DataType": "Choice",
      "Required": false,
      "DefaultFormula": "Medium",
      "Hidden": false 
    }
  ]
}

This returns key metadata for two columns in the Orders list – Requestor and Priority. We can see data types, formulas, and other useful configuration details.

Updating List Column Settings

A PATCH request allows modifying column settings and metadata for an existing list column. For example, changing the Requestor column to no longer be a required user value:

Example Request

PATCH https://contoso.sharepoint.com/sites/team/_api/web/lists/getbytitle('Orders')/columns('Requestor')

{
    "Required": false
}

This request body sets the Required attribute to false in the metadata for the Orders list Requestor column. The API will return a 204 No Content response if successful.

Handling Errors

If the update fails, the API may return a 409 Conflict error indicating improper data types or values in the request. Proper error handling is necessary.

Deleting Columns from a SharePoint List

We can delete a list column by sending a DELETE request to the column endpoint. This removes the column and associated data entirely. An alternative is deactivating columns via updates.

Example Request

DELETE https://contoso.sharepoint.com/sites/team/_api/web/lists/getbytitle('Orders')/columns('Status')

This will delete the Status column in the Orders list. The API will return 204 No Content if deletion succeeds.

Inspecting Column Values in List Items

When selecting list items via the API, we can choose to expand and reveal column values. For example, retrieving orders with column data exposed:

GET https://contoso.sharePoint.com/sites/team/_api/web/lists/getbytitle('Orders')/items?$expand=FieldValuesAsText

Results will include the FieldValuesAsText collection for each order containing entries with internal column names and values.

Example Request and Response

Request:

 
GET https://contoso.sharePoint.com/sites/team/_api/web/lists/getbytitle('Orders')/items?$expand=FieldValuesAsText&$top=1 

Response:

{
  "value": [
    {
      "ID": 112,
      "FieldValuesAsText": {
        "Requestor": "User1", 
        "Priority": "High",
        "Status": "Closed"
      }
    }
  ]
}

We can see values for columns like Requestor, Priority, and Status exposed directly on list items.

Considerations for Large Lists

Expanding list items does add verbosity. Alternate patterns like $select may be better for large lists with thousands of items.

Additional Column Inspector Options

Further capabilities for inspecting SharePoint columns include:

• Filtering – Query columns modified after a certain date for governance.
• Ordering – Sort by column type for propagation jobs.
• Managing schemas – Add/update column schemas that dictate available settings.
• Lookups and relationships – Build custom cascading dropdowns.

Next Steps for Leveraging Column Metadata

Key next steps for extending solutions with accessed column data:

• Building interfaces – Auto-generate SharePoint forms and views.
• Syncing – Send column data to external databases.
• Analytics – Create Power BI dashboards from column values.
• Governance – Enforce policies for appropriate metadata and settings.