How to Bypass Salesforce 2000 Rows Limitation

[fernandohiltner]

Hey guys,

A few weeks ago I had a problem with the 2000 rows limitation while importing data from Salesforce to Power BI. I did a little research and found out that this was a problem with the Salesforce API, Microsoft couldn't do anything to fix it and a lot of people were struggling with that issue as well. 

However, I finally found an easy and quick way to bypass this problem and since this community helped me a lot when I started working with Power BI, I feel that now is my time to give it back. Now we don't have to manually download the report from Salesforce and replace an Excel file or create a lot of reports to split the data in pieces of 2000 rows.

You will import data from Salesforce reports to a Google Sheets and then import data from this spreadsheet to your Power BI file.

- Open a sheet in Google Sheets.

- Go to Add-Ons > Get Add-Ons.

- Search for 'Salesforce' and install the 'Data Connector for Salesforce'.

- Login to your Salesforce account, allow and authorize the permissions Google Sheets needs to access the data.

- Go to Add-ons > Data Connector for Salesforce > Open.

- You will find this menu:

 

- Now you can import data from all your reports with no limitation clicking on "Reports".

- After that you can click on "Refresh" and schedule auto data refreshes every 4, 8 or 24 hours.

- Open your Power BI, Get Data > From Web

- Get a shareable link of your Google Sheet (with permission to view at least) and paste it.

- Modify your Google Sheet link from:

https://docs.google.com/spreadsheets/d/google-sheet-example/edit?usp=sharing

To:

https://docs.google.com/spreadsheets/d/google-sheet-example/export?format=xlsx&id=google-sheet-example

Now it's done. You can start working with your Salesforce data without the 2000 rows limitation and with auto refreshes.

Hope it helps!

[TaxiDad]

You can upvote the request to increase that 2000-row limitation.  The more votes, the more likely it will be changed.  Here is that link.  https://ideas.salesforce.com/s/idea/a0B8W00000GdYvXUAV/power-bi-communicate-with-microsoft-developme...

[danzrust]

I don't think this is working anymore. The connector will also download only 2000 records into the gsheet.

Then it will tell you to re-auth to get "all data" but it just does not work. You can be revoking in SF and authorizing here in G Sheet over and over this message will just keep popping out and of course, the connector continues to download only 2000 records:

(Not to mention the message is outdated too - you need to revoke the permissions in your personal settings in SF). 

And of course, no way to contact Google about this (maker of the add-on). 

[aanyoti]

Just want to say thank you for this neat workaround, awesome stuff!👍

[LucasRibeiro95]

My Salesforce object has 137 columns, when trying to extract it gives the following error: Error: Limit Exceeded: URL Length of URLFetch.

[querycruncher]

Does this method allow authentication using username/password or another method or it is only via unsecured web connector (Anyone with the link can access the data)?

[Anonymous]

Hi Fernando,

@fernandohiltner 

Thanks for the solution here, is was fantastic.

But unfortunately I still have an issue to solve, when I am using google sheets and as my report is quite long, the Salesforce connector shows un error "Exceeded maximum execution time", and because of that I can't import the report. I have searched about this error and everyone says the limits is 5 min running, but I don't have a solution for that. Could you please help me on it?

Thanks,

MatheusFF

[fernandohiltner]

Hi Matheus!

@Anonymous 

Firstly, thank you for your feedback.

Regarding your problem, I have never faced this issue before. I guess this must be a limitation within Google Sheets for running scripts. I guess I would split the report into smaller parts (to run the request in less than 5 minutes) and then aggregate it all inside Power BI. Is this a viable option for you?

Hope it helps!

[v-frfei-msft · Accepted solution]

Hi @fernandohiltner ,

That's pretty cool! Thanks for sharing that!