DEV Community

Aulvem
Aulvem

Posted on

A Google Sheets .xlsx breaks silently in Excel: the 203 cells IFERROR swallowed

#python #excel #googlesheets #automation

Export a Google Sheet to .xlsx, hand it to someone, and they open a different file than the one you tested. ARRAYFORMULA and QUERY don't exist in Excel, which is unsurprising — the annoying part is that nothing errors.

I opened a file I'd been shipping with openpyxl and found 203 cells still holding Google-only functions. All 203 were wrapped in IFERROR, so Excel showed "no matches" instead of #NAME?. Here's how to detect that, and how to rewrite it without leaning on dynamic arrays.

Why no error shows up

Google-only functions survive the export as __xludf.DUMMYFUNCTION("original formula"), with the original kept as a string argument. Excel stores it and never evaluates it.

=IFERROR(__xludf.DUMMYFUNCTION("IFERROR(SORTN(FILTER({'パスワード一覧'!A2:A80, …
Enter fullscreen mode Exit fullscreen mode

The IFERROR I'd added on the Sheets side — to print an em dash when nothing matched — also catches "this function does not exist" in Excel. What comes back is an empty string or the fallback text, so the sheet reads "— no matches —" and looks perfectly healthy.

flowchart TD
  A["Sheets formula<br/>ARRAYFORMULA / FILTER / SORTN / QUERY"] --> B["Opened in Excel"]
  B --> C["Function does not exist"]
  C --> D["The outer IFERROR catches it"]
  D --> E["Empty string returned"]
  E --> F["Screen shows '— no matches —'"]
Enter fullscreen mode Exit fullscreen mode

The display for zero results and the display for a formula that never ran are identical. You can't spot the difference by looking.

Detection is one pass with openpyxl

__xludf.DUMMYFUNCTION persists as text, so you can count it. The same code confirms you reached zero after the rewrite.

import openpyxl

KEYS = ("DUMMYFUNCTION", "REGEXMATCH", "SORTN",
        "QUERY(", "ARRAYFORMULA", "UNIQUE(", "FILTER(")

def check_no_google_funcs(path):
    wb = openpyxl.load_workbook(path)
    bad = []
    for sh in wb.worksheets:
        for row in sh.iter_rows():
            for c in row:
                v = c.value
                txt = getattr(v, "text", None) or (v if isinstance(v, str) else "")
                # formula cells only — prose sheets mention these names too
                if not txt.startswith("="):
                    continue
                if any(k in txt for k in KEYS):
                    bad.append(f"{sh.title}!{c.coordinate}")
    return bad
Enter fullscreen mode Exit fullscreen mode

Keep that txt.startswith("=") guard. If the workbook has a how-to sheet, its FAQ answers will use the word ARRAYFORMULA in ordinary prose, and you'll end up with unfixable text in your list of things to fix.

Don't reach for Excel 365's dynamic arrays

FILTER, SORT, and UNIQUE map closely onto the Google formulas, which makes them the obvious swap. They also don't exist in Excel 2019 or 2016. For a file you hand to strangers you can't assume a version, so they were off the table here.

Approach Sheets Excel 365 Excel 2016
ARRAYFORMULA / QUERY / SORTN works no no
FILTER / SORT / UNIQUE (dynamic arrays) works works no
Per-row helper columns + INDEX/MATCH works works works

The approach is to split "sort and take the top N" into two steps: each row computes its own rank into a helper column, and the display side just asks for the row whose rank is n. Remove the sorting operation and the need for dynamic arrays goes with it.

Compute the rank per row with SUMPRODUCT

"Only rows past 90 days elapsed (column L), ordered descending" becomes this. RANK doesn't work because the ranking applies to a filtered subset.

=IF(AND($A2<>"",ISNUMBER($L2),$L2>90),
   SUMPRODUCT(($A$2:$A$200<>"")*ISNUMBER($L$2:$L$200)*($L$2:$L$200>90)
   *(($L$2:$L$200>$L2)+(($L$2:$L$200=$L2)*(ROW($L$2:$L$200)<ROW($L2)))))+1,"")
Enter fullscreen mode Exit fullscreen mode

Multiply the conditions together, count the rows that beat you, add one. Pasting 200 rows of that by hand isn't realistic, so I generated it per row from Python.

def rank_renew_formula(row):
    L, A = f"$L{row}", f"$A{row}"
    la, lb = f"$L$2:$L${LAST_ROW}", f"$A$2:$A${LAST_ROW}"
    rows = f"ROW($L$2:$L${LAST_ROW})"
    return (
        f'=IF(AND({A}<>"",ISNUMBER({L}),{L}>90),'
        f'SUMPRODUCT(({lb}<>"")*ISNUMBER({la})*({la}>90)*'
        f'(({la}>{L})+(({la}={L})*({rows}<ROW({L})))))+1,"")'
    )
Enter fullscreen mode Exit fullscreen mode

Gotcha 1: drop ISNUMBER and every blank row joins the ranking. Column L returns "" where there's nothing to measure, and Excel evaluates text as greater than any number — so $L$2:$L$200>90 on its own lets the blanks through. The numeric test has to be part of the product.

Replace REGEXMATCH with an array constant and SUBSTITUTE

Character-class checks like REGEXMATCH(E2, "[A-Z]") don't port either. Instead of matching, count occurrences per class.

def char_class_count(cell, chars):
    arr = "{" + ",".join(f'"{c}"' for c in chars) + "}"
    return f'SUMPRODUCT(LEN({cell})-LEN(SUBSTITUTE({cell},{arr},"")))'
Enter fullscreen mode Exit fullscreen mode

Handing SUBSTITUTE an array constant of {"A","B",…,"Z"} returns 26 strings, each missing one letter. Sum the length deltas with SUMPRODUCT and you have the uppercase count. SUBSTITUTE is case-sensitive, so [A-Z] and [a-z] stay distinct for free, and symbols fall out of total length minus upper, lower, and digits.

Gotcha 2: avoid splitting the string with INDIRECT. My first version used MID(E2, ROW(INDIRECT("1:"&LEN(E2))), 1) with FIND, and verification rated every single password as weak. INDIRECT array expansion varies across environments, and when it fails it lands in the IFERROR branch — so once again, everything goes wrong quietly.

The display side is one INDEX/MATCH

With ranks in a helper column, the dashboard needs a single formula.

=IFERROR(INDEX('パスワード一覧'!A$2:A$200,
         MATCH(1,'パスワード一覧'!$R$2:$R$200,0)),"")
Enter fullscreen mode Exit fullscreen mode

Change the first MATCH argument to 1, 2, 3 … and you walk the list in order. IFERROR appears here too, but now it only carries its intended meaning: no row has rank 3, so there are fewer than three matches.

If you build in Google Sheets and ship .xlsx, open the export with something other than a spreadsheet at least once. It's a twenty-line script, and it doubles as your after-the-fix check.

Breaking ties when two rows share a rank, the time I passed dates as strings and silenced three metrics at once, and the full breakdown of all five broken spots are written up on Aulvem → Aulvem | A Google Sheets .xlsx breaks silently in Excel. The corrected file is live on the password manager sheet.

Top comments (0)