04-25-2023 09:27 PM - edited 04-25-2023 09:29 PM
With power query in excel, you can connect to a database one time with your credential then they are stored in the document. If the connection is refreshed on another computer, credentials are required to refresh; however if the connection is refreshed for the latest data on your own computer credentials are not required. This is a great set up for overlaying python scripts to refresh the data in automated fashion leveraging task scheduler, see below for example script with just a simple power query connection:
import win32com.client
import time xl = win32com.client.DispatchEx("Excel.Application")
wb = xl.workbooks.open(fileName)
xl.Visible = True
wb.RefreshAll()
wb.Save()
xl.Quit()
Is anyone aware of a similar type set up with the OneStream excel add in. As far as I can see, you have to log in your credential each time you open excel and hit the refresh sheet button each time you want updated data. Most times this works just fine, but there are certain cases where I want to automate the refresh wiht a python script (or other method?) for various reasons.
See if anyone has thoughts here....
04-25-2023 11:53 PM
If the answer helped you, Please give kudos and mark the answer as accepted solution
With an Okta user you could try the following:
' Set Excel Addin object
Set xfAddin = Application.COMAddIns("OneStreamExcelAddIn").Object
'OneStream Web URL
url = ""
'okta user credentials
oktaUsername = "enter okta username"
oktaPassword = "enter okta password"
' app name
app = "enter application name"
' get SSO Token
ssoToken = xfAddin.ProcessSSOAuthenticationAndCreateToken(url, oktaUsername, oktaPassword)
' Get user from SSO token
user = xfAddin.GetXFUserNameFromSSOToken(ssoToken)
' log into application using token
isLoggedIn = xfAddin.LogonAndOpenApplication(url, user, ssoToken, app)
If isLoggedIn Then
Refresh = xfAddin.RefreshQuickViewsForActiveWorksheet()
Else
MsgBox ("Problem with login")
End If
Save the file as macro enabled workbook
Open the workbook to test it, it will Run a Macro Automatically.
04-26-2023 08:17 PM
This looks great!
Now to figure out credentials.....
04-27-2023 03:38 AM
What about credentials ? You just to have put the credentails once and done.
05-30-2024 02:14 PM
Do you know if this Okta login module will work with version 8?