CC/Transcription via external CSV in After Effects

Use the CSV template provided here to modify your transcript/CC and place in your AE project folder.

The AE Expression below gets applied to the text layer that contains the CC:

//The Following script goes on the empty text layers source text
let timeMarker = thisComp.layer("MARKER-LAYER").marker;
let n = timeMarker.nearestKey(time).index; // get index of the closest key in time on the Marker Layer
if (time < timeMarker.key(n).time) { n--; } // make sure n == most recent key index only
n;

//Convert Source Text to cell number text within the csv file
text.sourceText = footage("cc-data-01.csv").dataValue([0,0 + n]);

Create a Null Layer that will hold the time markers for the CC.

The excel/csv rows are associated to the time markers on that Null layer. When you modify and save the CSV in excel, the text will update in the AE comp and you can place a new marker to advance the text in the the CSV row.

To vertically center align your text you can place the following script on the the text layer's anchor point:

s=sourceRectAtTime();[0,s.top-45+s.height/2]+value