function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SF_MonkeySF_Monkey 

report api and excel

Hello everyone,
I am very new to API and writing my first API ever. I am trying to extract data from a report and put it in a formatted excel file. It is giving me an error.
Apex class 
public class excelReport {

    public excelReport(){
 		Http http = new http();
        HttpRequest request = new HttpRequest();
        string sessionId = System.UserInfo.getSessionId();

        request.setEndpoint('/services/data/v45.0/analytics/reports/00OG00000046KbV');
        request.setMethod('GET');    
        request.setHeader('Authorization', 'Bearer ' + UserInfo.getSessionId());
		request.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		

		HttpResponse response = http.send(request);

        if(response.getStatusCode() == 200){
            System.debug(response.getBody());
        }
    }
}

Error:
Line: 11, Column: 1
System.CalloutException: no protocol: /services/data/v45.0/analytics/reports/00OG00000046KbV

I understand that the endpoint is coming as blank. I looked at the REST API and Reports & Dashoard guide. I cannot find a way to fix this. Is it because of authorization? 
Any suggestion would be appreciated!
Thanks!
Best Answer chosen by SF_Monkey
Raj VakatiRaj Vakati
Actually, looking at your url it is being assembled wrong.
 
You have ://<http:YOUT INSTANCENAME> ?<REST API >.
 
use 
  
System.URL.getSalesforceBaseURL().toExternalForm();

Use this code
  
public class excelReport {

    public excelReport(){
 		Http http = new http();
        HttpRequest request = new HttpRequest();
        string sessionId = System.UserInfo.getSessionId();

        request.setEndpoint(System.URL.getSalesforceBaseURL().toExternalForm()+'/services/data/v45.0/analytics/reports/00OG00000046KbV');
        request.setMethod('GET');    
        request.setHeader('Authorization', 'Bearer ' + UserInfo.getSessionId());
		request.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		

		HttpResponse response = http.send(request);

        if(response.getStatusCode() == 200){
            System.debug(response.getBody());
        }
    }
}

Make sure you set the remote site settings 

All Answers

Raj VakatiRaj Vakati
Actually, looking at your url it is being assembled wrong.
 
You have ://<http:YOUT INSTANCENAME> ?<REST API >.
 
use 
  
System.URL.getSalesforceBaseURL().toExternalForm();

Use this code
  
public class excelReport {

    public excelReport(){
 		Http http = new http();
        HttpRequest request = new HttpRequest();
        string sessionId = System.UserInfo.getSessionId();

        request.setEndpoint(System.URL.getSalesforceBaseURL().toExternalForm()+'/services/data/v45.0/analytics/reports/00OG00000046KbV');
        request.setMethod('GET');    
        request.setHeader('Authorization', 'Bearer ' + UserInfo.getSessionId());
		request.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		

		HttpResponse response = http.send(request);

        if(response.getStatusCode() == 200){
            System.debug(response.getBody());
        }
    }
}

Make sure you set the remote site settings 
This was selected as the best answer
SF_MonkeySF_Monkey
Thank you Raj! It took away the error and the code runs successfully; however, it does not download it in excel. The content-type is in seHeader. 
I added https://myInstance.saleforce.com in the remote setting. Same url as the endpoint. Is that correct?
 
Raj VakatiRaj Vakati
Can you check debug logs what is coming ..? I dnt think so salesforce will download automcatillcay in rest api 

Read the body and try to save it to the files or attachements 
SF_MonkeySF_Monkey
in debug log, it is running the system.debug statement. It is giving the attributes : describe, instance url, report name, report id, fact map
Raj VakatiRaj Vakati
Means code is working .. but i dnt think so you can able to download the csv in salesforce .. 

Do like this 
  
public class excelReport {

    public excelReport(){
 		Http http = new http();
        HttpRequest request = new HttpRequest();
        string sessionId = System.UserInfo.getSessionId();

        request.setEndpoint(System.URL.getSalesforceBaseURL().toExternalForm()+'/services/data/v45.0/analytics/reports/00OG00000046KbV');
        request.setMethod('GET');    
        request.setHeader('Authorization', 'Bearer ' + UserInfo.getSessionId());
		request.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		

		HttpResponse response = http.send(request);

        if(response.getStatusCode() == 200){
            System.debug(response.getBody());
			
			Attachment att=new Attachment();
att.Body=Blob.valueOf(response.getBody());
att.Name='report.csv';
att.parentId='Some Record Id';
insert att;


        }
    }
}

 
SF_MonkeySF_Monkey
What I am trying to accomplish is..... get an excel template which is an attachment on some cusObject__c. Get that id and then map the cells with the fields from the report. For example: on excel cell G07, insert values from report colum: description. I put the response into a map object.
like 
Map<String, Object> results = (Map<String, Object>)
            JSON.deserializeUntyped(response.getBody());
            System.debug(results);

How would I achieve this?
Thanks alot for helping out!
SF_MonkeySF_Monkey
https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_rest_api_download_excel.htm
In this document, they explain it as simple as changing the accept in the header to be able to download the report which I realised that it is not a complete information
Raj VakatiRaj Vakati
I mean not frmo the apex ( If you are using Workbench, click Headers in the API Explorer to view and modify the current Accept header. (For more on Workbench, see Using Workbench.))

So some web app to downlaod the files 
SF_MonkeySF_Monkey
I am not using at web apps. I want to make a url on the home component where the user can download a report and it goes into a formatted excel file