Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Failed to refresh – Google Sheet & QBO

Hi,

 

I’ve been having difficulty refreshing a report using data from Google Sheet and QuickBooks Online (QBO). There are to 2 main problems:

 

  1. QBO credentials:

Power BI Server seems not remember the QBO credentials. It keeps telling me that the credentials are invalid and I have to sign in back to QBO every time I refresh. This happens both in Power BI Desktop and Power BI Service.

 

Invalid credentials.png

  1. Google Sheet data format:

Sometimes when I refresh the Google Sheet data in Power BI Desktop, it will change the data format, meaning that some rows and columns are messed up and not in the correct position as they’re in the Google Sheet itself. I can see this clearly when refreshing in Power Query – the data positions are massively altered. After a couple of times hit refreshing in Power BI Desktop, the data may then be in the right position.

However, whenever I refresh the Google Sheet data in Power BI Service, this problem always occurs.

 

Wrong data format.png

                                                                           The wrong data format

 

Right data format.png

                                                                              The right data format

 

Are these common issues? Please advise on how to solve these problems! Thank you in advance!

2 REPLIES 2
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

For your first question, there are several points you can considerate:

 

1.make sure you connect to the following the right way, Select oAuth2 for Authentication Method and select Sign In. When prompted, enter your QuickBooks Online credentials and follow the QuickBooks Online authentication process. If you are already signed in to QuickBooks Online in your browser, you may not be prompted for credentials. Note that you need admin credentials for your QuickBooks Online account. see more: https://docs.microsoft.com/en-us/power-bi/service-connect-to-quickbooks-online.

 

2.If your dataset has the gateway already, then try to upgrade the gateway to the latest version.

 

3.If your dataset contains not only on premise data source but also the cloud data source, then it needs to use the gateway and configure schedule refresh .see more: :On-premises data gateway , Configure scheduled refresh .

Note that you also need to select Allow user's cloud data sources to refresh through this gateway cluster option Under Gateway Cluster Settings. See: Merge or append on-premises and cloud data sources.

 

6.png

 

 

You may resolve the issue via the troubleshooting link :https://docs.microsoft.com/en-us/power-bi/refresh-troubleshooting-refresh-scenarios .

 

For your second question, to connect to Google Spreadsheet, you can go to that specific spreadsheet, use Publish to the Web, change the selection from Web Page to Microsoft Excel. Copy that generated URL, go to Power BI desktop, use Web data source and paste URL, enter credential use Basic Authentication to get data.

 

You can refer to the links:

https://community.powerbi.com/t5/Power-Query/Connecting-to-a-Google-spreadsheet/td-p/166657,

https://bizone.co.th/2016/09/26/connecting-power-bi-to-google-sheets/. (Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.)

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Hi @v-xicai ,

 

Thank you for your prompt response and detailed information! There're still some things I'm wondering about:

 

1. I did follow the same way to sign in QBO. The problem is even after saving QBO credentials that way, sometimes when I refresh the dataset on Power BI Service, it says the credentials are invalid and asked me to sign in again. 

 

2. I normally use gateway for on-premise data only. For the report that I'm mentioning, I only connect data from QBO and Google Sheet, so I don't use any gateway for it. Should I use gateway for cloud/web data sources though? Will the dataset be more stable/consistent that way?

 

3. I'm currently connecting to Google Sheet by copying the link and replacing everything after the Google Sheet guild by "/export?format=xlsx". For example, the link to connect is now https://docs.google.com/spreadsheets/d/16oVCuGSKVhtQfUU0wtjboca/export?format=xlsx

I used this way because I don't want outside people to access to the dataset I have.

I actually tried the Publish to web way with another test dataset and for both ways, I do get the problem of inconsistent data format when refreshing. Do you have an explanation or a solution that might help solve this issue?

 

Thank you!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors