Google sheets to Power BI: New connector & schedule refresh
Vložit
- čas přidán 4. 08. 2024
- Microsoft just released a brand new connector that is super easy to use, this had been in the top 15 most voted for features on ideas.powerbi.com for some time so it's great that they finally brought in the feature. In this video I show how to connect to any Google Sheets file (business, consumer or free package), you must log in with your credentials then you can work in Power Query and Power BI just like you would with any data from Excel. When you are done, I show how to publish to Power BI online and set up an auto-referesh schedule (no gateway required), Sheets may be the best source for an auto-refreshable dashboard/report, it's so quick, easy & hassle-free compared to other data sources (even OneDrive which is hard to work with a non-business account)
Note this feature is in Beta and I did notice some bugs that it asks to sign in a lot online so after I set up auto-fresh it failed a few times because it claimed I wasn't signed in, so I had to sign in again.
More info on the connector (The function is GoogleSheets.Contents) here: aka.ms/googlesheetsconnector
Table of Contents:
00:00 - Introduction
01:42 - Power Query data clean up
03:43 - Creating a parameter
04:51 - GoogleSheets Function
05:31 - Data modeling
07:18 - Schedule refresh
08:37 - Documentation
08:53 - Limitations - Jak na to + styl
Thank a lot from bottom of my heart ❤❤ i am searching this features from last 1 month but found right way to do this only in your video ❤❤❤❤❤❤❤❤❤❤
I’m glad it helped! Yay
Hey David! Thanks for the video! I'm having troubles with the "published" report web link since is not refreshing.. Looks like the dataset and the report are working correctly but the web link doesn't updates. Any idea?
Hi there was a bug with this when it was first released, but I think it was fixed. If it doesn’t work you should get email notifications, send Microsoft a frown & tell them if it continues
Thank you
Yay
Hey David, can we use incremental refresh in google sheets?
Hi I’m not sure about this sorry
Thank you for the vide. How can we make a "LIVE UPDATE". So when the Googlesheet changes, the Power BI form changes also (without hitting refresh). Live update?
Good question. You can set up auto refresh at certain points in the day but live feed I don’t think is possible with g sheets currently (or most sources)
If you transfer the google sheet to a shared drive will the power bi still connect, if not, how do you connect the model to the new share drive file/location?
Hi! Yes it should still work, you need to go into power query, then the source step, click the Cpg and change it to the new url
hi
what about powerbi report server?
there is no credential option in there and I cant refresh my report
Hi sorry I am not sure about report server
Are you able to edit data from BI and sync to G.sheets?
No it works one way not both
Good question though!
how do I filter for hidden sheets before expanding the tables?
That is power query steps, the sheet names should come up with a column to say if it’s hidden or not
I dont have this option.. connect to google sheets.. is there another way?
Hey! Make sure you are on the latest version of power bi (from December 2021) & it should work ok
what about a csv file in google drive? can you explain?
Hey, thanks for the question. There is no google drive connector at this stage, for that you would need to sync google drive to your local computer then get data from folder but you’d need a gateway to refresh it.
for me it showing refresh, and it is disabled
Sorry to hear, there are various reasons why that might be but if you write in the forums with demo file uploads people can help hopefully
I have a ques In Current Value option which link is Copied ??
can anyone help me please !
Thanks for the comment but I cannot help I’m not sure sorry
@@learnspreadsheets My Ques is - In video 4:23 - (what is that link and from where i found that link which one is pasted that Current Value Option)
hallo sir.How to connect Google Sheets to Power BI using the Importange function.
I’ve not tried that but assume it should work as usual. If it doesn’t work as normal then I’m not sure sorry
i can't connect with new google sheet. anyone else face same problem?
Sorry to hear it should still work the same way now
Thanks so much, however, I still can't connect to ggsheet with below error, does anyone know how to fix it?
DataSource.Error: Unable to parse range:
Details:
errorstatus=INVALID_ARGUMENT
errormessage=Unable to parse range:
errorcode=400
Im glad you like the video. Sorry but I haven’t encountered that error