Using data from google sheets into modano

4 posts / 0 new

3 years ago

Iman Lazuardi A+ 5

Hi Modano team,

What is the most efficient way to connect data from google sheets into modano? Lets say I'm building a model (income statement only) with around 100 business units (100 simple income statements using categories layout section). The historical data is in google sheets, and from what I understand it's not possible to link excel directly to google sheets. Any suggestions other than downloading the file into excel format and try to use import assumptions feature?

Regards,

Ardi

Forum

3 years ago

Jun Yan A+ 124

Hi Iman,

Did you manage to get this working? I would suggest one option could be to use PowerQuery to grab the information out of your GoogleSheet.

Here's a useful link to show you how to setup the data flow.

Using Google Sheets as Data Source in Power Query – Excel In PPC

I used the Excel export, but apparently that can sometimes cause issues. The downside of using the CSV is you'll need to do a separate Query per sheet you need.

Once you have this setup, as long as you know how to get the data into a useable format in PowerQuery, you can load a table and then Index the data into your model.

Hope this helps to give you some ideas.

Jun

3 years ago

Jun Yan A+ 124

Hi Iman,

Quick update after I gave it a try.

I've attached some screencaps from the file. In this instance I used a category block to drag in the data, and I'm pulling this "forecast" data directly from the table created from the PowerQuery.

I automated the rows to just bring in the labels and put in checks to make sure there are sufficient categories getting brought in. You may want to separate this process in a proper model as a way to double check data integrity.

There's other things you can do to manage data integrity, but this is a first option.

Screencap 1: File in Google Sheets