Joyagoo Spreadsheet Automation Guide

Formula Automation: The Foundation

ARRAYFORMULA lets you apply a formula to an entire column without dragging. Wrap your margin formula in ARRAYFORMULA and it auto-fills every row that has data. No more dragging down five hundred rows when your haul grows.

IFERROR protects fragile formulas from breaking your sheet. Wrap any formula that might divide by zero or reference a missing cell: =IFERROR(your_formula, "Check data"). Instead of ugly red errors, you get a clean message.

VLOOKUP and XLOOKUP connect sheets. Maintain a Rate table on a hidden Config tab. In your price columns, use =VLOOKUP("USD_CNY", Config!A:B, 2, FALSE). Update the rate once, every price updates.

Conditional Formatting as Visual Automation

Color logic replaces manual scanning. Set rules that trigger on values, not just exact matches. For example, format the Net Margin column to turn red when the value is less than 0.15, yellow between 0.15 and 0.25, and green above 0.30. Your eyes spot problems instantly.

Apply a rule to the Status column that turns the entire row pale gray when Status equals Sold. This fades completed items without hiding them. Your active work stays visually prominent without deleting history.

Highlight the entire row when Days in Status exceeds 14. This catches stalled items before they become refund disputes. The rule uses a custom formula applied to the range, not just the single column.

Data Validation: Prevent Errors at Entry

Restrict the Status column to a dropdown list. No typos. No creative spellings. Just eight valid options. When someone tries to type Shippedd, the sheet rejects it.

Use custom number validation on price columns. Require positive numbers. Require whole numbers or two decimal places depending on the currency. This prevents accidental text entries like 50 yuan from corrupting your margin math.

Apply URL validation to the Product URL column. Require entries to start with http. This catches copy-paste errors where only half the URL makes it into the cell.

Google Apps Script: The Power Layer

Auto-Archive Script: Triggered daily, this script scans the Status column. Anything marked Sold for more than three days gets copied to the Archive sheet and deleted from Active. Your workspace stays clean without manual effort.

Weekly Email Report: Triggered every Monday at 9 AM. The script counts items by status, sums total estimated cost, and emails a summary to your inbox. You start the week with full visibility.

Stale Item Alert: Triggered daily. The script checks Days in Status. Anything over ten days in Issue or Purchased gets flagged in a Stale Items tab. You review that tab weekly and chase agents proactively instead of waiting for problems.

Setting Up Triggers

Open Extensions then Apps Script. Paste your script into the editor. Click the clock icon on the left to open Triggers. Add a trigger, choose your function, set the source to Time-driven, and pick a frequency. Daily at midnight is common for archive scripts. Weekly on Monday morning works well for email reports.

Always test a script manually before attaching a trigger. Run it from the editor, check the output, verify no errors appear in the Execution log. A broken trigger running daily will quietly corrupt your sheet while you sleep.

Automation Comparison

Continue Learning

Frequently Asked Questions