Planning Analytics Integration With Google Sheets

Post Reply
BhunaTuna
Posts: 1
Joined: Mon Jul 19, 2021 12:58 pm
OLAP Product: Planning Analytics
Version: 209
Excel Version: 2065

Planning Analytics Integration With Google Sheets

Post by BhunaTuna » Mon Jul 19, 2021 2:30 pm

Hi there,

Within our business we would like to push some PA data into Google Sheets - I have found this TMV Gate (https://www.youtube.com/watch?v=WwH0Rq0-89M) which seems to do the job but an in-house solution would be ideal.

Has anyone done this - is it difficult etc? Just debating whether to start on this journey or is it easier to get the add-in?

Many Thanks,

User avatar
Elessar
Community Contributor
Posts: 258
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia
Contact:

Re: Planning Analytics Integration With Google Sheets

Post by Elessar » Tue Jul 20, 2021 6:30 am

Hi,

It's can be done very easily using python and pandas:
  1. Use tm1py to load data from TM1 to a dataframe: example
  2. use gspread to load dataframe to google sheet: example
Best regards, Alexander Dvoynev

TM1 and Data Science blog: First article - Primary sales forecasting using PA, SPSS Modeler and python.

declanr
MVP
Posts: 1728
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Planning Analytics Integration With Google Sheets

Post by declanr » Wed Jul 21, 2021 4:45 pm

How often do you need it to update?

The easiest and cheapest (free) solution would be to just export from TM1 to CSV on a schedule (with Chores) and then use the CSV Importer for Google Sheets which can also be scheduled. I should add you will need to get the CSV to somewhere with a URL before the import to Google Sheets, be that google drive or onedrive etc but the transfer from your server folder to most of those online storage solutions can be done with a small script which could be executed from TI also.
Declan Rodger

lotsaram
MVP
Posts: 3503
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Planning Analytics Integration With Google Sheets

Post by lotsaram » Thu Jul 22, 2021 7:23 am

You could do it with TM1Py
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
scrumthing
Posts: 76
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 11.x
Excel Version: MS365

Re: Planning Analytics Integration With Google Sheets

Post by scrumthing » Thu Jul 22, 2021 12:51 pm

lotsaram wrote:
Thu Jul 22, 2021 7:23 am
You could do it with TM1Py
I think you can and you should to it with tm1py. :-)

Most obvious solution from my point of view would be to use tm1py to put the data from tm1 into a pandas dataframe and push that dataframe into a google sheet. https://towardsdatascience.com/using-py ... e69422508f

If you trigger that script from within google sheets, you have something similar to an active form.
https://stackoverflow.com/questions/229 ... readsheets
There is no OLAP database besides TM1!

Post Reply