Forum Discussion

accountant's avatar
accountant
New Contributor II
2 years ago

Python Refresh Sheet or Workbook - OneStream Excel Add In

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

  • OS_Pizza's avatar
    OS_Pizza
    Contributor III
    2 years ago

    accountant 

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

     

    1. Open an excel workbook
    2. Press Alt+F11 to open VBA Editor
    3. Insert a New Module from Insert Menu
    4. 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.

     

    • accountant's avatar
      accountant
      New Contributor II
      2 years ago

      This looks great!  

      Now to figure out credentials.....

      • OS_Pizza's avatar
        OS_Pizza
        Contributor III
        2 years ago

        What about credentials ? You just to have put the credentails once and done.

    • AdamB's avatar
      AdamB
      New Contributor II
      7 months ago

      Do you know if this Okta login module will work with version 8?