Any Script or Expression to pull data from a spreadsheet?

[Declan Smith]

Hi,
You can set up a text file with all the data you need to follow the format below:

= [ “”,”” , “”]

where:
is the name of your composition
is the text of the first field
is the text of the second field
is the text of the third field
etc

Each comp would have an associated line. The number of fields you include depends on how many separate pieces of information you want to extract.

As an example, lets assume this is saved in a file called /compdata.txt and you have two comps. The file would look like this

comp1 = [“A line of text”]
comp2 = [“A different line of text”]

Now, setup a comp called comp1, add a text layer then option or alt click the source text stopwatch, and add the following in the expression window:

$.evalFile(“/compdata.txt”);
try {
eval(thisComp.name)[0]
} catch (err) {
“not found”
}

This will set the source text appropriately. If you leave it as numbered compositions, then it’s easier to duplicate as you get the number increment for free and all your expressions copy with the duplicated composition.

If you want to access the other fields, then you would eval the additional fields such as

eval(thisComp.name)[1]

or

eval(thisComp.name)[2]

etc

Note that it doesn’t have to just be text in the fields, you could put position data, rotation angles etc as you need.

Declan Smith
https://www.madpanic.tv
After Effects CS5.5/ FCS3 / Canon 7D / Canon XL2 / Reason / Cubase

“it’s either binary or it’s not”

[Matt Cheah]

Thanks so much Declan! I’ll try this out and let you know how it goes.

[Adam Zygadlo]

I tested this out and it works out very well! Here’s a question, Is there anyway to make the text on two lines ; just incase if the bodycopy too long and runs off the screen?

Like…
comp1 = [“I like spam”]
comp2 = [“Those who mind don’t matter, and those who matter don’t mind.”]

The line break will be the comma in comp 2.

Cheers,
+Z

Adam Jozef Zygadlo
Motion Graphic Designer

07708 124 915
http://www.jarbrain.com
‘Filled with brainy goodness.’

[Declan Smith]

Hi,

Glad it worked.

All you need to do is add r (That is backslash r ) at the point you want a line break. This is an escaped sequence that gets interpreted to mean carriage return. e.g. in your example

comp2 = [“Those who mind don’t matterr and those who matter don’t mind.”]

EDIT: adding screenshot because I can’t get the COW to accept backslash r:

Declan Smith
https://www.madpanic.tv
After Effects CS5.5/ FCS3 / Canon 7D / Canon XL2 / Reason / Cubase

“it’s either binary or it’s not”

[Yasush Azush]

Hi,

In my case this is not working. I get the word “not found” on my view no matter what I do or where I put the .txt file. I have put the .txt file in my “C:/compdata.txt”

After I can resolve this issue, here’s my questions:

Is it possible to put the .txt file in any place, rather than the root?
Is this working on mac as well?

Thanks

[Declan Smith]

You are almost there. Your file looks like it is loading, but the lookup you are performing is failing, hence the ‘not found’ message.

Assuming that your comp is called: comp1

Then your expression should read as below, and the text file (compdata.txt) should look something like this:

comp1=[“Here is my Text”]
comp2=[“And some more text”]

Note that my setup is a MAC so I can’t test the loading of the windows file.

$.evalFile("c:/compdata.txt");
try {
eval(thisComp.name)[0];
} catch (err) {
"not found";
}

Declan Smith
https://www.madpanic.tv
After Effects CS5.5/ FCS3 / Canon 7D / Canon XL2 / Reason / Cubase

“it’s either binary or it’s not”

[Andrew Hoeveler]

I think this link will solve all your problems!

https://aescripts.com/compsfromspreadsheet/

[Arie Stavchansky]

And just wanted to update this thread with this link for others who are searching the interwebs for a similar solution. Templater for After Effects also supports pulling data in from collaborative spreadsheets like Google Sheets.

https://aescripts.com/templater

Arie Stavchansky | Demo Reel | Portfolio of Creative Work | Blog

[Enginera]

I’m using that Templater plugin for a while. But I’m not able to automate cell-to-compname renaming.

Can you help me about renaming composition name from a text layer within that composition? That would also solve my problem at this point.

[Arie Stavchansky]

Hi Engin,

Glad to hear you are using Templater! Unfortunately, it currently does not “officially” support the renaming of comps. You can use the “Make Comps” feature to create new, unique, comps based on the target (master) comp you select. “Make Comps” will create a new composition based on the target comp and will give that new composition a unique name based on the `id` and the name of the target composition.

In your spreadsheet, set the `id` column to have a unique name that you want in your comp. Then either choose your default target comp from Templater’s main panel, or specify which comp you want Templater to target via your spreadsheet under the `target` column. You can choose whatever term you want for a row’s `id` column, but in your case, you probably want to match it to the value of the text layer column. Then hit “Make Comps” and you should have uniquely name compositions with unique data. They will appear under the [TEMPLATER COMPS] folder in your project panel.

Hope this helps!

Arie Stavchansky | Demo Reel | Portfolio of Creative Work | Blog