Tip:
Highlight text to annotate it
X
compsFromSpreadsheet is an after effects script that will allow you to make template compositions
in after effects, then, using data from a spreadsheet, make duplicates of those template
comps, and insert data from the spreadsheet into them as either text layers or av layers.
the latest version of compsFromSpreadsheet and a sample project can be downloaded from
aescripts.com/compsfromspreadsheet
Every line, or row, of your spreadsheet will represent a copy of one of your templates,
and every column will hold data to be inserted into that copy. One of the columns of the
spreadsheet will need to hold the name of the ae comp to use as the base. compsFromSpreadsheet
will make a dupe of that comp and use the data from the columns in that row to populate
text layers, replace layers with either items in your project, or import items to replace
layers.
Setting up a template comp is simple. To have a text layer receive text from the isci column
of your spreadsheet, hit the create text object while you are in the comp you want to use
as a template. a window will pop up, type isci into the field and click okay. a text
layer is created with the text caret isci, caret is the funky symbol above the six on
your keyboard. You dont have to use the button on the interface, any text layer that starts
with a caret symbol in either the source text or layer name will be targeted to receive
data from the spreadsheet.
You also dont have to name the text layer isci, you could call it anything you want,
but naming it the same name as the spreadsheet column will make it automatically sync up
when you run the script.
if you make a text layer named ^date it will have todays date inserted into it. the default
format is month day year numerically. you can change the format by going into the settings.
if you want to have a non text layer in your comp replaced with an item in your project,
name the layer that you want to target # (thats shift 3) and the name of the column in your
spreadsheet you want to sync to, so if you create a layer #graphic, that layer will be
replaced by the project item named in the graphic column of your spreadsheet.
if you want ae to import a layer and place it in your comp use the greater than sign;
so in your template comp the layer named >audio will be replaced by the file that resides
at the path in the audio column of your spreadsheet. to get the proper path for a file, click on
the get file path button and navigate to the file you want to use. the proper path will
be displayed in a window that you can copy and paste into your spreadsheet.
finally, if you have a layer in your spreadsheet that is actually a precomp that contains target
layers, you need to change the layer name to begin with a ! the name that follows is
irrelevent as this is to tell the script that it needs to copy that precomp, replace it
and process any layer that has been set up to receive data accordingly.
Now that you have some template comps set up you need to set up your spreadsheet. the
very first row of your spreadsheet needs to contain names for all the columns. If you
have more than 1 comp in your project you need to have a column named COMP which will
hold the name of the template comp to be duplicated for each row.
If you are updating a text layer from your spreadsheet and need a new line in your text,
you need to type instead of using a newline, also, if you need a comma you need to type
instead of comma. the script will replace these with their intended values at run time.
the final step before you run the script is you need to convert your spreadsheet into
a tab delimeted text doc. despite its name, compsFromSpreadsheet cant actualy read a spreadsheet
if you try to feed the script your actual spreadsheet it will choke. most spreadsheet
programs can convert to tab delimited text easily. in excel just do save as, text tab
delimited.
now you are ready to go. click the run compsFromSpreadsheet button. a window will ask you to select your
grid file this is the tab delimited text file you just created. navigate to this file and
select it and click ok, remember dont point it to your spreadsheet, you need to select
the tab delimited text file.
the field init window pops up. in the fields section the top header represents the first
line of your spreadsheet. the dropdown below each heading contains all the target layers
you created in your template compositions. If you used the same name in your template
as is named in the text doc it will automatically be selected, otherwise you need to manually
select.
the next section of the field init window lets you specify how you want the newly created
comps to be named. the default is to name it the line number of the spreadsheet. the
first comp created will be named 2 because its data came from the second line of the
spreadsheet, remember line 1 is the column names. the next comp will be named 3 and so
on.
you can also choose to have the newly created comps retain their original name, or you can
set it to receive its name from a column in your spreadsheet.
when you run the script, each newly created comp will be set to render in the render que
using the default settings. If you wish to have a specific render template used for the
run hit the render settings button and select the appropriate template from the dropdown
and hit save settings. if you want to always use these templates click the remember settings
checkbox before clicking save settings.
Now you are ready to do the work. click okay. the script goes throught the tab delimited
text doc line by line. foreach line it duplicates the comp named in the comp column and puts
it in the compsFromSpreadsheet folder. it then inserts any data from columns into the
layers of the comp synced to it, it then renames the newly created comp based on your settings
and puts it in the render q either using the default settings or the render template you
chose.
so the idea of theCompsFromSpreadsheet came from my old job I used to work at a post house
in philadelphia and we did work for aig auto to insurance
every three or four months they would send like three different commercial templates
that would have blank areas to insert a phone number and then they would send us a spreadsheet
with like 80 lines on it each line would represent a version of one of those three template comps,
so a line may say use this template comp as a base, insert this phone number and there
would be different columns of data for the slate.
It used to take me about a day and a half to do, so I decided to automate it with scripting. Of
course we lost AIG as a client before I ever got to use my script