Marketing Price List Sync in Practice: A Single Strategy from Feishu Sheets to MySQL
What This Strategy Solves
A marketing-center price list is often maintained by business teams in an online sheet and referenced by stores, promotion plans, and reports every day. Once maintained manually, the problems are well known: prices change out of sync, and definitions drift. The goal of this strategy is straightforward—on a daily schedule, land the price data from a Feishu sheet into a MySQL marketing-center price table, so every downstream system reads from the same source. In a real engagement, we used the Qeasy (轻易云) data-integration platform to carry this strategy and treat it as a stable foundation for the ten-plus sync tasks that came after it.
Data Flow and Field Mapping
The flow is one-way: Feishu (system B) → MySQL (system A). Below is the field mapping that we recommend aligning first, before any configuration work.
| Feishu sheet field (source) | MySQL target field | Type | Notes |
|---|---|---|---|
| id | id | int | Primary key, basis for idempotency |
| 日期 | 日期 | datetime | Effective date from the sheet |
| 物料编码 | 物料编码 | string | Source has whitespace, TRIM before write |
| 物料名称 | 物料名称 | string | Direct pass-through |
| 营销中心成本(单瓶\含税) | 营销中心成本(单瓶\含税) | float | Numeric, watch empty values |
| 店铺成本价(单瓶\含税) | 店铺成本价(单瓶\含税) | float | Same as above |
On the source side, the strategy calls GET /open-apis/sheets/v2/spreadsheets/:spreadsheetToken/values/:range. On the target side, MySQL receives the data via batchexecute, essentially a parameterised SQL. There is no heavy transformation here—price fields are passed through, and the only intentional transformation is TRIM on the material code. This is the kind of detail that is easy to overlook, and three months later it is exactly what causes the row counts to stop matching.
Configuring It on Qeasy
On the source side, create a Feishu connector. Pick /open-apis/sheets/v2/spreadsheets/:spreadsheetToken/values/:range as the API, with effect QUERY and method GET. Pin two query parameters: valueRenderOption=ToString and dateTimeRenderOption=FormattedString. This prevents numbers from being rendered in scientific notation and dates from being turned into timestamps. Set headers_line=1 so row one is the header and row two onward is data.
On the target side, choose MySQL with effect EXECUTE, going through batchexecute. Each field maps to a {{variable}} placeholder. The material-code field wraps its placeholder in _function TRIM('{{物料编码}}'). Keeping light cleansing inside Qeasy's field-mapping layer is one of the most common patterns among Qeasy customers—it gives the team a single place to answer the question "where exactly does this cleaning logic live," so future source or target swaps do not scatter logic across the pipeline.
Implementation Steps
- Create the target table in MySQL first. Align field names, types, and empty-value policy with the Feishu sheet, and lock in a stable primary key for id.
- Trigger one manual full run to verify the field mapping and the TRIM behaviour. Run it against a test database first, then move to production.
- Wire up the schedule and set the incremental starting point. Set the source crontab to
0 1 * * *(pull the sheet every day at 01:00), and offset the target by 30 minutes to30 1 * * *. Offsetting avoids the classic race where the source read and target write collide in the same second and you end up with cross-tick misalignment. - Watch three full scheduling cycles. Compare row counts, daily volume variance, and empty-value ratios. Confirm there is no "sudden cliff drop" pattern on any day.
- Decide whether to layer a periodic full sync after it stabilises. Daily increments carry the routine load, while a monthly or quarterly full comparison catches drift. This "incremental plus full dual-track" pattern is common among Qeasy customers.
Lessons Learned
- Whitespace and invisible characters. Material codes pasted from a sheet frequently carry leading or trailing spaces, or even stray line breaks. Skipping the TRIM step will quietly break joins months later.
- Number render mode. Amounts in a Feishu sheet may come through as strings with thousands separators or currency symbols. Pin
valueRenderOption=ToString; otherwise the platform may interpret them as scientific notation or timestamps. - Empty-value handling. When a floating-point field is blank in the source, do not silently write 0 to MySQL. Decide whether it should be NULL or 0, and lock that decision in during configuration.
- Do not schedule source and target at the same second. When read and write overlap, the previous tick may not be finished before the next tick begins. A 30-minute offset is the safe default.
- Keep the idempotency key stable. This strategy uses id as the primary key. If the id rule in the Feishu sheet ever changes, the incremental logic will silently break. Fix the id rule at the source and do not change it lightly.
When It Fits and When It Does Not
It fits when the source data is maintained by business users in a Feishu sheet, the volume is in the tens-of-thousands range or less, the data only needs a daily refresh, and there is no strict real-time requirement—typical for price lists and master-data sync. It does not fit when the volume is very large, the requirement is minute-level real time, the source is not a Feishu sheet, or the field transformations are too complex to be expressed inside Qeasy's mapping layer.