Transform Tips & TroubleshootingCopied!

Working with the AI Assistant to build your Transform scripts is powerful, but you might occasionally run into roadblocks. Use these tips to help the AI understand exactly what you need and troubleshoot common errors when they pop up.

How to Handle Formulas vs. Hardcoded ValuesCopied!

The Challenge: The AI doesn't automatically know if it should do the math itself, write a formula for Excel to calculate later, or read a number that Excel has already calculated.

The Solution: Give the AI exact directions based on your goal:

• To have the AI do the math: Prompt the AI to "Calculate in Python and write the result as a hardcoded value - no formula needed."
• To read numbers already calculated by Excel: If you need the AI to grab the final answer of an existing formula, prompt: "Read formula results using data_only=True and wrap all cell reads in a safe numeric fallback (like safe_float)."
• If your formula results are showing up blank: Sometimes, if a file hasn't been opened and saved by a human recently, the AI will see formula results as "None." To force it to calculate, prompt: "Write the formulas, recalculate via LibreOffice (scripts/recalc.py), then re-open with data_only=True to read the results."

How to Work with Data That Changes PositionCopied!

The Challenge: If you tell the AI to always look at "Column C," the script might break next month if someone adds a new column and shifts the data to "Column D."

The Solution: Ask the AI to build the script dynamically so it finds the data no matter where it moves.

• The Prompt:"Ensure the script dynamically identifies the specified cells/columns/rows each time the script is run as the position may change depending on the dataset."
• If you want the code to be easier for you to read later, you can also tell the AI: "Refer to columns by letter (e.g., 'C') not number," or ask it to use column_index_from_string / get_column_letter.

How to Format Your Data and TablesCopied!

The Challenge: Describing how you want a spreadsheet to "look" (like "make it pretty and blue") can confuse the AI.

The Solution: Give the AI exact commands using the formatting language it understands (called openpyxl). Use these specific phrases in your prompts:

• Colors and Fonts:"Bold, Arial 11pt, white text on dark blue background (RGB 003366)."
• Layout:"Right-align numeric columns, center-align headers," or "Freeze the top row and first column."
• Number Formats:"Number format `#,##0.00` for values, `0.0%` for percentages, `#,##0;( #,##0);-` for currency with zero as dash."
• Pivot-Like Tables:"Aggregate data from [Sheet X] to construct a table similar to a pivot table with the following details..." (List out exactly what you want in the rows, columns, and values).

How to Prevent Scripts from Breaking When Data is MissingCopied!

The Challenge: If you build a script using a file with 100 rows of data, but next month's file has 0 rows (because there was no activity), the script might crash because it can't find what it's looking for.

The Solution: Tell the AI to create an empty "shell" of your table if no data exists. Paste this exact instruction into your prompt:

"If no data is available, generate and return an empty table that preserves the expected structure (including all columns and data types, but no rows). Ensure the logic is dynamic and reusable so that when data becomes available for this period in the future, the same process executes correctly without modification."

How to Choose the Right Background Tools (Pandas vs. Openpyxl)Copied!

The Challenge: The AI uses two different background toolboxes to do its job. One is great for heavy math, and the other is great for keeping spreadsheets looking pretty. If the AI guesses the wrong toolbox, your script might wipe out your formatting or run too slowly.

The Solution: You can explicitly tell the AI which toolbox to use based on what you are trying to accomplish!

• Tell it to use "Openpyxl" when you need to preserve cell formatting, keep formula strings intact, insert/delete rows, or work across multiple sheets.
• Tell it to use "Pandas" when you need to filter massive amounts of data, combine datasets, or do heavy math before writing the final results.
• Example Prompt: "Use pandas for the reshaping, then openpyxl to write back with formatting."

How to Troubleshoot Common ErrorsCopied!

If things go wrong, here is how to get the AI back on track:

• Problem: I don't understand the error message.
  Solution: Don't try to guess. Copy the exact red error text, paste it into the chat, and prompt: "I received this error: [Paste Error]. Explain the issue and fix the script so it does not happen anymore."
• Problem: The script timed out or took too long.
  Solution: Occasionally this happens because the script is not optimized for runtime performance. If this happens during a run / test run, you can add a prompt similar to:
  Examine the script for performance optimizations. Avoid nested or duplicate iterations over the data where possible. Ensure existing output logic is preserved.
• Problem: Error says it tried to add a number and text together.
  Solution: This happens when the AI tries to do math on a blank cell or a word. Prompt the AI to implement a safe_float() or safe_divide() function so it knows how to skip text when doing math.
• Problem: Formulas and formatting disappeared after running the script.
  Solution: If your Excel file is very large, FloQast will automatically "sample" it to save space, which strips out formatting. You may need to have the AI write the formatting back in using Python.
• Problem: My formulas are referencing the wrong rows.
  Solution: Simply prompt the AI: "Drag formulas down using relative referencing to ensure formulas reference their respective row."