cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdamW
Helper III
Helper III

Can't List Rows in Excel when table contains SQL data merged with SharePoint data?

Can anyone explain this error? This is what I am getting when I try to use the List Rows Present in a Table for Excel action. 

 

I have an Excel file that contains a power query where I am mixing together SQL data with a SharePoint list.  Why is this not allowed?

 

 

The dynamic invocation request failed with error: Unexpected error occurred when calling the ApiHubsRuntime API: 'Microsoft.Azure.ProcessSimple.Data.Entities.Exceptions.ProcessSimpleDataException: The ApiHubsRuntime API call failed with http status code 'BadGateway' and response content '{ "error": { "code": 502, "source": "unitedstates-002.azure-apim.net", "clientRequestId": "d5be53f2-3f6b-453f-a323-xxxx-xxxxxxx", "message": "BadGateway", "innerError": { "status": 502, "message": "This workbook cannot be edited because it contains the following features:\r\n\r\n• SharePoint lists\r\n\r\nTo edit this workbook, open the workbook for viewing, select Save a Copy from the File tab, and edit the new copy.\r\nclientRequestId: d5be53f2-3f6b-453f-a323-xxxx-xxxxxxx", "error": { "message": "This workbook cannot be edited because it contains the following features:\r\n\r\n• SharePoint lists\r\n\r\nTo edit this workbook, open the workbook for viewing, select Save a Copy from the File tab, and edit the new copy." }, "source": "excelonline-ncus.azconn-ncus.p.azurewebsites.net" } } }' at Microsoft.Azure.ProcessSimple.Data.DataProviders.AzureResourceManagerDataProvider.<>c__DisplayClass52_0`2.<<CallAzureResourceManager>b__0>d.MoveNext() in X:\bt\1220470\repo\src\processsimple\Roles\ProcessSimple.Data\DataProviders\Services\AzureResourceManagerDataProvider.cs:line 587 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.WindowsAzure.ResourceStack.Common.Algorithms.AsyncRetry.<Retry>d__3`1.MoveNext() in X:\bt\1207438\repo\src\common\core\algorithms\AsyncRetry.cs:line 0 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.WindowsAzure.ResourceStack.Common.Algorithms.AsyncRetry.<Retry>d__3`1.MoveNext() in X:\bt\1207438\repo\src\common\core\algorithms\AsyncRetry.cs:line 171 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.ProcessSimple.Data.DataProviders.AzureResourceManagerDataProvider.<CallAzureResourceManager>d__52`2.MoveNext() in X:\bt\1220470\repo\src\processsimple\Roles\ProcessSimple.Data\DataProviders\Services\AzureResourceManagerDataProvider.cs:line 0'.

3 REPLIES 3
eliotcole
Super User
Super User

I'm not sure, but it looks like somewhere in the process it thinks you're trying to edit soemthing that you should be doing via another method. (editing the SP data)

 

Purely out of curiousity ... Is there any reason why excel needs to be involved at all in this situation? It seems that if you're handling SQL and SharePoint data you might be better served doing whatever you need either in Power Automate or in PA with some of the AI/Dataverse stuff.

 

Is this just a legacy thing?

 

If so, can the same data be arranged using one of the methods that don't involve excel, then you just update/create/replace a static excel sheet somewhere for whomever needs the data in excel?

AdamW
Helper III
Helper III

The reason Excel is involved is because I got the same error in Power Query online when trying to merge this SQL table with SharePoint.  It just won't allow it.

 

My workaround has been to use Excel and then link a different Excel workbook to the one with the query and update it that way.  It's a tedious workaround and I need to find a different way.

 

I'm not editing any SharePoint data.  I am only merging it to some SQL data.

eliotcole
Super User
Super User

So (sorry, not to belabour the point), could you not create a copy of the SharePoint list, then use a Power Automate SQL connector to pull in the SQL data and merge both into the new SP list?

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Users online (3,049)