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!

[metrica]

Hi @fernandohiltner,

Thanks for sharing this workaround. It was a smart way to get around the Salesforce Reports 2,000-row limit at the time.

For anyone reading this now, I would be careful with the Google Sheets layer. It can work for small or temporary use cases, but it also adds a few risks: extra refresh steps, possible execution time limits, URL length issues, shareable link security, and another connector that can break or apply its own limits.

A cleaner option is to avoid the spreadsheet layer and connect Salesforce to Power BI through a Salesforce-side data source.

Power BI Connector for Salesforce by Metrica Software is available on Salesforce AgentExchange / AppExchange:
https://appexchange.salesforce.com/appxListingDetail?listingId=31526f0e-abd8-4cb5-bd1a-3bd56b5c0577

It lets you prepare Salesforce data inside Salesforce first, select the objects and fields you need, apply filters, preview the output, and then connect Power BI to that prepared source. It also supports creating Power BI data sources from Salesforce reports, including joined reports, which is useful if you do not want to rebuild everything manually from objects.

This avoids the frustrating 2,000-row Salesforce Reports limit and removes the Google Sheets middle layer.

Docs and support:
https://metricasoftware.com/docs/salesforce/
https://metricasoftware.com/docs/salesforce/contact-support/

There is a 30-day free trial, so you can test it on the exact report that hits the row limit.

Happy to show how we would replace this Google Sheets workaround with a direct Salesforce to Power BI setup.

Cheers,
Metrica Team

[Rabi]

Hi,

I am able to connect, However the all the columns I have in salesforce report is not being retrieved across. 

Is there anything I could do ?

Regards,

[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!👍

[Anonymous]

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!