I used a Python script in Power Query to pull data from a Google Sheet. It works fine in PBI Desktop, but I get an error for the automated refresh:
You can't schedule refresh for this dataset because the following data sources currently don't support refresh: Discover Data Sources Query contains unknown function name: Python.Execute Unknown function name: Python.Execute is a valid Mashup library function name format. Hence fail the operation.
This is the Python script that works on desktop but not for sched. refresh (anonimized the spreadsheet key):
import numpy as np import pandas as pd pd.options.mode.chained_assignment = None import re import gspread import csv from oauth2client.service_account import ServiceAccountCredentials from df2gspread import df2gspread as d2g import glob from datetime import datetime from datetime import date import calendar import requests from dateutil.parser import parse def connect_to_gsheets(): scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'] credentials = ServiceAccountCredentials.from_json_keyfile_name(r'C:\Users\Laila\Documents\Python Scripts\google_spreadsheet_secret_key.json', scope) gc = gspread.authorize(credentials) return gc,credentials def read_cost_sheet(): gc,credentials = connect_to_gsheets() spreadsheet_key = 'xxxx' spreadsheet = gc.open_by_key(spreadsheet_key) worksheet = spreadsheet.worksheet("DATA") list_of_lists = worksheet.get_all_values() data=pd.DataFrame(list_of_lists) data.columns = data.iloc return data cost_data=read_cost_sheet() print(cost_data)
I can't find what part of the script throws the error.
Solved! Go to Solution.
Adding this just in case the first solution doesn't work for other people:
- Something that works for me is in the PBI file change the privacy level for Python to "Private" and republish.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Mark your calendars and join us for our next Power BI Dev Camp!