Newcomer

Python Refresh Sheet or Workbook - OneStream Excel Add In

Forum|Forum|3 years ago
April 26, 2023
1 reply
0 views

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....

O

OS_Pizza

Veteran

accountant

If the answer helped you, Please give kudos and mark the answer as accepted solution

Open an excel workbook
Press Alt+F11 to open VBA Editor
Insert a New Module from Insert Menu
Copy the below code and Paste in the code window.

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.