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, Oct 09 '22 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, Oct 11 '22 at 03:28
Have you looked at using LogicApps instead of PowerAutomate? — Skin, Oct 12 '22 at 02:22

score 2 · Accepted Answer · answered Oct 19 '22 at 17:25

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.

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. — RowanC, Oct 21 '22 at 01:08
@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. — Yutao Huang, Oct 22 '22 at 02:50

Using sharepoint list REST API with Officescript in Excel

1 Answers1