Planning Analytics Integration With Google Sheets

Post Reply
BhunaTuna
Posts: 3
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 »

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: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Planning Analytics Integration With Google Sheets

Post by Elessar »

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: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
declanr
MVP
Posts: 1815
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 »

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: 3651
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 »

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: 81
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 »

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!
User avatar
Mike Cowie
Site Admin
Posts: 482
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

Re: Planning Analytics Integration With Google Sheets

Post by Mike Cowie »

If you already have TM1 Excel reports that you've created with Perspectives or Planning Analytics for Excel (PAfE) and you'd like to generate/convert to nicely-formatted versions of the same in Google Sheets then have a look over at the "Commercial & Events" subforum for a recent post about ReportWORQ (or just reach out to me via private message).

The TM1Py => DataFrame => Google Sheets options will certainly work, too, but I wanted to mention this additional option if you hadn't already thought about leveraging existing TM1 Excel reports that you might already have.
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
MariusWirtz
Posts: 29
Joined: Sat Apr 08, 2017 8:40 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016

Re: Planning Analytics Integration With Google Sheets

Post by MariusWirtz »

Hi,

sorry for the late reply.
I just wanted to confirm that you can indeed build a lovely integration between Planning Analytics and Google Sheets with Python and TM1py.

I did this recently for a customer. We implemented read and write features and TI process executions.
Happy to share more details.
BhunaTuna
Posts: 3
Joined: Mon Jul 19, 2021 12:58 pm
OLAP Product: Planning Analytics
Version: 209
Excel Version: 2065

Re: Planning Analytics Integration With Google Sheets

Post by BhunaTuna »

Hi Everyone,

Thank you all for the replies, very helpful indeed and we managed to perform the Google Sheets integration!

Next project is to look at integrating with Tagetik our consolidation system. Apparently they have an O Data connection but has anyone got experience of trying to integrate (extract data to and from Tagetik) ???
Post Reply