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

Jon 0

Hi!
Is there a way to modify the color of the text but keep the copy that is in the Google Sheet? I mean, what I’d like to do is to keep the copy or text that is on the Google Sheet cell but be able to change the color without losing that copy.

Jeff

@jon-0

Changing the text color via an Expression shouldn’t change the text present in the text field. Basically, you’ll have two text layers in your project. One layer will be the text whose color you’ll want to change. The other will be the color value you want to apply to the first layer. The Expression will use the data from the color value layer to change the text layer.

So, let’s say you had two textual layers, “text” and “color”. You’d apply the Expression to the “text” layer and reference the “color” layer. The contents of the “text” layer shouldn’t change at all.

Hopefully, that makes sense, but you can check out this example project from our Github page to see the Expression in action.

Thanks!

Jon 0

@jeff
I got it now.
Thank you!