Change table data structure

[VictorB]

Hi guy's,

I'm working one a app to monitor buildings consumption.

My table is like the one on the left and I would like to get one like the right one.

Is it possible to generate it automatically? 

The Idea is to generate a Chart Col series stack from it and to filter by building in a dashboard view. 

[Marc_Dillon]

>>"Is it possible to generate it automatically? "

Yes-ish. If you build it so that it happens automatically, then it will be automatic.

What exactly are you wanting? Are you wanting to create a new table that will automatically re-structure the data from the first table? Or are you wanting to actually change the structure of the first table, and combine all old records together?

[VictorB]

I think it will be better to create a new table that use the data from the other one. 

[Marc_Dillon · Accepted solution]

So, there'd be multiple different ways to accomplish what you need.

To start, you'd need your new table to have all the unique rows of date and building combinations that are in the first table. Some possibilities for that would be:

1. Use formulas directly in the spreadsheet to generate the rows.
2. Use Form Save Actions or Bots to generate the unique rows in the new table when records are added to the old table.

Then you need a way to keep the new table updated from the old:

1. Easy way would be to just use virtual columns to pull the values from the appropriate old table records. I imagine this would slow your app down pretty quick.
2. You could again just use formula directly in the spreadsheet to pull the values, INDEX,MATCH,or VLOOKUP are appropriate.
3. Use Form Save Actions or Bots to update the new records when the old records are updated.

You could also just write a Google App Script to handle the entire thing. Really just depends on what you already know and what makes most sense to you and for your app.

Alternatively, can you not just fix the structure or usage of the main table so the data comes in in your desired format from the get-go?

[VictorB]

Thanks for the help, it's moving forward. 

"To start, you'd need your new table to have all the unique rows of date and building combinations that are in the first table." 

Well i'm not currently working with a lot of data, so I don't need to automate this process. 

"Then you need a way to keep the new table updated from the old:"

That's the heart of the problem! 

The first table is full of virtual column so I don't think I can use formula directly from the spreedsheet. 

I'm tying the way of the Action and the Bot.

Action : 

And the Bot trigger with Data Change and Adds only. 

But as you already know, this give me a new and clean table with the same problem as before: 

 "Alternatively, can you not just fix the structure or usage of the main table so the data comes in in your desired format from the get-go?"

I don't think so, each building has many meters and I need to do a lot of calculation for each one to get the consumption.