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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Murad
Frequent Visitor

Connecting to GoogleSheets Via ODBC/CDATA

Hello, 

 

I'm trying to connect one of my protected googlesheets into powerBi. I thought that this can't be done unless if the sheet is public but then I came upon this. I followed the guidance step by step and the DSN connection actually worked, with each connection attempt google is requesting access to Googledrive as expected. 

 

But the issue seems to be with PowerBi as im getting error [HY000] when trying to connect, screenshot below. I didn't include my OAuth details in the photo but i have copied and pasted everything excatly as it should from the API.  Please help me with this issue. 

 

Best regards,

Murad. 

 

PowerBi connecting to Googlesheets.png

5 REPLIES 5
v-qiuyu-msft
Community Support
Community Support

Hi @Murad,

 

In your scenario, as you want to get data from Google Sheets, I would suggest you follow  v-yuezhe-msft's suggestion to do it in this thread, instead of using third party driver. 

 

Note: Do mask sensitive data before uploading.

 

Best Regards,

Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-qiuyu-msft but the issue with that approach is that i have to publish my data and make it public to the web. I want to keep my personal data where it is without publishing it. 

 

Best regards,

Murad. 

Hi @Murad,

 

Based on my test about CData ODBC driver for Google Sheets, from Power BI desktop, it can get data from ODBC fine. 

 

In your scenario, please make sure the DSN has correct credential and Test Connection to check if it's successful. 

 

q1.PNG

 

When you get data in Power BI desktop, please use Default or Custom type to connect. 

 

q3.PNGq2.PNG

 

Also please try to update Power BI desktop and driver to the same version as ours. 

 

q5.PNG

 

 

Best Regards,
Qiuyun Yu

 

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-qiuyu-msft

 

Its taking longer to fetch back the error so I think we closer than ever on this.

I'm getting a Successful DSN connection message but the same error on PowerBi, CDATA has the same version you do but my PowerBi's version is 2.52.4921.461. I can see that you can access multiple sheets, to double check, did you only enter the OAuth details when establishing CDATA's connection? Would be great if you could tell me how you established your connection, I'm afraid i might have skipped a step during the configuration. 

 

Best regards,

Murad.

Hi @Murad,

 

When I create a DSN in ODBC, I just provide the OAuth Client Id and OAuth Client Secret information, then Test Connection. As long as the connection is successful, you should be able to get data in Power BI desktop follow steps in my previous post.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors