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 time xl = win32com.client.DispatchEx("Excel.Application")
wb = xl.workbooks.open(fileName)
xl.Visible = True
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....
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.
What about credentials ? You just to have put the credentails once and done.