I posted separately about getting my Netsuite custom connector to work in Power Query/Power BI and FINALLY got it working, but now I'm running into an issue where every single time I edit a query or refresh, it prompts me to log in again (go to the Netsuite log in form, etc.)
And due to how the API pages, I have almost 30 different queries with unions and joins to get the complete datasets. So basically, every time I "Close & Apply" or "Refresh", I have to login 30+ times for each query.
Below is the code in my .pq file:
[Version = "1.0.0"]
section NetsuiteConnector;
client_id = Text.FromBinary(Extension.Contents("client_id.txt"));
client_secret = Text.FromBinary(Extension.Contents("client_secret.txt"));
redirect_uri = "https://localhost";
token_uri = {my_token_uri};
authorize_uri = {my_authorize_uri};
logout_uri = "https://login.microsoftonline.com/logout.srf";
state1="ykv2XLx1BpT5Q0F3MRPHb94j";
// Login modal window dimensions
windowWidth = 720;
windowHeight = 1024;
[DataSource.Kind="NetsuiteConnector", Publish= "NetsuiteConnector.Publish"]
shared NetsuiteConnector.Contents = (url as text, query as text) =>
let
headers = [#"Prefer" = "transient"],
body = Json.FromValue([q = query]),
source = Json.Document(Web.Contents(url,[Headers = headers, Content = body]))
in
source;
// Data Source Kind description
NetsuiteConnector= [
TestConnection = (dataSourcePath) => { "NetsuiteConnector.Contents", dataSourcePath },
Authentication = [
OAuth = [
StartLogin=StartLogin,
FinishLogin=FinishLogin,
Refresh=Refresh,
Logout=Logout
]
],
Label = Extension.LoadString("DataSourceLabel")
];
// Data Source UI publishing description
NetsuiteConnector.Publish = [
Beta = true,
Category = "Other",
ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
LearnMoreUrl = "https://powerbi.microsoft.com/",
SourceImage = NetsuiteConnector.Icons,
SourceTypeImage = NetsuiteConnector.Icons
];
// Helper functions for OAuth2: StartLogin, FinishLogin, Refresh, Logout
StartLogin = (resourceUrl, state, display) =>
let
authorizeUrl = authorize_uri/*& "?" & Uri.BuildQueryString([
redirect_uri = redirect_uri,
client_id = client_id,
response_type = "code",
state = state1,
scope = "rest_webservices"
])
*/
in
[
LoginUri = authorizeUrl,
CallbackUri = redirect_uri,
WindowHeight = 720,
WindowWidth = 1024,
Context = null
];
FinishLogin = (context, callbackUri, state) =>
let
// parse the full callbackUri, and extract the Query string
parts = Uri.Parts(callbackUri)[Query],
// if the query string contains an "error" field, raise an error
// otherwise call TokenMethod to exchange our code for an access_token
result = if (Record.HasFields(parts, {"error", "error_description"})) then
error Error.Record(parts[error], parts[error_description], parts)
else
TokenMethod("authorization_code", "code", parts[code])
in
result;
Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);
Logout = (token) => logout_uri;
// Exchange code for access token
TokenMethod = (grantType, tokenField, code) =>
let
queryString = [
grant_type = "authorization_code",
redirect_uri = redirect_uri,
client_id = client_id,
client_secret = client_secret
],
queryWithCode = Record.AddField(queryString, tokenField, code),
tokenResponse = Web.Contents(token_uri, [
Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
Headers = [
#"Content-type" = "application/x-www-form-urlencoded",
#"Accept" = "application/json"
],
ManualStatusHandling = {400}
]),
body = Json.Document(tokenResponse),
result = if (Record.HasFields(body, {"error", "error_description"})) then
error Error.Record(body[error], body[error_description], body)
else
body
in
result;
Value.IfNull = (a, b) => if a <> null then a else b;
GetScopeString = (scopes as list, optional scopePrefix as text) as text =>
let
prefix = Value.IfNull(scopePrefix, ""),
addPrefix = List.Transform(scopes, each prefix & _),
asText = Text.Combine(addPrefix, " ")
in
asText;
NetsuiteConnector.Icons = [
Icon16 = { Extension.Contents("NetsuiteConnector16.png"), Extension.Contents("NetsuiteConnector20.png"), Extension.Contents("NetsuiteConnector24.png"), Extension.Contents("NetsuiteConnector32.png") },
Icon32 = { Extension.Contents("NetsuiteConnector32.png"), Extension.Contents("NetsuiteConnector40.png"), Extension.Contents("NetsuiteConnector48.png"), Extension.Contents("NetsuiteConnector64.png") }
];
A sample query looks like this:
let
result = NetsuiteConnector.Contents("https://{my_netsuite_ site}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000","SELECT * FROM transaction WHERE recordtype = 'invoice'")
in
result
The tokens should only expire every hour, which I think is fine if I have to re-authenticate once an hour, but having to re-authenticate 30 times every time I change something in the query won't work. Not to mention I would eventually like to set up scheduled refresh in PBI service, so thinking about how to do that too.
Is there a way to implement in the pq logic of the custom connector a way to check if the current token is valid before initiating the whole OAuth flow again?
