Hi everyone, I have a pretty complicated issue this morning and looking for advice.
Background
- Typically, we use Microsoft for everything including work emails, etc - with one exception. We prefer Googles Sheets over Microsofts Excel.
- Today, have too much data and experience with Sheets to simply swap over to Excel based solutions, and here lies the problem.
- And the data we work with is pretty confidential, so it needs to be on a Private Google Sheet, not public.
Problem
- I created a Private Google Sheets Connector based on the bi-accountants tutorial found here.
- Everything works OK-ish, well, at least we can import. There are a lot of issues, mostly with updating and only when the sheet has more than 1 tab, but we can make it work ~60-80% of the time.
- As we have progressed with PBI over the past few months, we are now ready to start submitting our reports, and that means automatic updates. The particular goal, is to pull data from our Sheets automatically, whenever they're updated. Even if its only at the end of a work day, thats fine too.
- However, after setting up both types of gateways, we got this error message:
You can't schedule refresh for this dataset because the following data sources currently don't support refresh:
Data source for Test
Discover Data Sources
Query contains unknown funtion name: PQGoogleSpreadsheet.Contents.
Solution
I have... NO IDEA WHAT TO DO! AAAAAH
The things I am thinking of atm;
- Some other another type of direct import connection from Sheets.
- Mirroring a Private Google Sheet somewhere else, that natively works with a PBI gateway.
- Mass panic
- Something else?
I have spent a few days researching alternative Private Sheet connections and found a few that I will try out this week, but based on that error message, I am not hopeful 😕 And so I come to petition to you fine folk, masters of the craft!
Do you guys know of any type of solution to this problem?
