Changing text color dynamically using Google spreadsheet

Jorge Santana

I didn’t find an updated content explaining how to change the color of texts dynamically using Google Spreadsheet. I’ve already followed the tutorial on Youtube, but it looks very old.

Jeff

@Jorge-Santana

Using Templater to change a layer’s text color is a bit more involved than Templater’s more basic functions. Basically, you’ll need to set up a text field with the hex code for the color you’d like to use and then apply an expression to the layer that uses that code as a variable.

For example, you could start by adding a column to your Google Sheet called “textcolor”. Populate that column with the hex codes of the colors you’d like your text to be. From there, add a text object to your After Effects project, apply the Templater Effect, and rename the layer to “textcolor”.

Once you’ve verified that that data is coming through, add the “Fill” effect to the text layer whose color you’d like to change. Then, apply this expression:

text.sourceText

function hexToColor(theHex) {
    var r = theHex >> 16;
    var g = (theHex & 0x00ff00) >> 8;
    var b = theHex & 0xff;
    return [r/255,g/255,b/255,1]
}
   
hexToColor("0x" + comp("CompName").layer("textcolor").text.sourceText);

To the “Color” attribute of the Fill Expression. Make sure that “CompName” refers to the comp where your “textcolor” variable is located. If you’re planning on using a large number of variables, it might not be a bad idea to create a separate Comp just to store variables you might use in expressions.

Hopefully, that’ll be enough to get you started, but if you have any other questions, please just let us know. Thanks!

Jeff