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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Olia
Helper III
Helper III

Google Sheets (highly sensetive data shared within company)

Hello,

 

I've been reading on some topics posted here about how to use Google Sheets as a source, but since they were posted about a year ago I thought that it'd be better to make a new topic. PBI updates quite frequently, or I must be missing some steps because I can't get those solutions to work. So here's the business case:

 

 

Situation

I work for a company that "lends out" professionals (known as IPers) on project-basis (this is due to very high firing costs in EU, it's cheaper to "borrow" someone than to hire and then fire that person). We have multiple locations through the country, and it's not uncommon for one location to borrow a IPer from another location to then lend him/her out to a client.

 

Goal

The company wants to know which IPers are available and on which location, so Sales can go ahead and lend out IPers from not just their own location. Preferably this should be something fancy-looking, because it's Sales we are talking about. There are currently too many IPers "available", so the company wants to make sure that they are lend out to clients asap, with little regard of how long the IPer will need to commute to client.

In the past some more advanced locations were using Trello to achieve something like that, but the Corporate Office decided to disallow that. nobody dared to ask why.

 

Resources

- Google Sheets

- Corporate office that isn't giving us any budget 

- a noob developer who has once (!) created a PBI dashboard based on already provided and loaded in data ( = me), but who is "available" and should be doing something useful

 

Limitations

The information shared about IPers is highly (!!!) sensetive, as it includes employee data + their minimum asking price per hour. 

 

 

this leads to the following questions:

 

1) how to connect Google Sheet to PBI?

2) how to ensure the data from Google Sheet itself is highly secured, while still being able to use that data in PBI?

3) how to enable a very limited number of people (all the sales people) to refresh the data themselves in PBI?

 

halp? 

5 REPLIES 5
v-yuezhe-msft
Employee
Employee

@Olia,

You can connect to Google sheets using R or custom connector in Power BI Desktop. Both methods require authentication, which builds a secure channel between Google sheet and Power BI. For more details, please review the following blog.

https://itsalocke.com/blog/connect-to-google-sheets-in-power-bi-using-r/
https://www.thebiccountant.com/2017/09/24/custom-connector-import-google-sheets-oauth2-powerbi/

In addition, could you please describe more details about why you want users to refresh the data? You can publish the PBIX file to Power BI Service and then use gateway to refresh dataset, and you can  share the report to users, they can get updated data in the report based on refresh schedule you set.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia @v-yuezhe-msft

 

Have you tried creating the connector yourself? Because I'm stuck at OAuth Consent Screen. what is this Authorized domain they are talking about? and I don't see exactly how you can set it up in such a way that only a number of previously approved people can see the data.

 

As for your question, there will be no support for the PBI report created from my side. every Sales person must be able to edit the underlying dataset on google sheets, and update the report for themselves. After all, I don't think that my manager will be happy if I get access to how much my colleagues are earning. 

@Olia

Could you please post a screenshot and error message?  Another easy method is to connect to Google Sheet data following the guide in this thread.

After publishing the PBIX file to Power BI Service and set schedule refresh for the dataset, when users edit the underlying data in google sheet, Power BI report will get updated automatically.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for everything
GamesBX

Olia
Helper III
Helper III

Hello,

 

I've been reading on some topics posted here about how to use Google Sheets as a source, but since they were posted about a year ago I thought that it'd be better to make a new topic. PBI updates quite frequently, or I must be missing some steps because I can't get those solutions to work. So here's the business case:

 

 

Situation

I work for a company that "lends out" professionals (known as IPers) on project-basis (this is due to very high firing costs in EU, it's cheaper to "borrow" someone than to hire and then fire that person). We have multiple locations through the country, and it's not uncommon for one location to borrow a IPer from another location to then lend him/her out to a client.

 

Goal

The company wants to know which IPers are available and on which location, so Sales can go ahead and lend out IPers from not just their own location. Preferably this should be something fancy-looking, because it's Sales we are talking about. There are currently too many IPers "available", so the company wants to make sure that they are lend out to clients asap, with little regard of how long the IPer will need to commute to client.

In the past some more advanced locations were using Trello to achieve something like that, but the Corporate Office decided to disallow that. nobody dared to ask why.

 

Resources

- Google Sheets

- Corporate office that isn't giving us any budget 

- a noob developer who has once (!) created a PBI dashboard based on already provided and loaded in data ( = me), but who is "available" and should be doing something useful

 

Limitations

The information shared about IPers is highly (!!!) sensetive, as it includes employee data + their minimum asking price per hour. 

 

 

this leads to the following questions:

 

1) how to connect Google Sheet to PBI?

2) how to ensure the data from Google Sheet itself is highly secured, while still being able to use that data in PBI?

3) how to enable a very limited number of people (all the sales people) to refresh the data themselves in PBI?

 

halp? 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors