Using sharepoint list REST API with Officescript in Excel

Question

I need to connect to sharepoint lists in web excel for both read and write, to use excel essentially like a form where I can build smarter calculations (yes, I know powerapps/automate is a better solution, however there are some company limitations on this front). So far my script looks like this:

 let bob = await getListData();
 let mySheet = workbook.getActiveWorksheet();
 let myCell = mySheet.getCell(1,1)
 myCell.setValue(bob)
 
}

async function getListData(){
 let dataj='test';
 let headers:{};
 headers ={
   "method":"GET",
   "credentials": "same-origin", 
   "headers": {
     "accept": "application/json;odata=verbose",
     "accept-language": "en-US,en;q=0.9",
     "content-type": "application/json;odata=verbose"}
 }
 await fetch("https://mySite.sharepoint.com/sites/myGroup/_api/web/lists/GetByTitle('myList')/items", headers)
 .then((data) => {dataj=data.statusText; console.log(dataj)});
 return dataj
}

I've tested the second function in the browser console & I get the expected response, but in officescript I get 'forbidden', while if I change the credentials to 'include' I get 'failed to fetch'. Anyone got ideas?

Have you tried using PowerAutomate with the SharePoint connector? You may have an easier time with that. — Brian Gonzalez
– Brian Gonzalez, Commented Oct 9, 2022 at 14:36
Sadly, my org doesn't allow anyone outside the central team to share PowerAutomate, so while it would be easier for me, the flow won't be available to others. — RowanC
– RowanC, Commented Oct 11, 2022 at 3:28
Have you looked at using LogicApps instead of PowerAutomate? — Skin
– Skin, Commented Oct 12, 2022 at 2:22

Yutao Huang · Accepted Answer · 2022-10-19 17:25:20Z

2

Some thoughts on the possible causes to the fetch failures:

CORS (https://en.wikipedia.org/wiki/Cross-origin_resource_sharing), which could block you from making web request from the origin/domain where Office Scripts is running (https://*.officescripts.microsoftusercontent.com) to a different domain (in your case, https://mySite.sharepoint.com). Here are some potential workaround ideas. Basically, if you don't have control over the API service provider (in your case the SharePoint) to make changes to the APIs to support CORS on their server side, you'll need to find a way to "bridge" your requests through some sort of middle-tier or proxy service. But this might also be challenging to you due to the Azure limitation in your organization, which might block you from building a web service? Or do you think you would be able to build something using other web hosting providers?
Auth. The SharePoint API is a protected API that requires you to make an authenticated call. You wouldn't need to do this while testing an API call directly from the browser console of the Excel Online web page since I guess that API call could inherit some context (e.g., cookies?) of that authenticated Excel Online session because they are within the same origin. But you'll need to find a way to acquire an access token to make an authenticated call if you do that from a different origin (https://*.officescripts.microsoftusercontent.com), or even from the proxy service if you are to build one. This could also be quite challenging because token acquisition normally requires some extra pre-configurations in AzureAD besides you may not even be able to do it from inside Office Scripts - the token acquisition flow requires either you put your AAD client-secret in the script code or use the redirect/pop-up approach that definitely won't work in Office Scripts.

Unfortunately, I think this overall is a tough problem to solve with only Office Scripts at the moment. If you believe there are values to support making authenticated calls (esp. to those official Microsoft/Office APIs) from inside Office Scripts, please feel free to submit a suggestion/idea at: https://feedbackportal.microsoft.com/feedback/forum/c23f3b77-f01b-ec11-b6e7-0022481f8472.

answered Oct 19, 2022 at 17:25

Yutao Huang

1,5913 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RowanC Over a year ago

thanks for your response. I agree, and went with a much clunkier vba solution with desktop app. I think powerapps is what I needed here, but our central team couldn't help on the timelines we need. I do however think that officescript should inherit the current users permissions into the broader office365 env (personal view, likely not aligned with cybersecurity principles). In terms of external authenticated api's, I think it's going to be something that's useful in the future. Most api's have some layer of auth...but we may then require a secure storage for api keys, and oAuth integration.

Yutao Huang Over a year ago

@RowanC - Yes, I totally agree. The capability to interact with other Office 365 apps (e.g., SharePoint, OneDrive for Business, Teams, Exchange, etc.) from within Office Scripts using the current user's permissions will definitely be a very powerful feature.

Collectives™ on Stack Overflow

Using sharepoint list REST API with Officescript in Excel

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related