Compatibility After Effects Please check exact version compatibility under the "COMPATIBILITY" tab below
Add Your tagsClose
Automated Versioning of your CompsThe compsFromSpreadsheet script allows you to quickly generate copies (versions) of your master comps with data from a spreadsheet used to update text or replace layers.
Getting StartedBefore you can use the script you need to setup one or more template compositions to receive data from your spreadsheet. There are a few 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 need a line break in a text layer DO NOT put a line break in your spreadsheet cell as this will cause the script to think it is the end of that line of data. Instead use <br> and the script will replace that with a newline in your text layer
- If you have a text layer named ^date it will receive todays date (great if you're generating slates)
default format is MM/DD/YYYY. You can switch to a different format by clicking on the settings window.
- 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 have a non-text layer in your comp that begins with >, it will be replaced by a file. The script will import a file based on the path in your spreadsheet, then that imported file will replace the layer. If you have the same file referenced more than once in your spreadsheet, the script will only import it once and use that item for every other time the file is referenced
- If you have a precomp layer in your comp that begins with !, any layer in the precomp setup for compsFromSpreadsheet will be updated from the spreadsheet.
So if you click on the button to create text object and type "title" in the window, After Effects will create a text layer named "^title" in the currently selected comp.
Setting up your Spreadsheet
The first line of your spreadsheet must contain the names for each column. If you have more than one composition in your project, you will need a column named ‘COMP’ that will hold the name of the comp to copy for that line. When you run the script it will take each of these column names and allow you to connect that column with target layers in your template comp(s). If you name the columns the same name as the layers they are going to update, these items will automatically be selected. 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 (the one referenced in the ‘COMP’ column of your spreadsheet if you have more than one comp in your project), data from that line of the spreadsheet will update any target layers in the new comp that have been targeted to receive it. It will then put the new comp in the render que with either default settings or settings you have chosen in the render settings window. Finally, the newly created comp is placed in the folder named “compsFromSpreadsheet”.
Special Text CharactersIf you need to have a comma or newline in your text, you will need to enter special characters in place of these.
<c> = ,
<br> = newline
When the script runs it will replace these with their real values. If you have an actual comma or newline in a text field of your spreadsheet, the script will not run properly because these characters will be falsely interpereted as the ond of record or end of field.
Converting Your SpreadsheetBefore 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).
Running the ScriptWhen you click the "run compsFromSpreadsheet" button a window will pop up asking you to select your grid file. This is the tab-delimited text file that you created from your spreadsheet. Do not select your actual spreadsheet as the script will not be able to understand it.
Once you select the tab-deloimeted text file, the script will read the first line of it which needs to contain the names of all the columns. The field init window will pop up to allow you to sync the data in the text file to target layers in your comp.
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, layers with a # will be replaced with the project item named in this column, and layers with > will be replaced by files imported from the location specified in the 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.
Importing Files Via Spreadsheet
You can have layers in your comps be replaced by files that are not in your project.
Any layer tht begins with > will be replaced by a file imported from the path specified in your spreadsheet. To get the proper path format of a file click on the "Get File Path" button located in the main compsFromSpreadsheet window. When you click this button a window will pop up asking you to navigate to your file; when you click ok the proper path to this file will appear in a window that you can copy and then paste into your spreadsheet.
When the script runs it will import the file from the location specified in the spreadsheet and replace the layer. If you have the same file more than once in the spreadsheet it will only be imported once, and that copy will be used each time that particular file is referenced.
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 is 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 "TemplateA" it is named "TemplateA_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.
By default, 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 don't want your new comps put in the render que you can turn this off in the settings.
If you click the "Render Settings" button (on the "field init" window) you can select which render setting and output module to use for the script and where to put the rendered files. If you check the "Remember Settings" box these settings will be used every time tyou run compsFromSpreadsheet; if not checked, these settings will be used in the current run.
SettingsThe settings button on the main UI panel allows you to change the default symbols the script uses to target layers in your Comps.
You can also set the number of columns that appear in the field init window.
And you can also change render settings from here, or uncheck the "add new comps to render que" to bypas this altogether and just crerate comps.
The date format section allows you to change the defualt date format. The default format is MM/DD/YYYY, you can change the order or have month names used instead of numerical (currently this only has english names for the months. If there is enough interest I will add multi language support in a future release).
ConclusionThe script will run through your data line by line. For each line it will create a new copy of the template 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!)
HistoryI initially wrote the script while working on spots for AIG Auto Insurance. They would have 3 base spots with a blank area for the phone number. Then they would send a spreadsheet with about 80 lines, each line would say which of the 3 base spots to use and what phone number to plug in, and what to put in the slate. It would take about a day and a half to create the 80 spots. Once I wrote the script it took seconds to create the spots (minus render time).
Installing CompsFromSpreadsheetThe compsFromSpreadsheet script is meant to be run as a dockable panel (but it can run as a straight script). This means it needs to be saved in “Adobe After Effects CS(your version here)/Support Files/Scripts/ScriptUI Panels/” folder. Save the “compsFromSpreadsheet.jsxbin” file into the “ScriptUI Panels” folder
Restart After Effects.
Click on the After Effects “Window” menu.
Way down at the bottom of the “Window” menu click on “compsFromSpreadsheet”, the compsFromSpreadsheet interface will load onto a new panel.
A window will pop up allowing you to enter a license, or run a fully functional demo.
In After Effects General Preferences, make sure that "Allow Scripts to Write Files and Access Network" is enabled.
If you purchased CompsFromSpreadsheet after September 1st, 2012 you can upgrade to CompsFromSpreadsheet 4 for free. If you purchased it before September 1st you can upgrade for $24.99. To get the upgrade pricing you will need an account with the same email as your original purchase. User accounts are a new feature of our new site so you will need to create a new account if you haven't already but make sure to use the same email address as your previous purchase to get the upgrade discounts. If you already have an account on the new site then simply log in. We no longer offer site licenses so if you previously owned a site license you will be able to upgrade an unlimited number of seats at the discounted upgrade price but you will need to purchase the number of licenses that you require. Contact us if you have any questions.
|After Effects||CS6, CS5.5, CS5, CS4, CS3|
- v4.03 - Fixed "unable to execute script at line 1" error - Jan 2013
- v4.02 - Nov 2012
- Fixed licensing issue and made compatible with foreign language versions of After Effects
- v4.01 - Sep 2012
- Added Precomp support: previously, you could not have precomps in your main comp to be updated. Now if you have a precomp in your comp that begins with a ! in the layer name the script will update any layers in the precomp that have been setup.
- Date format: You can now specify a date format different from month day year
- No more comp select window: You no longer have to select which comps to use. The script now makes every comp in your project available for use as a template
- v3.02 - Oct 2011
- Fixed orphan target layer bug: previously, when you were connecting spreadsheet columns to target layers in your comp, if you had a target layer not connected to a column, the script would not complain, but when it came to the unlinked target layer it would have an error and stop. Now the script will warn you if you have a target not connected to a column. You can choose to go back and connect the target to a column, or ignore in which case the script will turn of any targeted layer not connected to a column.
- Fixed import layer bug: previously, if you had a layer name or path name misspelled in your spreadsheet, the error message would not show you the misspelled name, now it does.
- v3.01 Added error handling when importing corrupted files - July 2011
- v3.0 - Feb 2011
- Now allows you to have the script import files via a spreadsheet column
- You can have the system auto select the column for comps to be used by changing the column name to 'comp'.
- User-definable special characters.
- Inline help file
- Many bug fixes
- 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.
- Fixed a small bug that prevented the script from running on some non-english systems.
- 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.
- 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.
- 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