How would I go about automating this graphic?

[georgeh70994914]

So as seen here I have a few games being displayed in a chronological order. The titles move to match their positions on the timeline, and old timeline objects are pushed down and out of frame. Dotted lines mean one thing, solid lines mean another. When games appear they are always stacked on top.

My issue is that this video is a test - it is entirely non-functional (and also extremely inaccurate) and that is because I have no intention of making manual keyframe animations for this.

I am very new to After Effects and I understand the concept of expressions, but I don't necessarily understand how I would go about turning data I have in a color-coded spread sheet into a json or csv which would be used to automate the process of placing objects and lining them up accordingly - and moving them up and down as needed.

One major complication is that in the data - things have a tendency to "rise from the dead". Meaning that these games can come back to live on the board for at least a while. For example, in July 2000 - Team fortress 2 leaves the board, but sometime later in 2004 it will come back. How do I account for this in such a way that when it returns it's put on top, and moves with the other data in the same way?

Pictured is the data represented in the video. When a specific day is given that means that it was a specific day that month, otherwise it just means the beginning/end of that month

[Mylenium · Accepted solution]

Excel cell colors are not data. You need to fix that first and add actual data, be that just a 1/0 true/false statement. Likewise, column headers and labels need to be formatted correctly. What you have there wouldn't generate a proper Excel chart and therefore won't generate useable exports, either. Any actual date values you have would then also have to be moved to separate cells or treated as comments or you would have to fill in some dummy dates on the other cells to make it work consistently. Where the actual expressions in AE are concerned, it could probably be set up easily. Checking, whether a game is actually active is merely a case of running a loop and iterating through each month and then e.g. scaling/ resizing a shape layer rectangle path accordingly. At this point the actual styling wouldn't matter. Likewise, the auto-sorting would be easy based on the same loop that checks for a game's activity. You just need to have a bunch of bars ready somewhere off-canvas or with zero opacity to hide them. Depending on the scenario this could probably also be endlessly optimized to recycle older bars once games have faded into the mists of time and never reappear. So in summary it's not at all that complex, just a bit of repetitive rigging work and figuring out the specific expressions along with straightening out your input data in teh first place.

Mylenium

[Rick Gerard · Accepted solution]

I'm with Mylenium on this one. Each cell on the spreadsheet must contain text or a number. You could use hex codes and turn those into colors. Each green cell would have this entry: 00FF00

Then the layer in After Effects would have this expression applied to the fill color:

hexToRgb("00FF00")

The same thing goes for every cell in the spreadsheet, which is going to be converted to a CSV file that AE can read. 

I'm pretty good with expressions but it would probably take me the better part of a day to figure out how to set up a fully automated set of expressions that would not only add the appropriate colors to the rectangles in the moving timeline but automate the movement and visibility of layers. It would take a significant amount of time just to fill in the spreadsheet. 

I hope this helps. There are a couple of links in AE's user guide to fairly decent tutorials on using JSON files to automate AE comps, and there are are a couple dozen pretty lousy tutorials on YouTube. I don't have time to dig up links for you but if you find some tutorials on YouTube please vet your trainers and make sure they know what they are talking about.