Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
noliverte
Helper III
Helper III

refresh data automatically google sheets

Hello,

 

I use powerbi desktop, and I use web connector to import all my google sheets data.

I published my report on the web (app.powerbi.com).

 

How it's possible to refresh google sheets data automatically on app.powerbi.com ?

 

Thanks for your advises

 

Noliverte

1 ACCEPTED SOLUTION

@noliverte - You need a gateway, but as you correctly surmised, you cannot install it on your personal computer as you shut it off. You need to install it on a PC or server that is up 24/7. If you turn the PC with the gateway off, your report will not refresh until you turn that PC on.

 

The On-Prem gateway is free. Just download, install, and configure. You'll then need to go to the Service under Settings, Manage Gateways and add that web source as a valid source the Gateway can use.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

11 REPLIES 11
v-xicai
Community Support
Community Support

Hi @noliverte ,

 

If your report dataset doesn't contain any on premise data source, that is to say the dataset only has the cloud data source Google sheet,  then it needn't to use the gateway.

 

You can manually refresh the dataset by using Refresh now . Power BI automatically connects to the workbook on OneDrive, or SharePoint Online, about every hour to check for updates. If the workbook has changed, Power BI will refresh the dataset and reports in the Power BI service.

 

If your dataset contains not only the cloud data source but also the on premise data source, then it needs to use the gateway,  and then you can configure schedule refresh. See more : On-premises data gateway , Add a data sourceConfigure scheduled refresh .

 

Note that you also need to select Allow user's cloud data sources to refresh through this gateway cluster option Under Gateway Cluster Settings. See: Merge or append on-premises and cloud data sources.

 

In addition, the data gateway is free, and recommend you install on premise data gateway if a gateway is necessary. See more: Install an on-premises data gateway.

 

You shouldn't install a gateway on a computer, like a laptop, that might be turned off, asleep, or disconnected from the internet. The gateway can't run under any of those circumstances.

 

Because the gateway runs on the computer that you install it on, be sure to install it on a computer that's always turned on. For better performance and reliability, we recommend that the computer is on a wired network rather than a wireless one.

 

Best Regards,

Amy 

 

Community Support Team _ Amy

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

Hell,

 

When you said :

"You can manually refresh the dataset by using Refresh now or you can set up a refresh schedule by using Schedule Refresh. Power BI automatically connects to the workbook on OneDrive, or SharePoint Online, about every hour to check for updates. If the workbook has changed, Power BI will refresh the dataset and reports in the Power BI service."

 

you can set up a refresh schedule by using Schedule Refresh , this is exatly what I want to do, all my data are imported with powerbi web connector, and all targets (urls) come from google sheets with this type of url : https://docs.google.com/spreadsheets/d/e/2.......

 

So how can I set up schedule refresh without gateway ? In powerbi desktop ? In https://app.powerbi.com/ ? both ?

 

Thanks for your help

 

Noliverte

hi, i have created a workbook on google sheets and i want to link it to power bi. This works for me, but as soon as I publish the report to power bi online and share it with someone, the data is not automatically updated. Is it possible to publish the report online and at the same time to update the data from the google workbook? Thanks

Hello,

 

You can test this solution.

https://www.youtube.com/watch?v=fejSQmshwrE

 

Noliverte

Tahreem24
Super User
Super User

Please refer the below link:

https://community.powerbi.com/t5/Power-Query/Auto-refresh-Google-Sheets-data/td-p/150924#:~:text=Re:....

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Thank you for your response but I think I have to install gateway to refresh my data ? What do you think ?

 

Noliverte

@Tahreem24 - have you tried that? It was sometime in 2017 or 2018 that MIcrosoft moved Web based data sources to require a gateway. I think it was driven by security needs so people weren't mashing up internal data with web data and potentially sending confidential data to a website as part of a query. By forcing it to go through a gateway, a Power BI admin has to allow that websource.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans ,

Yeah, I'd tried this. It was working seemlessly fine. But I haven't got a chance to work on since one year or so.

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
edhans
Super User
Super User

You need an on-prem Gateway to set up the automatic refresh for web pages, which is how Google Sheets are imported. You can install the gateway from here.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank you for all your responses.

I use Powerbi on my desktop and I switch off my computer sometimes several days... but I want that my report (on https://app.powerbi.com/) refresh data everyday... My company has this version of Powerbi (Power BI Pro for faculty - Power BI Pro)

 

Question 1 : Can I download on premise gateway with this version of Powerbi without paying extra money ?

 

Question 2 : Are all my data will be refreshed ? even if  my computer is switched off ?

 

A response would be greatly appreciated

 

Noliverte

@noliverte - You need a gateway, but as you correctly surmised, you cannot install it on your personal computer as you shut it off. You need to install it on a PC or server that is up 24/7. If you turn the PC with the gateway off, your report will not refresh until you turn that PC on.

 

The On-Prem gateway is free. Just download, install, and configure. You'll then need to go to the Service under Settings, Manage Gateways and add that web source as a valid source the Gateway can use.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.