Export CSV file to OneDrive Using R Scripting in Power BI Service

Question

I am trying to build a fully automated and sustainable reporting tool in Power BI. I have built a report in Power BI that among other things uses R scripting at one point to create a data export to my local C: drive which is the following code:

# 'dataset' holds the input data for this script
.libPaths(.libPaths()[3])
require(gdata)
write.table(trim(dataset), file="C:\\Users\\Username\\OneDrive\\Folder\\Inventory Log.csv", sep=",", row.names=FALSE, append=TRUE, col.names=FALSE)
plot(dataset);

While all my other data is connected to PBI via OneDrive or online sources, this is still connected to my local machine. I have personal gateway setup but that still requires my local machine to be physically on during the scheduled refresh on the PBI service.

I have tried used the Microsoft365R Package but my R knowledge and experience is still limited so I wasn't able to come up with a solution that would allow file="OneDrive Path" within the write.table() function to successfully execute on Power BI Desktop, let alone Power BI Service.

The goal is to fully automate and not require me to have my computer on during the weekends or a non work day.

Is it possible to write a csv to a OneDrive file? If so, what are some ways that have worked successfully?

Any ideas? Thank you for any help you can provide!

Answer 1

Microsoft365R author here. Disclaimer: I'm not familiar with PowerBI, but I assume you can run R scripts inside it and connect to the Internet etc.

There's a few things needed to get this to run unattended.

A function to upload a data frame as CSV to Onedrive, without requiring you to login, is as follows:

upload <- function(dataset, upload_path, drive_id, ...)
{
  outfile <- tempfile()
  on.exit(unlink(outfile))
  write.table(dataset, outfile, ...)

  library(Microsoft365R)
  library(AzureGraph)

  gr <- create_graph_login(
    tenant="{yourtenant}",
    app="{client_id}",
    password="{client_secret}",
    auth_type="client_credentials"
  )
  gr$get_drive(drive_id)$upload_file(outfile, upload_path)
}

On the AAD side, create an app registration and give it a client secret (password). You then give it the Microsoft Graph application permissions necessary to read drives--most likely "Files.Readwrite.All".

Note down the client ID and client secret for your app registration. These are the values you plug into the R function above.

You can get your drive ID with the following R code.

drv <- get_business_onedrive(tenant="yourtenant")
drv$properties$id

You'll probably need the help of your friendly local admin to get all this done, if only because most orgs lock down the ability to register apps.