Unable to import from Sharepoint Excel file to an R dataset

Question

I am trying to download an Excel in Sharepoint site into an R dataset. But I keep getting errors. I have also tried searching for solutions across forums for this issue. Although I got many updates, none of them were working for me. Here is what I have tried so far.

METHOD 1:

    library(readxl)
    library(httr)
    url1 <- 'http://<companyname>.sharepoint.com/sites/<sitename>/Shared%20Documents/General/TRACKERS/<FolderName>/<TrackerName>.xlsx?d=wbae96ce171e14926863e453a8bec146a?Web=0'
    GET(url1,write_disk(tf <- tempfile(fileext = ".xlsx")))
    df <- read_excel(tf,sheet = "sheetname")

OUTPUT 1:

GET(url1,write_disk(tf <- tempfile(fileext = ".xlsx")))
Response ['https://<companyname>.sharepoint.com/sites/<sitename>/Shared%20Documents/General/TRACKERS/<FolderName>/<TrackerName>.xlsx?d=wbae96ce171e14926863e453a8bec146a?Web=0']
Date: 2020-08-06 08:43
  Status: 400
  Content-Type: text/html; charset=us-ascii
  Size: 311 B
<ON DISK>  C:\Users\
\<username>\AppData\Local\Temp\RtmpuM3YpD\file2c646e4c5d50.xlsx
df <- read_excel(tf,sheet = "sheetname")
Error: Evaluation error: zip file 'C:\Users\<username>\AppData\Local\Temp\RtmpuM3YpD\file2c646e4c5d50.xlsx' cannot be opened.

Please note that I had added “?Web=0” at the end of the url to make the xls directly download.

METHOD 2:

    url1 <- 'http://<companyname>.sharepoint.com/sites/<sitename>/Shared%20Documents/General/TRACKERS/<FolderName>/<TrackerName>.xlsx?d=wbae96ce171e14926863e453a8bec146a?Web=0'
    destfile <- "C:/Users/<username> /Downloads/<TrackerName>.xlsx"
    download.file(url = url1,destfile = destfile)
    df <- read_excel(destfile,sheet = "sheetname")

OUTPUT 2:

trying URL …

cannot open URL …

HTTP status was '403 FORBIDDEN'Error in download.file(url = url1, destfile = destfile) :

cannot open URL …

METHOD 3:

    url1 <- 'http://<companyname>.sharepoint.com/sites/<sitename>/Shared%20Documents/General/TRACKERS/<FolderName>/<TrackerName>.xlsx?d=wbae96ce171e14926863e453a8bec146a?Web=0'
    
    GET(url1,authenticate("<myusername>","<mypassword>", type = "any"),write_disk(tf <- tempfile(fileext = ".xls")))
    df <- read_excel(tf,sheet = "sheetname")

OUTPUT 3:

GET(url1,authenticate("<myusername>","<mypassword>", type = "any"),write_disk(tf <- tempfile(fileext = ".xls")))

Response ['https://<companyname>.sharepoint.com/sites/<sitename>/Shared%20Documents/General/TRACKERS/<FolderName>/<TrackerName>.xlsx?d=wbae96ce171e14926863e453a8bec146a?Web=0']
 
 Date: 2020-08-06 09:04

  Status: 400

  Content-Type: text/html; charset=us-ascii

  Size: 311 B

\<ON DISK>  C:\Users\<username>\AppData\Local\Temp\RtmpuM3YpD\ file2c6456bd6d20.xlsx
df <- read_excel(tf,sheet = "sheetname")
Error: Evaluation error: zip file 'C:\Users\<username>\AppData\Local\Temp\RtmpuM3YpD\ file2c6456bd6d20.xlsx' cannot be opened.

Of course, Initially, I tried reading the excel from Sharepoint directly (Method 4 below). But that didn’t work. Then I tried the above methods, by first downloading the Excel and then importing to a dataset.

METHOD 4:

    url1 <- 'http://<companyname>.sharepoint.com/sites/<sitename>/Shared%20Documents/General/TRACKERS/<FolderName>/<TrackerName>.xlsx?d=wbae96ce171e14926863e453a8bec146a?Web=0'
    
    df <- read.xlsx(file = url1,sheetName = " sheetname")

OUTPUT 4:

Error in loadWorkbook(file, password = password) : 

  Cannot find <url> …

Answer 1

I encounter the same issue as you did. And I thought there are some problem with url. So I did this instead of directly copy url above the browser :

1. Find your file in Sharepoint site and click "Show actions"(3 dots) buttons of your file.
2. Click "Details", and then find "Path" at the end of details information.
3. Click "Copy" icon.

Last, follow the METHOD 1 you use :

GET(url1,write_disk(tf <- tempfile(fileext = ".xlsx")))
readxl::read_excel(tf, sheet = "Sheet 1")

This works on me. Hope it can be useful to you.