Cant import data from Google Sheets "Object reference not set to an instance or object."

Hi,

I'm trying to connect a Google Sheets file to my Power Bi, but i'm keep getting the error message: "Object reference not set to an instance or object." 

With previous files I succesfully connected my PowerBi to a Google Sheets file by using a link from Google sheets by "publishing it to the web" and exporting the link as an .xlsx file. 

Normally using the "Web" option in the PowerBi "get data" tab should connect me to my Google Sheet file when pasting the link, however the same error message keeps popping up.  

In the data source setting I filled in the same login data I use for my google account, however this doens't help either.  The file is accessable for everyone who has a link to the document. 

The Google Sheets file i'm trying to connect consists of multiple sheets (9 in total) with 3 sheets containing 250+ rows. These number will increase over the coming months as it is a document which I keep updating. It also uses a simple script to calculate some basic stuff. 

Hi rubacc,

I can reproduce your issue.

Current power bi seems support normal google sheet. If your sheets data are from other data source, when you try to get data from it, power bi will return this error.

In my opinion, I'd like to suggest you post this requirement to ideas.

Regards,

Xiaoxin Sheng