This script has an embedabble UI, click for an installation video tutorial Watch a quick demo video about this script

Makes multiple versions of your compositions using information from a spreadsheet or database. The script goes through your spreadsheet line by line, making a new version of your comp by replacing footage items and adding text supplied by the spreadsheet automatically.

"Name Your Own Price" for non-commercial use.
$20 for commercial use.
View Cart

CompsFromSpreadsheet This is a new version of this script Compatible with After Effects CS3Compatible with After Effects CS4Compatible with After Effects CS5

Version: 2.2   Sign up to be notified by email when a new version of this script is posted

Automated Versioning of your Comps

The compsFromSpreadsheet script allows you to quickly generate copies of your master comp with data from your spreadsheet used to update text or replace layers.


Getting Started


Before you can use the script you need to setup one or more template compositions to receive data from your spreadsheet. There are three different ways your template comp can be updated from the spreadsheet

  • Text layers that begin with ^(shift 6) will be able to receive data from the spreadsheet as the text for that layer.
  • If you have a text layer named ^date it will receive todays date (great if you’re generating slates)
  • If you have a non-text layer in your comp that begins with #, it will be replaced by the project item named in your spreadsheet

If you are running the script as a dockable panel, there will be two buttons to automatically create either a text or layer object in the currently selected comp.


Setting up your Spreadsheet

The first line of your spreadsheet must contain the names for each column.
When you run the script it will take each of these column names and and allow you to connect that column with elements in your template comp.

After you do this the script will go through the spreadsheet line by line, each line of the spreadsheet will become a new copy of one of your template comps, with data from that line of the spreadsheet updating the layers in the template comp copy that were targeted to receive it.  If a cell in the spreadsheet has <br> when the script is run it will replace the <br> with a carriage return.

If you have set up more than one composition to receive data, you will need to have a column in your spreadsheet that holds the name of the comp to be used as the base. In the above sample spreadsheet the column ‘Core Spot’ will be used, so when the script is run, for the first line of data it will create a copy of the comp named “spot 1″ to be used for that line.

Before After effects can read your spreadsheet, it needs to be converted into a tab-delimited text file (most spreadsheet programs do this easily). This is a universal text format that will allow After effects to read in your data and organize it by rows and columns.

When you run the script be sure to point it to this newly created text file and not your spreadsheet as After Effects cannot read any spreadsheet format (maybe I should call the script compsFromTabDelimitedTextFile).

A word about Commas

If you have commas in your data, they will probably get stripped. Why? I believe it is a bug in Excel. There are two major types of delimited text documents, tab-delimited (which we are using) and comma delimited text. If using a comma-delimited format, commas are used to separate columns, if there happens to be a comma in your data, the program will need to strip them out prior to saving as they will be falsely interpreted as the end of that column.

If saving as tab-delimited, tabs would likewise need to be stripped out from your data. Commas are fine and should not be removed, as they are NOT a separator. Sadly, Excel still strips out commas, even when you are saving as a Tab-Delimited text file.


Running the Script


compsFromSpreadsheet needs to know which comps in your project have been setup for use with the script.

When you run the script a window will pop up with all the comps in the project in the left column . Double click on the comp names that are to be used to move them to the right “selected comps” column. Click continue when you have added all the comps to be used to the “selected comps” column.

If you only have one comp this window is bypassed.

If you put all your comps in a folder called TEMPLATES the script will bypass this window and assume that the comps in that folder are to be used


Synching Column Data to your Comp


When the script reads the first line of your spreadsheet, it needs you to tell it where the data from each column should go in your comp. The Field Init window opens. Each column name from the first line of the spreadsheet appears with a dropdown box underneath.

