Translate product sheets to French

translate_product_sheets_fr/
  manifest.md
  types.ts
  steps/
    01_read_source_rows.ts
    02_translate_rows.ts
    03_check_gpsr.ts
    04_ensure_fr_tab.ts
    05_write_results.ts
  tests/
    03_check_gpsr.test.ts

---
id: translate-product-sheets-fr
name: Translate product sheets to French
description: Reads a product CSV, translates the description column to French using an LLM, validates GPSR compliance, and writes the results to the FR tab of a Google Sheet.
version: "1.0.0"
created: "2025-01-15"
parameters:
  - name: input_file
    type: path
    description: Path to the source CSV file
  - name: spreadsheet_id
    type: string
    description: Google Sheets spreadsheet ID to write results to
  - name: dry_run
    type: boolean
    default: false
    description: If true, skip the write step
tools_required:
  - csvkit
mcp_servers:
  - google-sheets
---

Reads the source CSV (`input_file`), translates the `description` column to French
using GPT-4o-mini, validates each row for GPSR compliance, ensures the FR tab exists
in the target spreadsheet, and writes the translated rows.

Pass `dry_run=true` to validate and translate without writing to the spreadsheet.

import { z } from 'zod';

export const ProductRow = z.object({
  id: z.string(),
  name: z.string(),
  description: z.string(),
  manufacturer: z.string().optional(),
  sku: z.string().optional(),
});

export type ProductRow = z.infer<typeof ProductRow>;

import { step } from '@cori-do/sdk';
import { z } from 'zod';
import { ProductRow } from '../types';

export const input = z.object({
  input_file: z.string(),
});

export const output = z.object({
  rows: z.array(ProductRow),
});

export default step.cli({
  description: 'Read rows from the source CSV using csvkit',
  command: ({ input }) => `csvjson ${input.input_file}`,
  parse_output: (stdout) => ({
    rows: output.shape.rows.parse(JSON.parse(stdout)),
  }),
});

import { step } from '@cori-do/sdk';
import { z } from 'zod';
import { ProductRow } from '../types';

export const input = z.object({
  rows: z.array(ProductRow),
});

export const output = z.object({
  translated_rows: z.array(ProductRow),
});

export default step.llm({
  description: 'Translate the description field of each row to French',
  model: 'gpt-4o-mini',
  prompt: ({ input }) =>
    `Translate only the "description" field of each product to French. ` +
    `Return a JSON array with the same structure, all other fields unchanged.\n\n` +
    JSON.stringify(input.rows),
  output_schema: output,
  system: 'You are a professional product translator. Return only valid JSON.',
});

import { step } from '@cori-do/sdk';
import { z } from 'zod';
import { ProductRow } from '../types';

export const input = z.object({
  translated_rows: z.array(ProductRow),
});

export const output = z.object({
  valid_rows: z.array(ProductRow),
  issues: z.array(z.string()),
});

export default step.code({
  description: 'Filter out rows that fail GPSR compliance checks',
  run: ({ input }) => {
    const issues: string[] = [];
    const valid_rows = input.translated_rows.filter((row) => {
      if (!row.manufacturer) {
        issues.push(`Row ${row.id} (${row.name}): missing manufacturer`);
        return false;
      }
      return true;
    });
    return { valid_rows, issues };
  },
});

import { step } from '@cori-do/sdk';
import { z } from 'zod';

export const input = z.object({
  spreadsheet_id: z.string(),
});

export const output = z.object({
  sheet_id: z.number(),
});

export default step.mcp_tool({
  description: 'Ensure the FR tab exists in the target spreadsheet, creating it if needed',
  server: 'google-sheets',
  tool: 'ensure_sheet',
  args: ({ input }) => ({
    spreadsheet_id: input.spreadsheet_id,
    title: 'FR',
  }),
  parse_output: (result: { sheet_id: number }) => ({
    sheet_id: result.sheet_id,
  }),
});

import { step } from '@cori-do/sdk';
import { z } from 'zod';
import { ProductRow } from '../types';

export const input = z.object({
  valid_rows: z.array(ProductRow),
  spreadsheet_id: z.string(),
  sheet_id: z.number(),
  dry_run: z.boolean(),
});

export const output = z.object({
  rows_written: z.number(),
  skipped: z.boolean(),
});

export default step.cli({
  description: 'Write translated rows to the FR tab (skipped if dry_run is true)',
  command: ({ input }) => {
    if (input.dry_run) {
      return `echo "dry_run=true, skipping write of ${input.valid_rows.length} rows"`;
    }
    const json = JSON.stringify(input.valid_rows);
    return (
      `sheets-cli write ` +
      `--spreadsheet-id ${input.spreadsheet_id} ` +
      `--sheet-id ${input.sheet_id} ` +
      `--data '${json}'`
    );
  },
  parse_output: (_stdout, { input }) => ({
    rows_written: input.dry_run ? 0 : input.valid_rows.length,
    skipped: input.dry_run,
  }),
});

import { describe, it, expect } from 'vitest';
import step, { input, output } from '../steps/03_check_gpsr';

describe('03_check_gpsr', () => {
  it('passes rows with manufacturer', () => {
    const testInput = input.parse({
      translated_rows: [
        { id: '1', name: 'Widget', description: 'Un widget', manufacturer: 'ACME' },
        { id: '2', name: 'Gadget', description: 'Un gadget', manufacturer: 'Globocorp' },
      ],
    });
    const result = output.parse(step.run({ input: testInput }));
    expect(result.valid_rows).toHaveLength(2);
    expect(result.issues).toHaveLength(0);
  });

  it('filters rows missing manufacturer', () => {
    const testInput = input.parse({
      translated_rows: [
        { id: '1', name: 'Widget', description: 'Un widget', manufacturer: 'ACME' },
        { id: '2', name: 'No-mfr', description: 'Sans fabricant' },
      ],
    });
    const result = output.parse(step.run({ input: testInput }));
    expect(result.valid_rows).toHaveLength(1);
    expect(result.valid_rows[0].id).toBe('1');
    expect(result.issues).toHaveLength(1);
    expect(result.issues[0]).toMatch(/missing manufacturer/);
  });
});

# Validate without executing
cori check ./translate_product_sheets_fr

# Dry run — translates but skips the write
cori run --dry-run ./translate_product_sheets_fr \
  input_file=products.csv \
  spreadsheet_id=1BxiM...

# Full run
cori run ./translate_product_sheets_fr \
  input_file=products.csv \
  spreadsheet_id=1BxiM...