TM1 Forum

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,

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

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.

You could do it with TM1Py

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

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.

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.

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) ???