automatically refresh pbi - google sheet

[Anonymous]

Hi

I'm new to PowerBI and I'd like some help to have my (now online) report updated automatically. It is built with a google sheet and an excel, I don't need the excel to be refreshed, only the google sheet.

Here is what I did:

- build a report using PBI Desktop. It uses as inputs (i) a local Excel file and (ii) an online Google Sheet (that I download as an Excel, but I think it's useless here)

- I used my Google credentials to connect to the google sheet, and on PBI desktop it works (i can manually refresh)

- I then published my report online, but I can't work out how to schedule an automatic refresh. I have an error: impossible to connect when I click on the web data source; and I have an error in another screen (the automatic refresh for the dataset) saying "at least one data source needs authentication information" (may be different, I'm translating from French).

I'm not sure what's the usual best practice here, and I'd appreciate any help.

Thanks in advance and have a great day

Alexis

[Anonymous]

Hi
It didn't work because you have to refresh both files, you cannot refresh only the Google Sheet, even if the Excel file won't move. I just found a way without the Excel file and the refresh of the GSheet worked out perfectly.
Thanks to both of you for your useful answers.
Best

[v-janeyg-msft]

Hi, @Anonymous 

Has your problem been solved? I saw you said there was a credential problem. You can go to dataset >>setting >>data source credentials ,then add credentials. This is the difference between desktop and service. Then you can set schedule refresh.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ibarrau]

Hi Alexis. I'm not sure I got everything but let's see.

If you have a report connected to local data like excel (on premise gateway) and cloud data like google sheet (directly connected online) you must validate an option in your gateway to make it work. You can check that extra option to check in this blog post that specifies one of the three special configuration on the gateway online.

https://blog.ladataweb.com.ar/post/647097339965734912/powerbigateway-opciones-de-interés-en-puerta-d...

If the problem is regarding the specific conectiong to google sheet. I'm not sure which one is the best way. I have read in the past the Power Bi doesn't have a good Google Auth configuration. So you might be able to connect it this way:

https://blog.ladataweb.com.ar/post/189308808470/powerbi-conectar-desktop-a-un-archivo-en-google

Hope that helps,