CODEWITHSUNDEEP
excelrestsharepointoffice-scripts
RowanC
RowanC

Reputation: 1649

Using sharepoint list REST API with Officescript in Excel

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?

Upvotes: 0

Views: 1521

Answers (1)

Yutao Huang
Yutao Huang

Reputation: 1571

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.

Upvotes: 2

Related Questions