Needed help in using of SharePoint Folder connector with R Script

[Anonymous]

Dear All,

I'm using SharePoint folder connector to get a zip file from. Then I'd like to run an R script to get the needed CSV file from the zip. I know there is a solution to unzip with binary coding but with my zip it didn't work. However when I use R script it doesn't have any problem with the same file. That's why I need to implement the R codeing part.

The following R script worked well in Power Query.

let
    Source = R.Execute("zip_file_path <- ""C:/Users/myuser/folder1/folder2/folder4/zipfilename.zip""#(lf)csv_file_name <- ""csvfilename.csv""#(lf)data <- read.csv(unz(zip_file_path, csv_file_name))"),
    data1 = Source{[Name="data"]}[Value]
in
    data1

What I currently have another Power Query code which contains together the SharePoint file and the R execution but it gives error unfortunately.

let
    // Get yesterday's date in the format "yyyy-MM-dd"
    Yesterday = DateTime.ToText(Date.AddDays(DateTime.LocalNow(), -1), "yyyyMMdd"),

    // Get yesterday's date in the format "yyyy-MM-dd"
    Yesterday_2 = DateTime.ToText(Date.AddDays(DateTime.LocalNow(), -1), "yyyy-MM-dd"),

    // Generate the file name for the exact filtering
    FilterText = "zipfilename_" & Yesterday & ".zip",

    // Connect to SharePoint and filter for the daily file
    Source = SharePoint.Files("https://company.sharepoint.com/sites/sitename", [ApiVersion = 15]),
    FilteredFiles = Table.SelectRows(Source, each Text.Contains([Name], FilterText)),
    FileUrl = "https://company.sharepoint.com/sitename/Shared%20documents/General/folder1/"&"zipfilename" & Yesterday & ".zip",

    // R script to open and load data from a selected CSV file within a zip file
    RScript = "
        library(utils)
        library(httr)

        # File URL in SharePoint
        zip_file_url <- '"&FileUrl&"'

        # Encode the URL
        zip_file_url <- URLencode(zip_file_url)

        # Download the zip file
        temp_zip <- tempfile(fileext = '.zip')
        download.file(zip_file_url, temp_zip, mode = 'wb')

        # Specify the CSV file name
        csv_file_name <- 'csvfilename'"&Yesterday_2&"'.csv'

        # Unzip and read the CSV file
        data <- read.csv(unz(temp_zip, csv_file_name))
    ",

    // Run the R script using the file URL
    RunRScript = R.Execute(RScript),

    // Remove unnecessary columns
    Result = Table.SelectColumns(RunRScript, {"Your", "Result", "Columns"})
in
    Result

DataSource.Error: ADO.NET: R script error.
trying URL 'https://company.sharepoint.com/sitename/Shared%20documents/General/folder1/zipfilename_20231129.zip'
Error in download.file(zip_file_url, temp_zip, mode = "wb") :
cannot open URL 'https://company.sharepoint.com/sitename/Shared%20documents/General/folder1/zipfilename_20231129.zip'
In addition: Warning messages:
1: In download.file(zip_file_url, temp_zip, mode = "wb") :
downloaded length 0 != reported length 0
2: In download.file(zip_file_url, temp_zip, mode = "wb") :
cannot open URL 'https://company.sharepoint.com/sitename/Shared%20documents/General/folder1/zipfilename_20231129.zip': HTTP status was '403 Forbidden'
Execution halted

Details:
DataSourceKind=R
DataSourcePath=R
Message=R script error.
trying URL 'https://company.sharepoint.com/sitename/Shared%20documents/General/folder1/zipfilename_20231129.zip'
Error in download.file(zip_file_url, temp_zip, mode = "wb") :
cannot open URL 'https://company.sharepoint.com/sitename/Shared%20documents/General/folder1/zipfilename_20231129.zip'
In addition: Warning messages:
1: In download.file(zip_file_url, temp_zip, mode = "wb") :
downloaded length 0 != reported length 0
2: In download.file(zip_file_url, temp_zip, mode = "wb") :
cannot open URL 'https://company.sharepoint.com/sitename/Shared%20documents/General/folder1/zipfilename_20231129.zip': HTTP status was '403 Forbidden'
Execution halted

ErrorCode=-2147467259
ExceptionType=Microsoft.PowerBI.Scripting.R.Exceptions.RScriptRuntimeException

The error I have I found already in this forum but without similar use case than mine and I didn't find even solution for it.

Please help me to fix it.

[Anonymous]

Hi, 

Thank you for bringing attention to the possibility of eliminating the filtering component.

I have uploaded the zip file to facilitate testing on a SharePoint site where I have the necessary access. As the sole user on the computer with administrative rights, when I paste the link into my browser, the zip file downloads successfully. This leads me to believe that the link is valid, and my access has been granted.

[lbendlin]

Don't assume that your user is the same as the one used by Power BI or by the R script.

[lbendlin]

You don't need to filter if you already know the complete file path.

Looks like you don't have permission to the specified file.

You will need to make sure that the R temppath is accessible for all users of your PC.