Problem with Google data sources connection

[Anonymous]

Hello! I have a big problem with data sources connection in PBI Desktop, especially WEB page from Google Spreadsheets.

Today I come across with this problem with my existing data, I have been working for a year with these sheets and everything was OK:

“The URL may be wrong or you might not have provided the right credentials to the server.”

As usual I Publish to the web my online Google sheet) and then I get new Web data to PBI Desktop and nothing =(..I have an empty window. PBI displays:

"We didn’t find any tables on this web page. No items selected for preview."

http://prntscr.com/vlgucs 

http://prntscr.com/vlguos

Please let me know how to overcome this issue.

[jamesjeff9]

@Anonymous I am also running into an issue with your second approach. I am able to connect; however, it does not pick up any of the tables from the Google Sheet. See screenshot.

I followed what I understood from your post to be -

1) Take the URL direct from the window, NOT the URL you get from publishing to web

2) Isolate your ID

3) Insert your ID into the following URL line - https://docs.google.com/spreadsheets/d/{your ID}/export?format=xlsx&id={your ID}

4) Replace your existing Source in the Query Editor, or connect to a new web data source with that new URL

Did I miss anything? @Anonymous Any luck?

[Anonymous]

HI @Anonymous 

It seems to me that your problem is solved with the first option.

• Open your Google sheets
• You press the share button
• Select the option "anyone with this link"
• you go to your PBI and update data and with that it should work

[Anonymous]

Example or demonstration with images

• First, I post my google sheets document

• I copy link to obtain data in PBI.

• The result is: "No table found on this web page"

I continue in the next comment.

[Anonymous]

Now I go back to my Google document and change the link to: "Anyone with this link"

I already know that I need to tell you ...

The generated link must be modified, in this example it would be:

https://docs.google.com/spreadsheets/d/1UJR8FbyN2uBdcejxrmy8VF1BJnobt3fCN5A9rjFTUJw/edit?usp=sharing

Just change the final "/" being:

https://docs.google.com/spreadsheets/d/1UJR8FbyN2uBdcejxrmy8VF1BJnobt3fCN5A9rjFTUJw/pub?output=xlsx

I go back to update my PBI file and ...

[Anonymous]

Hello people!

Thank you all for your suggestions. It really allowed me somehow to revive my report (without changing privacy because my NDA forbids it)

So, some of my resources come back with the next steps:

• Click the gear icon near your source and change source to CSV Document press OK

• If you have the table below change the source back to Excel Workbook or if (It worked not for all my resources)

OR if you originally downloaded the CSV source, then replace it with Excel, and then again with CSV.

• Then If you have the following table change back source to Excel Workbook and do the next steps:

• Open you online google sheet
• Publish Entire Document to Microsoft Excel
• Add new source with such link (with your document code) “”https://docs.google.com/spreadsheets/d/e/2PACX-1vSG32i7unNJg84UyCA-Nxa34SwP9P4a5zgm8cSaUAij40_L3ffwEiaANCc1F-iDVXFyjiJ_UKs-cnk2/pub?format=xlsx&id=2PACX-1vSG32i7unNJg84UyCA-Nxa34SwP9P4a5zgm8cSaUAij40_L3ffwEiaANCc1F-iDVXFyjiJ_UKs-cnk2””
• Then you can see your sheets with names like Table 1, Table 2…choose appropriate
• Unfortunately, you should perform all you applied steps as in your old source.

There may still be errors after that. So if you have invented another way please let me know

[onazaren]

Looks like I have similar issue.
I have PBI report connected to Google Big Query and can not refresh it on Deskot from Monday Nov 23th. It was working absolutely fine previously, but not data load is not finishing, I see that all the rows are loaded but then refresh is never-ending. Diagnostic define the most time spend as TraceGap.


When I cancel refreshment in 1 hour it provides error message: 

Object reference not set to an instance of an object.

System.NullReferenceException

True

Please, advise, how to fix it.

[v-chuncz-msft]

@Anonymous 

I tried the latest version and it seems to work fine now. You may check it again and create a support ticket for assistance if necessary.

[Anonymous]

@v-chuncz-msft  Thanks! 

[jamesjeff9]

@Anonymous I just tested going back to "Restricted" sharing in one of my workbooks, and the refresh worked. So I'm thinking that this has been resolved; however, would be nice to hear if it is working for you (and others) as well just to further confirm I am not delusional. Fingers crossed!