Is there a script that can transfer text from an excel file into an Adobe design program?

Question

We're making a quiz game. We have a question bank on a Google Drive Spreadsheet. We have a designer who has made a generic design for the question cards, in Photoshop.

Now, can we somehow transfer the questions, with the corresponding answers, onto this design through some script, code, or any other automated process?

I know there's a thing called PhotoshopScript, could that be worth taking a closer look at?

Answer 1

Yes, it can be done. A sample is this video that does something similar: https://www.youtube.com/watch?v=4SJxl4vAbqI

you should be able to do it following this video. The Scripting is actually done in Excel taht accesses Photoshop via api call. Hit Alt+F11 on Excel to open up the programming interface.

Answer 2

I am fairly certain I wouldn't try this with Photoshop scripting, but would go for ImageMagick which is installed on most Linux distros, and is available for Apple OS X and all good OSes (and Windows) for free from here.

Let's say you have a PNG, or JPEG or PSD, version of your card like this in a file called card.png

Then you can add text to it like this, either on your Desktop, or as a PHP script if your quiz is online and you want to generate the question cards dynamically:

convert card.png                   \
  -gravity center                  \
  -fill fuchsia                    \
  -font BradleyHandB               \
  -pointsize 48                    \
  -background none                 \
  label:'Q: Is this a question?\nA: If this is an answer.' \
 -compose srcover -composite out.jpg

If the card from your designer is a Photoshop PSD file, you would use card.psd[0] in place of card.png in the command above, since layer [0] of a PSD file is the entire flattened image.

I would suggest you export your questions from Excel into a CSV (Comma Separated Values) file and then it is pretty simple to read the questions and answers in a little loop to make the cards.

So, say you exported a file called quiz.cv from Excel that looked like this:

Is this a question? If this is an answer.
What was Walt Disney's first name? Walt.
In English, what do the initials of the car manufacturer BMW stand for? Bavarian Motor Works

then on Linux, you would do something like this

#!/bin/bash
i=0
while IFS=$'\n' read question; do
   convert card.png                \
  -gravity center                  \
  -fill fuchsia                    \
  -font BradleyHandB               \
  -pointsize 48                    \
  -background none                 \
  label:'$question' \
 -compose srcover -composite card${i}.jpg
 ((i++))
done < quiz.csv

which would give you card0.jpg, card1.jpg, card2.jpg etc.

Of course other colours, fonts, layouts, sizes are possible, as is a Windows version of the script. It just depends what you want.

Answer 3

I've had a bit of time to myself, so I've quickly written the basic structure of what you might need it loads in data from a (local) csv and turns it into text in photoshop. It's enough to get you started, even if it just one question.

CSV is as follows: 1 What's Gwen Stefani's real name? Bob Gwendoline Stefani Gwen Renée Stefani Gwen Stacey C

photoshop_script is as follows:

var wordData = "pop_quiz.csv";
var dataPath = "C:\\temp";
var myStr = readIt(dataPath, wordData);
var quizArr = myStr.split("\t");
var question = "Question " + quizArr[0] + "\n" + quizArr[1];
var choice = "A: " + quizArr[2] + "\nB: " + quizArr[3] + "\nC: " + quizArr[4] + "\nD: " + quizArr[5];
var answer = "Answer: " + quizArr[6];


// create a document to work with
var docRef = app.documents.add(300, 150, 72, "Quiz Question");
var srcDoc = app.activeDocument;

// adjust text because photoshop dones
// old school new lines
question = replaceNewLine(question);
choice = replaceNewLine(choice);


// write question as photoshop text
createText("Arial-BoldMT", 12.0, 0,0,0, question, 25, 15);
createText("Arial-BoldMT", 10.0, 0,0,0, choice, 25, 50);
createText("Arial-BoldMT", 8.0, 128,128,128, answer, 25, 140);

//alert(question);
//alert(choice);
//alert("Answer: " + answer);

// function REPLACE NEWLINE (str) :replaces "\n" with "\r"
// ----------------------------------------------------------------
function replaceNewLine(str)
{
  return str.replace(/(\n)/gm,"\r"); //replace newline
}

// function READ IT (path, filename) :returns string
// ----------------------------------------------------------------
function readIt(inPath, inFile)
{
    var theFile = new File(inPath + "/" + inFile);

    //read in file 
    var words = "";
    var textFile = new File(theFile);
    textFile.open('r');

    while(!textFile.eof)
    {
      var line = textFile.readln();
      if (line != null && line.length >0)
      //if (line != null) // reads it as is
      {
        words += line + "\n";
      }

    }
    textFile.close();

    // return string
    return words
}



// function CREATE TEXT(typeface, size, R, G, B, text content, text X pos, text Y pos)
// --------------------------------------------------------
function createText(fface, size, colR, colG, colB, content, tX, tY)
{

  // Add a new layer in the new document
  var artLayerRef = srcDoc.artLayers.add()

  // Specify that the layer is a text layer
  artLayerRef.kind = LayerKind.TEXT

  //This section defines the color of the hello world text
  textColor = new SolidColor();
  textColor.rgb.red = colR;
  textColor.rgb.green = colG;
  textColor.rgb.blue = colB;

  //Get a reference to the text item so that we can add the text and format it a bit
  textItemRef = artLayerRef.textItem
  textItemRef.font = fface;
  textItemRef.contents = content;
  textItemRef.color = textColor;
  textItemRef.size = size
  textItemRef.position = new Array(tX, tY) //pixels from the left, pixels from the top
}