The dropdown box links the data from that column in the spreadsheet to elements in your template comps. The dropdown box contains the names of all the layers that were set up earlier to receive data (remember, the text layers that started with ^ will receive the data from this column, and a layer with a # will be replaced with the project item named in this column.)

If a column name is the same as an element name in one of your comps, that will be automatically selected in the dropdown i.e. If you have a column in your spreadsheet “phone” and a text layer in one of your template comps named “^phone”, that item will be automatically selected in the dropdown box. If however, your text layer is “^phone number” it will not be selected in the dropdown box.

If you have more than one comp to be used you will need to have a column in your spreadsheet with the name of the comp to be used for that line. Select ‘COMP’ from the dropdown box of the column that holds comp names, if you only have comp to be used ‘COMP’ will not appear in the dropdown as the script will already know which comp to use.


Naming Comps


The next step you need to tell the script how to name the newly created sequence, and where you want the rendered sequences to go. The default naming convention is line number, so the name of the very first Comp is “2″ ( because line 1 holds the column names). and the next comp created it called “3″ and so on until it reaches the last line of the spreadsheet.

Alternately you can have the script keep the name of the original template comp and append a number to the end. so the first time the script creates a comp from “spotA” it is named “spotA_1″, next line that uses that comp will name the new comp “spotA_2″

Lastly, you can choose to get the name from one of your spreadsheet columns. So if you choose “FROM SPREADSHEET | name” from the dropdown, the comp will get its name from the ‘name’ column of the spreadsheet.


Setting Render Location

Every comp created by the script will be placed in the render que using the default settings for your render que. It will default to saving the renders in the same directory as your project. If you click the “Render Output Location” button you can select where you want the script to render your comps to.


Conclusion

The script will run through your data line by line. For each line it will create a new comp, put it in a folder called “compsFromSpreadsheet”, update any target layers in that comp with data from the current line, finally, the comp will be placed in the render que using render que defaults and set to render in the same directory as the project (unless you set a different render location in the field init window.

Hit the render button and all your newly created comps will render (go out for a pint while it renders!)


Dockable Panels

The script can either be run as a dockable panel or as a traditional script.

If run as a dockable panel, there will be buttons in the panel to create target text or layer objects to be used with the script. Clicking “Run compsFromSpreadsheet” will bring up the comp select window

If the script is run the traditional way (by accessing it through file – scripts), you will be taken to the comp select window immediately, bypassing the window shown here.


Licensing

compsFromSpreadsheet may be downloaded and used free of charge for non commercial use. If it is to be used commercially, the license is $20


Version History

  • v2.2 – Aug 2010
    • If a cell in the spreadsheet has <br>
      when the script is run it will replace the <br>

      with a carriage return.

  • v2.12 – June 2010
    • Now arranges the field init into multiple rows if you have more that 10 fields (and that number is changeble through a “layout” button)
  • v2.11 – May 2010
    • Small bug fix to new separate folder feature, now correctly places extension after frame number.
  • v2.1 – May 2010
    • You can now choose to have renders go to separate folders, so if you are rendering a tiff sequence for example, each comp will be rendered to an individual folder.
  • v2.01 – May 2010
    • Added file extension to output file name in render queue.
  • v2.0 – May 2010
    • Added ability to insert footage items.
    • Added dockable UI.
    • Blessed for CS5.
  • v1.22
    • Fixed a small bug that prevented the script from running on some non-english systems.
  • v1.21
    • Fixed a bug in the program that caused the script not to run in certain situations. Previously, the script would try to create a folder called “Renders” in the same directory as your project to put your rendered files. The script no longer does this. Now there is a button in the field init window which allows you to designate a folder for renders. If you do not choose one, renders will go wherever your render que is set to put them.
  • v1.2
    • The comp select window now uses scrolling lists to accommodate projects with a lot of comps. The way it works now is there is a list of all comps in the project on the left side of the window. Any comp you want to use, double click on it and it will go the the list on the right. The list on the right is all the comps to be used by the program.
    • If you create a folder called ‘TEMPLATES’ the comp select window will be bypassed and the program will assume that all the comps in this folder are to be used.
  • v1.1
    • Added the ability to choose the naming convention of new comps. Previously the comp was always named the line number of the database, now you can choose if you want line number, original comp name (with an incrementing number), or sync it up with a column in your spreadsheet.
    • Fixed a bug in the new version check.The function is only supposed to run once every 10 days, but the function checked every time the script was run. This has been corrected.
  • v1.0 Initial Release

Last updated August 26th, 2010
5,515 views
Tags: , , , , , , , , , , ,

About the author

Mike Cardeiro has been creating graphics with after effects since 1997. He has been programming computers since the early 80's and when Adobe introduced scripting into the mix he jumped in. Mike is currently employed at an ad agency in Philadelphia as an animator/designer. View Resume

Pingbacks

41 Comments

  • This is a very handy script. I recommend that you add a comment to After Effects Help to tell people about this script in the context in which they will most likely need it—such as here:
    http://help.adobe.com/en_US/AfterEffects/9.0/WS3878526689cb91655866c1103906c6dea-7cf2a.html

  • Very nice script Mike – Good work !

    It would be nice if you also could set the comp. / file name and not just have numbers.
    In the current script version I have to open all the movies and rename them afterward.

    >kenneth

    • what do you think about having a dropdown box when you select fields that gives you the choice of having the new comp named either [line number, original comp name and a unique number, database field...].

      do you think that would make the field init more confusing?

      Mike Cardeiro

    • Hey Kenneth,

      I have updated the script to allow you to change the way the script renames compositions. Now there is a dropdown box underneath where you sync fields with text layers.
      this dropdown allows you to have the comps named by either line number (the way it used to), original comp name, or you can have the comp named from a field in your spreadsheet

      Mike Cardeiro

  • Ok, so the project I’m currently working on has a TON of comps, so when I go to use the script, the list is so long that I can’t select the comp I want to use AND hit select. Now, I have them organized in folders, so is it possible in a future release to have the comp template selector recognize these folders as well?

    • Sounds annoying! I’ll get out a patch in the next couple of days.

    • Hey Nathaniel,

      I updated the script so now the select composition window should be easier to manage if you have a lot of comps.

      You can also bypass this window altogether if you put all the comps to be used in a folder called ‘TEMPLATES’ (all caps), then the script will use these comps and not ask you.

      Mike Cardeiro

  • I am having a problem with the script. Once I set up the layers to correspond with the drop down menu items, it asks if I want to do the work in a copy of the project, I say no and it saves. Then the script does nothing. It doesn’t create a render queue and it doesn’t make more comps. Am I doing something wrong? I am working on a intel mac in AE CS4. Any help would be appreciated as I am working on time sensitive material.

    • Kyle,

      try saving a copy see if your results are different. If not, forward me a copy of your project and tab-delimited text doc at mcardeiro@yahoo.com and I’ll see whats up

      Mike Cardeiro

    • I have been working with Kyle on solving his problem. He now has a working version of the script. It seems to be a problem with the script creating the “Renders” folder to put your renders in.

      I am working on a real patch, in the meantime, if anybody has the same problem, email me mcardeiro@yahoo.com and I can get you the temporary patch that I gave Kyle.

      Mike Cardeiro

      • there is now a new version of the script that fixes the issue. The way the script previously worked was that it tried to create a folder named “Renders” in the same directory as the script. This worked for most people but failed for some.

        The script no longer creates this folder. Now there is a button on the “field init” window where you can choose the folder to render to. If you dont choose a location, it will put the rendered files wherever the last render went (just as if you added a comp to the render que on your own).

        Hopefully this is the last bug!

        Mike Cardeiro

  • Hey guys I just wanted to let everyone know how great Mike has been to work with. He is dedicated to getting this thing working and I really appreciate his hard work.

  • I just want to add too, I had the same issue as kyle, and Mike got back to me within the hour with a new and perfectly working script. seriously, hours of work in minutes. minutes! this is amazing. thank you so much for your hard work.

  • Request: Have the next version with a column in the SS for a project folder name (source containing assets), and a field for the names of those assets.

    The script inserts assets from the source folder into each new comp, based on asset names in the spreadsheet asset field.

    Excellent work, very inspiring. THANK YOU!

  • Hey guys !
    This script is really usefull, very good job !
    I’m french and I’ve got problems with accent. Is it possible this script work in utf-8 ?

    • In speaking with Jeff, the problem he was encountering had to do with creating the tab-delimited text file on a PC and opening it on a mac for use with the script, somewhere in the process the accents in the text doc got mangled.

  • How do you remove all the extra empty cells when saving to a tab-delimited text file?

    • Never mind, I was able to remove the extra fluff from the text doc!
      Too bad Excel can’t export to tab-delimited and keep comma’s for city,state separation.

      • this seems to be a bug with excel. There are 2 different delimited file types, tab-delimited and comma-delimited. Now if you were exporting something comma-delimited, you obviously would not want commas from your field to make it into a comma-delimited doc because this would cause the program you are importing into to think the comma is the start of a new field rather than just a comma.

        I believe Microsoft in their infinite wisdom is using the same code in both tab-delimited and comma delimited docs and stripping out commas when it is not necessary, and actually wrong to do in a tab-delimited doc.

  • Hi Mike,

    Your script looks wonderfull, I was wondering if it is able to be tweaked do the following:

    I often have to add top and tail graphics to spots. Is there was a way to use your script to replace footage in a comp (and take into account differneces in footage length) rather only replacing text?

    Thank you, and great work on the script!

    • Hey Ed,

      Glad you like the script. You are not the first person who has asked for this. I am planning on adding the ability to add layers, whether a graphic, audio file or other comp, to your comps at specified timecode.

      Not sure when I will be able to code this as my life has suddenly become very busy, but it is in the works. The biggest challenge with implementing this will be to try and keep the script from becoming more confusing…I will probably have to break down and write a manual (and I hate writing).

      Mike Cardeiro

      • Hello

        Great Script. I used to do a similiar task with automotion , motion and ae6.5.

        Id like to ditto the comment to add movies or pics. Would be super helpful

        Thanks again

        Sean

  • Hi Mike,

    I got a little problem. Somehow when I start the script i just can’t select any comps? The list of available comps is empty and when I try to double click on some comps nothing happens?

    besides this little staring problem the script would be very useful !

    Thank you very much!

    • I worked with Max on his problem. He was running the script as a dockable UI script. compsFromSpreadsheets wasn’t designed to run as a dockable UI and thus should be put in the “supporting files/scripts” directory of your after effects install and run through the “file->scripts” menu.

      Mike Cardeiro

      • about 5 minute after I posted this, Max got back to me that he was still having the same problem. The problem ended up being a part of the that checks all the items in your project for compositions. the script is looking for typeName == ‘Composition’ but Max’s After Effect was not an english version and actually reffered to comps as typeName == ‘Komposition’.

        The script has been updated so now it checks if the item is instanceof CompItem (which is a way better way to do it anyway).

        More updates coming shortly.

        Mike Cardeiro

  • Thanks for this script Mike, saved me quite a bit of time (well, maybe not yet, but next time anyway!).

    One problem I’ve had is with other – non ‘^’ – layers pointing to the updated layers source text in expressions. As the script renames the layer from the template into the new generated comp from, say, ‘^Name’ to ‘Name’, any expressions that point to layer ‘^Name’ get broken, as they don’t get replaced. If we could have an option to not rename the generated comps layers, or just not do it, that would stop this.

    My workaround for now is to save the template comp with incorrect expressions (no ‘^’, point to the layer name without it), run the script, and then go into every generated comp and click the ‘Enable Expression’ switch – on every single layer that’s affected. Painful, but I do have to go into each one to swap out an image/video layer anyway. It would be nice not to do this though!

    Thanks anyway Mike. Nice work. I’ve looked at the script myself, but my javascript is barely passable, and if I start playing with it god knows what I’ll make it do! Cheers.

    • Hey Daryl,
      A quicker workaround would be to have your expression reference the layer index, as this will not change.

      so instead of :
      thisComp.layer(“^text”).text.sourceText

      you would do this (if your text was layer 3 of the comp):
      thisComp.layer(3).text.sourceText

      normally I wouldn’t reference by layer number because it breaks if you add a layer before the layer you are referencing…but when using compsFromSpreadsheet you generally wouldn’t be adding to the new comps as this would kind of defeat the whole point of the script.

      Mike Cardeiro

  • Hi Mike,

    I agree with …Ed Watkins

  • Fantastic Script!!!

    We have been looking for this exact thing.
    The only two things that could be helpful for us would be:

    1. Support for nested text layers within a selected comp. (can it already do this?)
    2. The ability to replace an audio file from a column in the spreadsheet.

    Again, Fantastic!!!

    Thanks

    • Greg,

      I originally started out with the script looking for text layers within precomps to target. The problem with doing it this was that if the script made 20 dups of the master comp and text within a precomped layer was targeted, they would each be replacing the same text layer so all 20 comps would have the text value from the last comp created.

      version 2.0 will have the ability to add layers from your project to your comp. Not sure when I’ll have time to finish this but it is a work in progress (glad you like the script.

      Mike Cardeiro

  • Version 2.0 is done. You can now target layers in your comps to be replaced with layers named in your spreadsheet. It can also run as a dockable ui panel.

    Enjoy and please report any flakiness here.

    Mike Cardeiro

  • it’s a great script >>>but there is a problem i faced….
    I’d like to update the text from a different comp and render the master comp…..
    i make a scene and embed all text layers as separate comps so i need to update them from the spreadsheet and render the container comp….. hope i explained what i mean.
    thanks
    Amr Gad

  • Hi Mike,

    First off… great script, you have saved us hours….. We do a lot of work where we need to one template which is replaced by lots of names and data.

    One thing we found is that sometimes we might have upwards of 20 placeholders on one screen. When we run your script the init data screen shows all of these horizontally accross the screen. Unfortunately, it doesnt fit all of them in.

    Is there a way that you can choose your init data and have the screen show vertically (or even a scroll bar for the horizontal window)

    Thanks for all your hard work.

  • Hey Craig,

    I updated the script. Now if you have more than 10 fields in your spreadsheet, the field init window will make a new row for every 10 fields across (you can change this number to any number you like by clicking the “layout” button)

    Mike Cardeiro

  • Hi Michael,

    Great script! It has saved me an enormous amount of time creating massive amounts of comps. Is there a way to allow enter marks in a single spreadsheet cell, and have the script recognize this as 1 comp? I’ve tried inputting eg. a list of items in a single cell, but each time it gets loaded into the script, it generates 1 comp per item in that cell due to the enter mark. If there is an enter mark at the end of the cell’s text, the script sees this as a line error, and asks for a link. Thanks for your time, hard work and excellent script!

    Cheers,
    JeffN

  • Fantastic script!

    I’ve just begun using it and run into a problem. In some of my spreadsheet cells I have text that contains line breaks (i.e. Two paragraphs contained in one cell) and the script breaks when it reaches the row with these cells. I can remove the data in these cells and the script runs fine. I would like to be able to carry over paragraphs of text into After Effects. I understand there are limitations to tab-delimited files, but I know that they can carry over line break OR paragraph break information. Any help appreciated!

    Best,
    C

    • I just finished working with Chad on his problem. The script has been modified in the following way.

      If you want to have a newline in a text field, you should type <br> where you want the newline to appear in your text.

      when you run the script, any instance of <br> will be relaced with a newline.

      Mike Cardeiro

Leave a comment

(to reply to a specific comment use the link by the comment)