CompsFromSpreadsheet
Version: 2.2 Sign up to be notified by email when a new version of this script is posted
Automated Versioning of your CompsThe 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
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. 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.
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
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
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
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 LocationEvery 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. |
ConclusionThe 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. |
LicensingcompsFromSpreadsheet 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.
- If a cell in the spreadsheet has <br>
- 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
Follow on Twitter
Join us on Facebook
Stay updated via RSS
Stay updated via email
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
Thanks Mike for updating with support for setting comp name.
Haven’t tested it yet – but a dropdown box sounds fine to me.
>kenneth
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
Thanks a ton Mike! I’ve really enjoyed using the script… it’s made my workflow a ton quicker!
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
Aah, of course! Thanks Mike, that is a smarter way to do it. Cheers!!
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