Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

automatically refresh pbi - google sheet

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

Need Help

3 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    5 years ago

    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-de

     

    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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    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