Use Google Sheet and go on!

https://business.tutsplus.com/

In

these days of working at home, the information piece has become more critical. Any type of information, usually stored in folders on-site servers, which is necessary to share. In this case, the need was to share excel files with budget information, where they should be read by a PowerBI platform.

I didn’t want to use the VPN (Virtual Private Network), in order not to be restricted to the Firewall’s internal access and permissions, but I shouldn’t expose the information either. The information would have to be kept private. One of the ideas for remote connection to the file was to put it in Google’s cloud, Drive.

Two problems arose with this decision:

1-PowerBI have no native connection to Google Sheet.

2- The connection to Excel (xlsx) saved in the Drive has connection errors.

One apart from this discussion, has a connection with so much platform in PowerBI, that it is hard to believe that there is no connection with Google Sheet. Market issues or lack of commercial ability to know that today Google applications are fighting on an equal footing in the market.

Studying the problem I found the workaround for this connection. 3 steps for this solution:

1-First it will be better to transform your excel sheet into a Google Sheet. It’s automatic using Google conversion.

2-Share your Google Sheet by obtaining the shareable link.

3-Define your PowerBI connection link as Web, and use the URL link obtained in step 2.

Here we have a hint, with the relationship the URL. You need to change the spreadsheet URL to the following format: https://docs.google.com/spreadsheets/d/[id=spreadsheet] / export? format = xlsx & id = [spreadsheet id]

An example:

Your shared URL is: https://docs.google.com/spreadsheets/d/5aEqaRdzheBRpTez_Y0GBxBH2vKBIzT-jcmds2VJjbIs/edit?usp=sharing.

Look that the ID is only ‘5aEqaRdzheBRpTez_Y0GBxBH2vKBIzT-jcmds2VJjbIs’

After our modification the URL will be:

https://docs.google.com/spreadsheets/d/58-0c4aNM0i0npydD8VKZfinye-y6DH4WrbvCC-eLvrw/export?format=xlsx&id=58-0c4aNM0i0npydD8VKZfinye-y6DH4WrbvCC-eLvrw

So, put this URL on the Web connection in PowerBI.

Let’s go test:

1-First my original excel file, load on Drive:

2-Transforming excel on Google Sheet

3-Obtain the link to share and put it on the Configuration data source, type Web.

Now we can watch our data on PowerBI:

I tested the modification of some customer records in the Drive Google Sheet. At PowerBI I push the Refresh button and the update was immediate.

Conclusion

The use of multiple platforms and the knowledge of their basic skills can be a strong ally in decision making, in record time.

In the case above, the need for an ingenious solution that solved a basic and urgent problem led to the complementary use of two products from competing companies. This use solved the proposed problem very well.

References:

My Medium