Salesforce: Download Reports via URL in R

Question

I try to download the reports available in Salesforce via the URL, e.g.

http://YOURInstance.my.salesforce.com/012389u13541?export=1&enc=UTF-8&xf=csv

in R.

I already did some investigation to access the report via HTTR-GET, however, up until today without any meaningful outcomes. Unfortunately, R is downloading HTML-code instead of the desired csv file. I also tried to realize the approach suggested here:

https://salesforce.stackexchange.com/questions/47414/download-a-report-using-python

The package "RForcecom" allows the interaction via an API, but I was not able to figure out how to realize above solution in R.

General GET-Request:

GET("http://YOUR_Instance.my.salesforce.com/012389u13541?export=1&enc=UTF-8&xf=csv")

I expect the output to be in csv format, but I receive the report data as html source code.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3...
<html>
<head>
    <meta HTTP-EQUIV="PRAGMA" CONTENT="NO-CACHE">
...

Did anyone of you guys encounter same issues and can provide guidance? Any kind of help is much appreciated. Thanks in advance!

UPDATED and not-working R-Snippet:

library(RForcecom)
library(httr)
username='username'
password='password'
instanceURL <- "https://login.salesforce.com/"
session <- rforcecom.login(username, password, instanceURL)
sid=as.character(session['sessionID'])

url='http://YOURInstance.my.salesforce.com/012389u13541?export=1&enc=UTF-8&xf=csv'
getData=GET(url,add_headers('Content-Type'='application/json','Authorization'=paste0("Bearer ",sid),'X-PrettyPrint'='1'),set_cookies('sid'=sid))

score 1 · Answer 1 · answered May 16 '19 at 07:03

Are you sure you have a valid report id? It doesn't look right (did you just obfuscate it for purposes of this post?). What is in that HTML you're getting, an error message? SF login screen?

What you're doing is effectively "screen scraping". This is not a real API, it can break at any time, you should find/build something that properly uses Salesforce Analytics API. You've been warned.

But if you're after a quick and dirty solution...

You need to pretend you're an authenticated user, that you have a valid session id. Add a cookie to your GET request.

How to get a valid session id?

You'd have to log in to SF first (for example use SOAP API's login call or I listed some REST api ideas here: https://stackoverflow.com/a/56034159/313628 )
or display some user's session ID in a SF formula, visualforce page and user would copy-paste it to your app.

Once you have it - add a Cookie header to your GET with value sid=<session id goes here>

Here's a raw request & response in SoapUI.

Hey there! Thanks for the answer :-) The report id is not a valid one, just entered some random stuff for explanation purposes. Anyway, I already tried to use the approach you suggested (to add headers as well as cookies). You can have a look on the R-snippet in the updated post above. I do not understand why I still get the HTML source code. The same approach realized in Python gets me the correct output. Any other suggestions? Thanks in advance - Best, Andre — PTueller, May 16 '19 at 13:00
Sorry man, never wrote a line of R in my life. Can you send your request to catch-all service like https://requestbin.fullcontact.com/ instead of SF to see if the Cookie gets set OK? For sure you don't need Authorization one... — eyescream, May 16 '19 at 18:00

score 1 · Answer 2 · answered Jul 10 '19 at 06:06

1

I recently struggled with the same issue, there's a magic parameter you need to add to the query : isdtp=p1

so if you try: http://YOURInstance.my.salesforce.com/012389u13541?export=1&enc=UTF-8&xf=csv&isdtp=p1

it should return you the file directly.

answered Jul 10 '19 at 06:06

Etienne

1,058
11
22

This link format works great for me when I simply enter it as the URL however when I try to use python/requests I get the same issue that the original question mentioned around it only downloading HTML in my csv. Are you doing something special when you download these reports? All I'm doing is using the requests.get(url) convention where my URL is the format you have above. – wharfchillin Jun 24 '20 at 05:05

score 0 · Answer 3 · answered May 19 '19 at 22:27

In your example, I don't think that you can use the rforcecom session with httr functions as you are trying.

Here is a slightly different way to solve the problem.

Rather than trying to retrieve a report that you already created in Salesforce, why not specify the report in SOQL and use rforcecom.query function to execute the SOQL from r. That would return the data in a data frame and would require no further data wrangling in r to make it useable.

I use this technique often and once you get used to the Salesforce API I think that its probably faster and more powerful for most use cases.

Here is a simple function that I use to return select opportunity data for all opportunities in Salesforce.

getSFOpps <- function(session) {

    #Construct SOQL Query
    soql <- "SELECT Id,
                Name,
                AccountId,
                Amount,
                CurrencyIsoCode,
                convertCurrency(Amount) usd_amount,
                CloseDate,
                CreatedDate,
                Region__c,
                IsClosed,
                IsWon,
                LastActivityDate,
                LeadSource,
                OwnerId,
                Probability,
                StageName,
                Type,
                IsDeleted
                    FROM Opportunity"

    #Retrieve Opp information
        as_tibble(RForcecom::rforcecom.query(session, soql))
}

It requires that you pass in a valid session from Rforcecom.login but you seem to have that part working from your code above.

I hope this helps ...

Hey! Thanks for the detailed answer - I am note really familiar with the Salesforce API and wonder if it is possible to extract data via SOQL even across different objects (e.g. a custom field A of a custom object B as well as field C of object D)? In this case, your approach would be definitely the better solution. Anyway, I realized my idea above with the help of the reticulate package - I execute python code in the R environment and translate the output to a data frame I can work with. Best, Andre — PTueller, May 21 '19 at 16:45
So, I'm not an expert in SOQL but I know enough to be dangerous. You can write SQL-style joins across different object in SOQL but salesforce also supports a dot notion that I find easier to navigate multiple objects in SOQL. Here is the salesforce API reference for it. Also, you could bring the data from each object down separately and manipulate it in r if you are more comfortable working there. — hank_044, May 21 '19 at 19:01

score 0 · Answer 4 · answered Jul 22 '20 at 11:42

As of v0.2.0, the {salesforcer} R package implements the Salesforce Reports and Dashboards REST API. You can execute and manage reports without needing to write functions from scratch to pull down report data. Below is an example of how to find a report in your Org and then retrieve its data. You can also just use the report Id which appears in the URL bar when viewing the report in Salesforce (highlighted in red in the screenshot below).

# install.packages('salesforcer')
library(dplyr, warn.conflicts = FALSE)
library(salesforcer)

# Authenticate using username, password, and security token ...
sf_auth(username = "test@gmail.com", 
        password = "{PASSWORD_HERE}",
        security_token = "{SECURITY_TOKEN_HERE}")

# ... or using OAuth 2.0 authentication
sf_auth()

# find a report in your org and run it
all_reports <- sf_query("SELECT Id, Name FROM Report")
this_report_id <- all_reports$Id[1]
results <- sf_run_report(this_report_id)
results

Salesforce: Download Reports via URL in R

4 Answers4

Linked