{"id":164,"date":"2018-07-18T08:39:30","date_gmt":"2018-07-18T08:39:30","guid":{"rendered":"http:\/\/4us2use.at\/?p=164"},"modified":"2019-03-07T14:37:25","modified_gmt":"2019-03-07T14:37:25","slug":"sharepoint-excel-data-refresh-with-o365-pro-plus","status":"publish","type":"post","link":"http:\/\/4us2use.at\/?p=164","title":{"rendered":"SharePoint Excel Data Refresh with O365 Pro Plus"},"content":{"rendered":"

My environment:
\n– SharePoint on-prem 2013
\n– Office Online Server
\n<\/span> – Excel Office Pro Plus (2016)
\n– Data source = SharePoint list<\/span><\/p>\n

Problem Description:<\/p>\n

One day I wasn’t anymore able to auto refresh my SharePoint embedded charts that pulled data from SP lists.
\nI had to do some research until I found that it’s a combination of my new Excel version and the Office Online server. Due to changes in the security model, the standard settings didn’t work anymore.<\/p>\n

Solution:<\/p>\n

    \n
  1. Enable “From OData Data Feed (Legacy)” in the Excel options on the “Data” tab
    \n\"\"<\/li>\n
  2. In Excel on the Data tab click on “Get Data – Legacy Wizards – From OData Data Feed (Legacy)<\/li>\n
  3. Connect to your site adding \/_vti_bin\/listdata.svc<\/strong> to your URL
    \n\"\"<\/li>\n
  4. on the next page select the list where you want to pull data from<\/li>\n
  5. now on the last wizard page it’s very important, that you use a stored account instead of Windows authentication (create on in “Secure Store Service” – Link<\/a>)
    \n\"\"<\/li>\n<\/ol>\n

    Now you can go on creating your charts and then embed it in SharePoint with the Excel web part.
    \n\"\"
    \nHave fun!<\/p>\n

    If that still doesn’t work, some other things to check:
    \n– check, if the SecureStoreService is set to type “Individual”
    \n– In the members section add the FQDN$ (if on-prem) of the OOS servers
    \n– in OOS configuration make sure -AllowHttpSecureStoreConnection is set to TRUE
    \n– in the c2wtshost.exe.config (under path\u00a0C:\\Program Files\\Windows Identity Foundation\\v3.5\\) make sure, that following line is NOT commented:
    \n<add value=”NT AUTHORITY\\Network Service” \/>
    \n(restart OOS service)
    \n– make sure, all BIServers are registered in OOS
    \nNew-OfficeWebAppsExcelBIServer\u00a0\u2013ServerId\u00a0YourSQLServer\\POWERPIVOT<\/p>\n\"1\"2\"3\"4\"5 (1<\/strong> votes, average: 5.00<\/strong> out of 5)
    <\/span><\/span>Loading...<\/span>\n","protected":false},"excerpt":{"rendered":"

    My environment: – SharePoint on-prem 2013 – Office Online Server – Excel Office Pro Plus (2016) – Data source = SharePoint list Problem Description: One day I wasn’t anymore able to auto refresh my SharePoint embedded charts that pulled data from SP lists. I had to do some research until I found that it’s a … <\/p>\n

    Continue reading “SharePoint Excel Data Refresh with O365 Pro Plus”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4],"tags":[],"_links":{"self":[{"href":"http:\/\/4us2use.at\/index.php?rest_route=\/wp\/v2\/posts\/164"}],"collection":[{"href":"http:\/\/4us2use.at\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/4us2use.at\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/4us2use.at\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/4us2use.at\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=164"}],"version-history":[{"count":8,"href":"http:\/\/4us2use.at\/index.php?rest_route=\/wp\/v2\/posts\/164\/revisions"}],"predecessor-version":[{"id":225,"href":"http:\/\/4us2use.at\/index.php?rest_route=\/wp\/v2\/posts\/164\/revisions\/225"}],"wp:attachment":[{"href":"http:\/\/4us2use.at\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/4us2use.at\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=164"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/4us2use.at\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}