Microsoft 365 : SharePoint online – REST API – Getting list items – resolving error – Cannot complete this action. OR Please try again – Microsoft.SharePoint.SPException or Input string was not in a correct format – System.FormatException

Hi All,

Greetings for the day!!! Today new issue and solution 🙂

Background / Details

• We have SharePoint list – DemoList
• List have columns which are created from UI
• So internal names are set with special characters as given name while creating columns from UI
• For ex. one of the column created by our user having name with special character – ?test – internal name becomes – _x003f_test as

• We are using REST API to get all items from the respective list

NOTE : This error is not happening in all tenants. I have noticed that in some tenants (my local tenant) it worked

Issue

• While using REST API – https://knowledgejunction1.sharepoint.com/_api/web/getlistbytitle(‘DemoList’)/items we are getting an following issue

This XML file does not appear to have any style information associated with it. The document tree is shown below.
-2147467259, Microsoft.SharePoint.SPException Cannot complete this action. Please try again.

• This is because of site column created with special character – ?test – internal name – _x003f_test
• When we select this field in REST call, we get an error as

Microsoft.SharePoint.SPException The field or property ‘_x003f_test’ does not exist.

• Also when we access the item with respective id like – https://knowledgejunction1.sharepoint.com/_api/web/getlistbytitle(‘DemoList’)/GetItemById(2), getting an following error

-1, System.FormatException Input string was not in a correct format.

Solution / Workaround – There are couple of workaround

• When we exclude this column from REST API using “select“, it started like – https://knowledgejunction1.sharepoint.com/_api/web/getlistbytitle(‘DemoList’)/items?$select=Title
• Either create same field without special character and start using those
• OR recreate list with proper naming conventions for site columns / list columns
• Follow best practice for creating site columns – Never ever create site columns from UI

Best Practice to create Site Columns

• Use PowerShell to create site columns
  • Create proper CSV file with details – column name like – “DisplayName”, “InternalName”, “Required” etc

• Use PowerShell script read from CSV and create site columns.
• We have an article to create site columns using PowerShell , please have a look – Microsoft 365 : SharePoint Online – Create Site Columns in a SharePoint online site through PowerShell – https://knowledge-junction.in/2022/07/26/microsoft-365-sharepoint-online-create-site-columns-in-a-sharepoint-online-site-through-powershell/

Thanks for reading !! HAVE A FANTASTIC LEARNING AHEAD 🙂 LIFE IS BEAUTIFUL 🙂