Help: import google sheet by Power query to Excel

[神游中的Eddy]

hi,

I'm attemping to import the google sheet data into Excel by Power Query, I copy the URL of the google sheet and change edit?usp=sharing to export?format=xlsx.  

However, I got below warning, "we couldn't authenticate with the credentials provided". I tried to change the different level and credential, but still fail. 

I'm the owner of the google sheet and the general access is "Only people with access can open the link". I also tried to change it from "Restriced“ to organization. But still get above warning.

 not sure which step causes the error🤔

[mariab94]

Hi,

I have the same issue. I am unable to connect to google sheets through Power Query. I get authentification error. In my case, the file I am trying to extract has been shared with me and I have viewer access only. The share option is "anyone in the group with this link can access". Why can't I connect through excel?

[Eddy_Brother]

hi mariab94,

I think it can't work now in Excel Power Query due to authentication as Kaesebro mentioned, connect via Power BI instead is one solution to fix it.

[Kaesebro · Accepted solution]

I have been having the same issue since last Friday with Excel files that I've been using for over two years. The issue also arose in Power BI for queries using the same anonymous authentication method. I tried refreshing on my desktop and my laptop - restarted the computers, cleared caches, checked for updates, etc. Nothing worked.

From what I can tell, this function is simply no longer available. When I switched the authentication method in Power BI to "organizational account" and used my Google credentials to authenticate, it worked, even though the Google Sheet was still set to public for anyone with the link.

I read that Google has been rolling out mandatory multi-factor authentication across its platform, and it's my suspicion that this is affecting the anonymous access function, even for files public for anyone with the link. I don't know if this is true, but I have tasks to finish, so I'm switching all of my work over to Power BI right now.

[神游中的Eddy]

Thank you very much, Kaesebro, you're right, I decide to use Power BI connector instead of Excel. It is the only solution at this moment.

[v-mdharahman]

Hi @神游中的Eddy,
Thanks for reaching out to the Microsoft fabric community forum.

It looks like you were facing issues related to importing data from Google sheet to Excel using Power Query. If the solutions provided by @PwerQueryKees, @adudani works for you then please accept them as solution.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.

[神游中的Eddy]

hi v-mdharahman,

I still had issue to import google sheet to Excel. I already login my company google account on web, but I still got the below window 

[v-mdharahman]

Hi @神游中的Eddy,

Before using the URL in excel to Get Data make sure you have made the required changes in General Access and given the access to anyone with the link.

You can get this access setting on the right top corner of the google sheet. Click on Share option and change the access settings accordingly.

After the setting is changed to "Anyone with the link" you can use the URL in Get Data -> From other sources -> From Web, without any credential requirement error.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[神游中的Eddy]

Thanks v-mdharahman, yeah, I tried the method you mentioned. but I still can't get the google sheet data.  I decide to use Power BI connector instead as Kaesebro mentioned.

[PwerQueryKees]

What you are doing seems to be fine.

You might want to try to clear your authentication cache and try again:

In the PowerQuery editor

[Eddy_Brother]

hi PwerQueryKees,

Thanks for the respond, clear authentication cache and sign with "Anonymous" only work for my personal google account. However, when I change back to company account, it can't work and excel shows I wasn't signed in my google account. I login my personal google account and company account by website. 

not sure which step I miss

[PwerQueryKees]

And you probably need "Anonymous", that worked for me...., but I was already signed in to Google with the correct account.

[adudani]

hi @神游中的Eddy ,

try this:

1. get data -> more -> google sheets connector

2. sign in with your google account.

let me know if this resolves the issue.

to me, it seems like you are trying to use the web/url connector

[神游中的Eddy]

hi Avinash,

yes, I use get data--from other sources---from web, I couldn't find google sheets connector in Excel, is the snapshot you provided from Power BI?

BR.

Eddy