For Darty-Ai to process your Illustrator documents, your spreadsheet needs to follow a format Darty-Ai can read. See our Getting Started guide for the rules of the Darty-Ai header. Although that header works well in many cases, client data copied directly from another spreadsheet often does not match the required structure. You therefore need a reliable way to transform that data into a Darty-Ai-ready sheet.
Common situations include:
- You receive a spreadsheet from a client that does not follow Darty-Ai specifications, but you still want to use it.
- You handle repeat jobs with recurring variables such as width, height, or text fields, and want a faster way to enter data without reformatting it each time.
- You want to combine multiple customer or internal data sources into a single Darty-Ai-compatible spreadsheet.
Darty-Ai really shines here 💪. With help from tools like ChatGPT, you can build Google Sheets or Excel workbooks that automatically transform raw customer or production data into the format Darty-Ai expects. The initial setup can take a little time, but it saves a lot of time on repeat jobs.
In this article, we cover the principles behind converting source data into Darty-Ai-ready spreadsheets. We also share working examples for both Google Sheets and Excel. These examples use cell formulas and typically include multiple sheets in the same workbook:
- The first sheet is always the Darty-Ai-formatted sheet.
- Subsequent sheets contain your customer or custom data, and the Darty-Ai sheet pulls from them.
Example 1 - Simple Text Changes and Image Placement
In this example, the workbook contains two sheets. The first is the Darty-Ai sheet, which contains the header and formulas that pull data from the second sheet. The second sheet contains the raw customer data. This is one of the most common and simplest scenarios.
Customer Data
The customer data includes fields such as Product, Tagline, Color, Image, and Prices. The Darty-Ai sheet uses formulas to transform this data into the format required for Illustrator automation.
Sample Spreadsheet - Customer Sheet
Raw data from customer
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| product | tag-line | color | image | price | discount |
| Richy the Rhino | Make your point | Aero | 000001 | $20.90 | $19.99 |
| Leo the Lion | Buy a cute cat with a bite. | "Amaranth Deep Purple" | 000002 | $2,090 | $1,999 |
| Buff | Best Big Beautiful Buffalo | 255 130 50 | 000003 | ¢1000 | ¢100 |
| Rolls Royce | You won't find a better bargain EVER! | #d0eea9 | 000004 | €706360 | €56,36 |
| Ted | A friend for life | 100 40 30 20 | 000005 | ฿50,323,001 | ฿50,323,000 |
| Dart the Darty Cat | Darty-Ai's star cat | lab "New Swatch" 100 30 -70 | 000006 | ₹1000000 | ₹100000 |
| Beautiful Bouquet | From Holland's Hights | Amazon | 000007 | ¢99 | ¢59 |
| Soft Chair | Poodle included for FREE | "Metallic Orange" | 000008 | ¥199.99 | ¥99.99 |
| Holiday Hotel | FIVE STARS with style | ANPA 702-0 AdPro | 000009 | ฿1000 | ฿863 |
| Paradise Hut | Find your soul, with pool included | "Dark Brown" | 000010 | £11 | £9.99 |
Darty-Ai Data
The Darty-Ai sheet uses formulas to pull and transform the customer data into the format required for Illustrator automation. This includes generating image file paths, populating text fields, and making sure all required columns are present.
The goal is to have the Darty-Ai sheet populate automatically from the customer data. That lets you paste in the source data and have the Darty-Ai sheet update without rebuilding the structure by hand.
Sample Spreadsheet - Darty-Ai Sheet
This is the sheet that Darty-Ai processes. It can be generated from the Customer sheet using formulas to transform the data into the required structure.
Hover over the yellow cells to see the formulas used.
| A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|
| image | product | tag-line | product | tag-line | regular_price | discount_price | * | |||
| place | text | text | text | text | |||||
| path: images | fittingMethod: fill | color | replace | replace | find: r-price | replace | find: d-price | replace | fittingMethod: shrinkToFrame |
| image-file | product | ||||||||
| 000001.jpg | Richy the Rhino | Make your point | Aero | $20.90 | $19.99 | ||||
| 000002.jpg | Leo the Lion | Buy a cute cat with a bite. | "Amaranth Deep Purple" | $2,090 | $1,999 | ||||
| 000003.jpg | Buff | Best Big Beautiful Buffalo | 255 130 50 | ¢1000 | ¢100 | ||||
| 000004.jpg | Rolls Royce | You won't find a better bargain EVER! | #d0eea9 | €706,360 | €56,36 | ||||
| 000005.jpg | Ted | A friend for life | 100 40 30 20 | ฿50,323,001 | ฿50,323,000 | ||||
| 000006.jpg | Dart the Darty Cat | Darty-Ai's star cat | lab "New Swatch" 100 30 -70 | ₹1000000 | ₹100000 | ||||
| 000007.jpg | Beautiful Bouquet | From Holland's Hights | Amazon | ¢99 | ¢59 | ||||
| 000008.jpg | Soft Chair | Poodle included for FREE | "Metallic Orange" | ¥199.99 | ¥99.99 | ||||
| 000009.jpg | Holiday Hotel | FIVE STARS with style | ANPA 702-0 AdPro | ฿1000 | ฿863 | ||||
| 000010.jpg | Paradise Hut | Find your soul, with pool included | "Dark Brown" | £11 | £9.99 |
In this example, formulas in the Darty-Ai sheet pull data from the Customer sheet and reshape it for Darty-Ai:
- The image file path is generated by taking the image code from the customer data and appending
.jpg. - Text frames tagged
productandtag-lineusereplace, so nofindmethod is needed. - Template strings
{{r-price}}and{{d-price}}are replaced with the respective fields, so they do need thefindmethod. productandtag-lineare combined into a single field to apply color.- The
fittingMethodshrinksetting is applied to text so it fits within the available space.
In this simple case, the Darty-Ai sheet header is built manually. In more complex scenarios, it can also be generated dynamically with formulas.
The formulas in this sheet consist of three stages:
- Data transformation formulas that pull and format data from the Customer sheet.
- A method for auto-populating enough rows to cover all of the customer data. The method for auto-populating rows is different in Excel and Google Sheets.
- Handling blank entries and ensuring
0values are not mistakenly inserted.
Data transformation
To pull a cell from the Customer sheet into the Darty-Ai sheet, you can use a formula like:
Here, = starts the formula in both Excel and Google Sheets.
Customer is the sheet name.The ! separates the sheet name from the cell reference.
A2 is the cell being pulled.To transform the data, you can extend the formula. For example, to generate an image file path by appending .jpg to the image name, you can use:
You could drag this formula down to apply it to additional rows. However, the goal is to automate that for the full dataset so you do not need to fill formulas manually.
Auto-Populating Rows
The simplest way to auto-populate a range rather than a single cell is to reference the full range in the formula.
When entered in the Darty-Ai sheet, this pulls values from column A of the Customer sheet for rows 2 through 1000. Increase the range as needed so it always covers the number of rows in your Customer sheet.
With Excel, using =Customer!A2:A1000 as is will work.
For Google Sheets, we need to wrap the =Customer!A2:A formula in an ARRAYFORMULA function, and use =ARRAYFORMULA(Customer!A2:A). In Google Sheets, leaving the range open-ended means it will continue to cover new rows as they are added.
This kind of auto-filling is often referred to as spill. If a cell in the destination area is not empty, you will get a #SPILL! error.
Handling Blank Entries
When auto-populating rows, we also need to make sure blank entries are handled correctly so 0 values are not inserted by mistake.
To handle blanks, we can use an IF statement to check whether the source cell in the Customer sheet is empty before applying the transformation.
For example:
The structure of the IF statement is =IF(condition, value_if_true, value_if_false).
In the formula above, we check whether the source cell in the Customer sheet is blank. If it is, we return an empty string "". If it is not blank, we return the source value. This prevents blank cells from becoming 0.
In Excel, the =IF(Customer!A2:A1000="", "", Customer!A2:A1000) will work as is.
In Google Sheets, you need to use the ARRAYFORMULA function and use =ARRAYFORMULA(IF(Customer!A2:A="", "", Customer!A2:A)).
Note that in Google Sheets, you can use a range such as Customer!A2:A without specifying an end row.
The Final Formula
Putting that together, the final formula for the image file path in the Darty-Ai sheet looks like this:
Excel
Adjust the range A2:A1000 as needed to cover your data, and make sure the cells the formula will spill into are empty.
Google Sheets
Check out the final Google Sheet and Excel Sheet for reference.
Locale Considerations
Note that some locales, such as German, use different separators for formulas and decimal points.
In German Excel, the formula would look like this:
In German Google Sheets, the function names remain in English, but separators change. The formula would look like this:
🤖 How to Ask Your Bot for a Formula
When you ask ChatGPT, Claude, or another assistant for a formula, give it these instructions up front:
- Specify the platform — Excel or Google Sheets — as their formulas differ.
- Tell it not to add comments in formulas (
/* like this */). - Tell it to use variable names of at least 4 characters that do not end with a digit.
- Tell it your locale, some locales use different separators for formulas and decimal points.
This example covers a simple transformation workflow. The Darty-Ai sheet header is built manually here, but in more advanced scenarios it can also be generated dynamically with formulas. In future examples, we will cover dynamic header creation, automatic translation workflows, and more.
More Examples - Coming Soon
- Example 2 - Text Changes with Header Defaults Set by Customer Data
- Example 3 - Text Changes with Dynamic Header Creation from Customer Data
- Example 4 - Automatic Translations Using Google Translate
- Example 5 - Automatic Translations Using ChatGPT