Translation coverage grid for your published WordPress posts

Build an app my content team opens every week to see which of our published posts exist in which languages, and which translations have quietly gone out of date. That currently lives in a spreadsheet nobody maintains, so this app should become the place that answers the question.

The main view is a coverage grid. Load every published post from our site with WordPress List Posts, filtering to published status and paging through the full archive, and lay the posts out as rows with one column per target language. Work out which language a post belongs to from our site convention, where each post carries a category or tag naming its language, so the English originals become the rows and their existing localized versions fill in the cells. Each cell shows one of four states: live, draft, stale, or missing.

Staleness comes from a register we keep in Google Sheets, read with Google Sheets Get Values. Each row records an English post id, a target language, the id of the translated post, and the English revision that translation was made from, which is the English post's last modified timestamp at the moment of translation. A cell is stale when the English post has been modified since the revision recorded for that language. A cell is missing when there is no register row and no localized post, draft when the localized post exists but is not published, and live when it is published. Where several rows exist for the same post and language, the most recent one wins, so the register can stay append only.

Sort rows so the posts that actually matter surface first. Default to a priority score built from what WordPress gives us, meaning recency of last modification and comment activity, and let the register carry an optional pageviews column that overrides that score when it is filled in, so a team can paste numbers from their analytics tool and have the busiest posts rise to the top. Add a language filter that collapses the grid to a single column and shows just that language's backlog, plus a state filter so a reviewer can jump straight to everything stale.

Let the user select any number of cells or rows and press Translate. That runs a background agent, one job per post and language pair. The agent takes the English body, translates it with DeepL Translate Text into the target language, creates the localized version with WordPress Create a Post as a draft carrying the category or tag for that language, and appends a row to the register with Google Sheets Append Values recording the English post id, the language, the new draft's id, and the English modified timestamp the translation was made from, so the staleness column stays honest. When a target already exists and is merely stale, the agent produces a refreshed draft the same way and appends a new register row rather than rewriting history.

Translation fidelity matters more than speed here. Turn on DeepL's HTML tag handling so the post markup survives, add our code block tags to the ignored tag list so code samples pass through untouched, and wrap shortcodes and internal link targets in non translatable markers before sending so they come back exactly as they went in. Map our site's language labels onto DeepL's codes using DeepL List Supported Languages, keeping regional variants such as Brazilian Portuguese and British English distinct where our convention has them. Before a large batch, call DeepL Check Usage and Limits and show the remaining character allowance for the period, warning the user when the current selection would exceed it.

Selecting any cell opens a review view with the English source and the translation side by side, scrolling together, with the translation editable in place. A reviewer can fix wording, then press Publish, which takes the draft live with WordPress Update a Post and writes that row's status back to the register with Google Sheets Update Values. Nothing reaches the site without that press.

Show a small header summary of how many posts are fully covered, how many translations are stale, and how many language slots are still empty, so the weekly open starts with the numbers that matter. Remember each viewer's last language and state filter so they land back where they left off, and record who ran each translation and who published it in the register so the team can see the trail.