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,
Planning Analytics Integration With Google Sheets
- Elessar
- Community Contributor
- Posts: 345
- 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
Hi,
It's can be done very easily using python and pandas:
It's can be done very easily using python and pandas:
Best regards, Alexander Dvoynev
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
-
- 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
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.
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
-
- MVP
- Posts: 3660
- 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
You could do it with TM1Py
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
- 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
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!
- 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
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.
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!
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!
-
- Posts: 31
- 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
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.
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.
-
- 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
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) ???
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) ???