Search code examples
ronedrive

Reading Onedrive file to R

This question is VERY similar to this one: Reading OneDrive files to R

But with a slight twist:

I have an R script that opens a connection to a database. This is roughly that code (names changed to protect the innocent):

jdbcDriver <- JDBC(driverClass="oracle.jdbc.driver.OracleDriver", classPath="C:/Users/JohnDoe/OneDrive - Job Corporation/Documents/Data/jar files/ojdbc8.jar")  
jdbcConnection <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=place.company.com)(PORT=1234))(CONNECT_DATA=(SERVICE_NAME=ghjed)))", "abc_thing_prod_ro4", "AKabc$VxqcasdfdsTDYjZsadfcdy-pxEUW")

As you can see, in that first line I link to the location of a ".jar" file that the code needs. Its already in my OneDrive, and because my onedrive is locally mapped to me, this code works great and opens a connection.

The problem is I need to be able to share this R code with other people, and have the code still work when run on their computer. (and really, ultimately, all this R code is actually run inside of a powerBI file). So in other words, I think I'd like to have a line of code that would read in/download the file from OneDrive so it could be run on other computers.

In this solution to that other question, it looks like it would do roughly that. It looks like his answer would download a csv that was on onedrive and had been linked to, but the problem one commenter pointed out was that "if you are using OneDrive for Business and your admin has disabled the use of public file sharing via links" it will not work. Well, thats exactly my case.

That being said the only people who'd be running this code are within my organization and ARE allowed to download from onedrive links I share. When I just tried to run the solutions code as-is I get an error of "http status was '403 forbidden'

Am I just plain out of luck? Is there a way to get R to read from a onedrive link (when run on other computers) if our admin doesnt like public link sharing?

Update to further clarify (hopefully)

So to reiterate, when my colleague and I share files in R by saving them to a Onedrive location we can both access, the R files will have lines of code that point to Onedrive locations (that we should be able to both access), something like this:

    source("C:/Users/123456/OneDrive - Lifespan Corporation/PowerBI/Test_Auto.R")

Problem is, when I look at that file, it looks like this to me: enter image description here

I.e. the filepath is different. So... even though I can access those files in onedrive, if I try to run that line of code in R, it fails and I get this:

enter image description here

So to reiterate my question:

Is there a way in R to either write that Onedrive filepath or do something where no matter what user is trying to run the code, it'll allow access.

I tried to use the 'web' address of the file like this:

source("https://company-my.sharepoint.com/personal/123456_company_org/Documents/PowerBI/Test_Auto.R")

And got a similar error. But if I paste that line into my browser it'll download the file, so I definitely have access.

Solution

  • 2024 Update:

    It appears that the OneDrive folder path is stored as an environment variable (on Windows), so you can get the path like this (@Bogdan Micu posted a new answer on the right track). Not sure if this consistent across all enterprise OneDrive systems, but you can check if it's in the output of Sys.getenv()

    So we can get the OneDrive directory like this:

    Sys.getenv("OneDrive")

    And construct a path like this:

    path <- paste0(Sys.getenv("OneDrive"), "/file.jar")

    Based on our conversion in the comments:

    In your local environment you have a folder like this:

    C:/Users/Mako/OneDrive/file.jar

    And if you share that folder with a collaborator and they sync it in their local environment, they'll have a path like this:

    C:/Users/OtherUser/OneDrive/file.jar

    So when OtherUser tries to access the path C:/Users/Mako/OneDrive/file.jar, they'll get the "No such file or directory" error.

    We can fix this if we either validate the path first based on a known local environment path for each user, or we can dynamically construct the path if it conforms to a general structure.

    Dynamically, we can use a function like sprintf() to construct a path based on the user:

    sprintf('C:/Users/%s/OneDrive/file.jar', Sys.info()[["user"]])
  
[1] "C:/Users/Mako/OneDrive/file.jar"

    Or statically we can specify two paths:

    path1 <- 'C:/Users/Mako/OneDrive/file.jar'
path2 <- 'C:/Users/OtherUser/OneDrive/file.jar'

    And then check for a valid path:

    if (file.exists(path1)) {
  path <- path1
} else if (file.exists(path2)) {
  path <- path2
} else {
  print("Error, no valid path found")
}