Planning Analytics

 View Only

Using TeamOne to run TM1 processes from Google Sheets

By Vlad Didenko posted Wed March 30, 2022 11:25 PM

In my previous post I was talking about TeamOne Google Sheets add-on, how to configure TM1 connections and =TEAMONE() formula, that allows us to easily get TM1 data.
    TEAMONE formula to get TM1 data in Google Sheets

    This time I would like to show you how you can build a control panel to run TM1 processes directly from Google Sheets using TeamOne.

    Let's open TeamOne Configuration from the Google Sheets Extensions menu, where we can add new TM1 connections and tasks:

    We will add a new tm1process task to execute copy_version_control process in our 24Retail TM1 sample model, which has 2 parameters pFromVersion and pToVersion:

    You can save and use this task now. But the beauty of TeamOne is to be able to use a spreadsheet to work with TM1 server. TeamOne can even generate tasks automatically for you. To demonstrate this, let's open TeamOne Sidebar, go to the Processes tab, search for our process and click it:

    Selecting the Add to TEAMONE sheet menu option, we can get the tm1process task definition automatically generated and added to the TEAMONE configuration sheet. Now if we switch to the tasks tab in TeamOne sidebar, we can see our task and parameters there:

    Let's rename our task, change the parameter values and add a small hint. Once we click the refresh config button, our changes on the sheet will appear in the sidebar. The little Info icon on the right will show our hint when hovering mouse over it, which is convenient to add task

    To make sure everything is working as expected, let's switch to Sheet1 and add a new =TEAMONE() formula to get both Actual and Version 2 data:

    Now let's run our task by clicking it in the TeamOne Sidebar. Great, it completed successfully and we can even see it on the Logs tab:

    Finally let's recalculate Sheet1 to make sure the data is all right:

    And it does look good!
    Using Google Sheets formulas we can dynamically calculate task names, options and parameters. Adding more tm1process tasks we can easily build a full TM1 control panel and give access for advanced business users to run tasks on their own. 

    That's it for today. I will cover other TeamOne tasks in the next post, stay tuned!