How to Clean Up and Bulk Edit Thousands of Metaobject Entries in Excel or Google Sheets

Someone decides “Organic cotton” should read “GOTS-certified organic cotton”, and that string lives in 1,400 metaobject entries. In the admin, that’s 1,400 edits. In a spreadsheet, it’s one find-and-replace, as long as you can get the data out and back in without the spreadsheet quietly rewriting your dates, dropping your leading zeros, or turning “é” into “é”.

This is the round trip we run on client stores: export, edit, check, import. It covers the spreadsheet traps behind most bad imports, the formulas we use for common clean-ups, and the one question to ask any import tool before you trust it with empty cells.

The round trip: export, edit, import

The whole workflow rests on one column: handle. On import, a row whose handle matches an existing entry updates that entry; a row with a new handle creates a new one. So:

• To update an entry, change its values and leave its handle exactly as exported.
• To create an entry, add a row with a new, unique handle.
• Never “tidy” handles during an edit. Renaming slim-fit-shirt-guide to slim-fit-shirt doesn’t rename anything; it creates a second entry and leaves the first one untouched.

Before you change a single cell, save the untouched export as size_guide-original.csv (or whatever the type is) and don’t open it again except to compare. It’s your diff baseline and, if the import goes wrong, your backup.

Spreadsheet traps that break imports

Excel

Excel’s defaults are built for accounting, not for data you’re going to send back to a system. Double-clicking a CSV to open it applies all of these:

• Dates are converted into your local display format and saved that way. Shopify date fields expect ISO 8601 (2026-10-12). A column that went in as 2026-10-12 can come out as 12/10/2026.
• Leading zeros vanish. A code like 00123 becomes 123.
• Long numbers turn into scientific notation, and anything past 15 digits loses precision. A 13-digit ID becomes 8.12346E+12 in the saved file.
• Encoding. Plain “CSV” can mangle accented characters. Save as CSV UTF-8 (Comma delimited).
• Booleans come out as TRUE/FALSE. Check what your importer accepts for boolean fields before you assume.

The fix is to stop Excel converting in the first place. Instead of double-clicking, use Data > From Text/CSV, choose the file, and set handle, date, code and ID columns to Text before loading. Recent Microsoft 365 versions also let you switch off the conversions under File > Options > Data > Automatic data conversion.

Google Sheets

Sheets is safer by default, with two traps:

• Locale. Check File > Settings > Locale. In a locale that uses a decimal comma, 10.4 can become 10,4, which a decimal field will reject.
• Import conversion. Use File > Import and untick “Convert text to numbers, dates, and formulas” so handles and codes stay exactly as exported.

Formulas are exported as their results, including error strings. A #REF! from a broken formula will be imported as the literal text #REF! unless something stops it.

Multi-line and rich text

A multi-line text value contains line breaks inside the cell. That’s valid CSV as long as the cell stays quoted, and every mainstream spreadsheet handles it, but hand-edited files in a text editor often break it. Rich text fields are worse: the value is a JSON document ({"type":"root","children":[...]}), and editing it in a spreadsheet cell is how malformed JSON gets into an import. Edit rich text in the admin, or have a developer generate it.

Bulk-edit recipes

All of these work in both Excel and Google Sheets. Do the work in a helper column, check it, then paste the results over the original column as values, and delete the helper.

Rename a value everywhere. Select the column first, then find and replace. Running find-and-replace on the whole sheet is how a word inside a handle gets changed too.

Add a field to existing entries. Add the field to the definition under Settings > Custom data, then export again. The new column arrives empty; fill it, import, and every entry updates.

Fix casing.

text

=PROPER(B2)      Slim fit chinos  ->  Slim Fit Chinos
=LOWER(B2)       Slim-Fit-Chinos  ->  slim-fit-chinos
=TRIM(B2)        removes leading, trailing and doubled spaces

Find duplicate handles. Sort the handle column, or flag them:

text

=COUNTIF(A:A,A2)>1

Swap a domain in every file URL.

text

=SUBSTITUTE(D2,"old-cdn.example.com","cdn.example.com")

Build handles from names for new rows (lowercase, spaces to hyphens):

text

=LOWER(SUBSTITUTE(TRIM(B2)," ","-"))

Check the result for characters handles don’t allow (anything other than lowercase letters, numbers and hyphens) before importing.

Empty cells: clear the field or leave it alone?

This is the question to answer before you delete a column or leave a cell blank, because tools don’t agree.

On the API itself, it depends on how the tool calls Shopify. metaobjectUpsert with the values argument replaces the whole entry: any field not sent is cleared. With metaobject: { fields: [...] } it patches only the fields you send. One tool treats a blank cell as “clear this field”, another as “don’t touch it”, and both are reasonable.

The safe habit, whatever you use: before editing a whole column, test on one throwaway entry. Export it, blank one optional field, import, and look at the entry in the admin. Thirty seconds, and you’ll never wipe a column by accident.

Before you import: diff the file

You have the original export. Use it.

1. Row count. If you were only editing, the edited file should have exactly as many rows as the original. More rows means you’ve created entries, probably through a changed handle.
2. Changed cells. Put the original in a second sheet named Original and add a helper column next to the field you changed, with =B2<>Original!B2. Filter for TRUE and read every changed row. If you expected 1,400 changes and see 1,412, find the twelve.
3. Handles. Check that every handle in the edited file exists in the original (for edits), with =COUNTIF(Original!A:A,A2)=0 flagging any that don’t.
4. The review step. When you upload, the counts of entries to be created and to be updated should match what you meant to do. Editing only? Creates should be zero.

In Metaobjectify, that last check is built into the import: every row is checked before anything is written, rows are marked as create or update, and problems such as a value that doesn’t fit the field’s type are listed row by row. The diff tells you whether you changed what you meant to change; the review tells you whether Shopify will accept it.

After the import

Read the job result: rows processed, succeeded and failed, with the exact error for each failed row. Fix those rows in the same spreadsheet and import just them again. Then open a handful of edited entries in the admin and on the storefront, including one at the very end of the file.

Keep the original export until you’re sure the change is right. If it isn’t, re-importing it puts every matching entry back the way it was. Backing up and restoring metaobjects covers that in detail, and catching bad data before import lists the checks worth running every time.