Connecting Power BI Desktop to Google docs having 2 factor authentication

[Sakshi]

Hello All 

I have a google spreadsheet containing data I need to fetch in Power BI Desktop reports. This spreadsheet has limited security permissions (i.e. only the users in the organization will be able to access/view the data) and our organization has 2-factor authentication enabled for each user.

Now, I need to generate a Power BI report by fetching the data from the spreadsheet. Could anyone redirect me to steps required to connect to the data source having 2-factor authentication enabled?

Could anyone redirect me to steps required to connect to the data source having 2-factor authentication enabled in Power BI Desktop?

Please reply asap. Thanks in advance.

[v-huizhn-msft]

Hi @Sakshi,

When we can getfrom Google documents, we usually follow the steps below, they work fine after test.

1. Use Power BI desktop.

2. Share Google Sheet and get link from sharing.

3. Paste Google Sheet shared link and it will end in something like "adfe/edit?usp=sharing".For example, my link is: https://docs.google.com/spreadsheets/d/11ywwxiiIf1_VLUjMZIW6da6dqaaAJ5Hf5UHtD_xTZqc/edit?usp=sharing

4. Remove the /edit?usp=sharing off the url

5. then add export?format=xlsx&id= where the edit/? had previously started

6. then copy and paste the long id from the first part of your url

7. the long, final URL you should use for Power BI get from web will be something like:

https://docs.google.com/spreadsheets/d/11ywwxiiIf1_VLUjMZIW6da6dqaaAJ5Hf5UHtD_xTZqc/export?format=xl...

In Power BI desktop, get data->web->type the URL, then you need to type your account and passwork as the screenshot below. We can not design the authentication method by ourself. The power BI tool have designed it using fix feature. If it needs 2-factor authentication, it will reminder you.

Best Regards,
Angelia

[Sakshi]

Hi @v-huizhn-msft

On performing the steps suggested by you, when I paste the URL in Get data, it's asking me to download the excel sheet on my local system. See screenshot

Does this mean, now I need to connect to this workbook on my local system?

But I require my report to directly interact with the spreadsheet available on google drive and any changes are done on that spreadsheet should be reflected in the report.

Is there a way that I do not need to export file first to my system and configure my report to directly interact with data available in the spreadsheet on the drive?

[v-huizhn-msft]

Hi @Sakshi,

I didn't receive the step, when I type the username and account, it shows all the tables, I select any of them, which will directly interact with data. I notice the different is, my documents is spreadsheets, while yours is not, how do you get your URL?

Best Regards,
Angelia

[Sakshi]

Hi @v-huizhn-msft

Mine also is a spreadsheet just it has different limit permissions for sharing. Yours is the one with no sign-in required while mine is the one where 'Anyone on Organization can find and access'.

I tried to do this with no sign in required spreadsheet and the steps you suggested are working fine for them.

As I can't alter the permissions for the spreadsheet, please suggest any workaround for this.

[Franklin1]

Did you get your answer for this one?