If you have inherited a Google Apps Script project from someone else, there are two defects worth checking before you fix anything at all. Both are silent. Neither throws an error. And the first one can make every other fix you attempt appear not to work, which is a genuinely maddening way to spend a weekend.
This is the one that wastes the most time, because it makes your fixes look like they failed.
Apps Script has no module system in the classic .gs runtime. Every file in the project is concatenated into one global scope before anything runs. There is no per-file privacy and no import. So if Utils.gs declares function parseAmount(value) { return Number(value); } and Helpers.gs declares function parseAmount(value) { return Math.round(Number(value) * 100) / 100; } you do not get an error, a warning, or a conflict. You get ONE parseAmount, and which one survives depends on file load order. Function declarations hoist, so the last declaration processed wins for the entire project.
The reason this burns people is the failure signature. You find a bug, you fix it carefully in Utils.gs, you test the function in isolation from the editor, and it works. Then you run the real workflow and the old broken behaviour is still there, because at runtime the call was resolving to the copy in Helpers.gs all along.
It gets worse if the duplication happened for an innocent reason. A very common history looks like this: someone was asked to fix a bug, they pasted a corrected version of a file into the project as a NEW file rather than replacing the original, everything appeared fine in their own tests, and they moved on. Now the project has two copies of a dozen functions, silently differing, and nobody knows which ones are live.
How to check. Before touching anything, list every function declaration in the project and look for names that appear more than once. If you have the project locally via clasp, ripgrep will do it in one line: rg -n "^\s*function\s+(\w+)" -r '$1' --no-filename | sort | uniq -d
Any name that prints is a name whose behaviour is decided by file order rather than by you.
How to fix it, in the right order. Do not simply delete the duplicates you think are older. First determine which copy is actually winning, because the live behaviour is your baseline and some of it may be load-bearing: other code may have been written to compensate for the surviving version's quirks. A quick way to find out, without changing behaviour, is to log the function's own source: function whichCopyIsLive() { Logger.log(parseAmount.toString()); }
That prints the source of the function that actually resolves. Once you know, consolidate to a single declaration deliberately, run your workflow, and only then start fixing bugs. Fixing first and de-duplicating later means you cannot trust any result you get in between.
This one is a plain JavaScript gotcha rather than an Apps Script one, but spreadsheets are exactly where it does financial damage.
'1.234.567'.replace('.', '') returns "1234.567". Only the first dot went. When the first argument to replace is a STRING, only the first occurrence is replaced. You need a global regular expression to replace all of them: '1.234.567'.replace(/\./g, '') returns "1234567".
Now consider the very common pattern of normalising a European-formatted number, where the dot is the thousands separator and the comma is the decimal, before parsing it: function parseAmount(value) { const n = parseFloat(String(value).replace(',', '.').replace(/[^\d.-]/g, '')); return isNaN(n) ? 0 : n; }
It looks reasonable. It is badly wrong for exactly the inputs it was written for. 1234,56 gives 1234.56, which is fine. 1 234,56 gives 1234.56, also fine. But 1.234,56 gives 1.234, wrong by a factor of a thousand. 1,234.56 gives 1.234, wrong by a factor of a thousand. And 1.550,00 gives 1.55, wrong by a factor of a thousand.
The thousands separator survives as a second decimal point, and parseFloat, which parses the longest valid numeric prefix and then stops, stops at it.
What makes this dangerous in a spreadsheet is that the output is still a plausible number. 1.55 is not obviously corrupt the way NaN or "1.234.567" would be. It formats cleanly, it sums, it charts. It is simply wrong.
A version that handles both conventions. Every case below was covered by a test run in Node before publishing, including the two cases that caught bugs in the first draft of it.
function parseAmount(value, decimalHint) {
if (value === null || value === undefined || value === '') return null;
if (typeof value === 'number') return Number.isFinite(value) ? value : null;
const raw = String(value);
if (!/\d/.test(raw)) return null;
const neg = /^\s*[-(]/.test(raw);
let s = raw.replace(/[^\d.,]/g, '');
if (s === '') return null;
const commas = (s.match(/,/g) || []).length;
const dots = (s.match(/\./g) || []).length;
if (commas && dots) {
const decimalIsComma = s.lastIndexOf(',') > s.lastIndexOf('.');
s = decimalIsComma ? s.replace(/\./g, '').replace(',', '.') : s.replace(/,/g, '');
} else if (commas || dots) {
const sep = commas ? ',' : '.';
const count = commas || dots;
const after = s.length - s.lastIndexOf(sep) - 1;
if (count > 1) { s = s.split(sep).join(''); }
else if (after === 3) { s = (decimalHint === sep) ? s.replace(sep, '.') : s.split(sep).join(''); }
else { s = s.replace(sep, '.'); }
}
const n = Number(s);
return Number.isFinite(n) ? (neg ? -n : n) : null;
}
Note what it returns when it cannot read the value: null, never zero. The ambiguous branch is the one worth reading twice. "1.550" is either one thousand five hundred and fifty or one point five five, and the string alone cannot say which, so the parser applies the caller's documented hint and defaults to grouping.
And the part most parsers leave out is a way to ask whether the value was ambiguous at all, so the caller can flag it instead of silently accepting a guess.
function isAmbiguousAmount(value) {
const s = String(value == null ? '' : value).replace(/[^\d.,]/g, '');
const commas = (s.match(/,/g) || []).length;
const dots = (s.match(/\./g) || []).length;
if (commas && dots) return false;
if (!commas && !dots) return false;
if ((commas || dots) > 1) return false;
const sep = commas ? ',' : '.';
return s.length - s.lastIndexOf(sep) - 1 === 3;
}
"1.550" returns 1550 by default, or 1.55 if you pass '.' as the hint, and isAmbiguousAmount("1.550") is true either way. "1.234,56", "1234,56" and "1550" are all false: they are decidable from the string, so there is nothing to flag.
Ambiguity here is not a flaw in the parser. 1,234 is either one thousand two hundred and thirty-four or one point two three four, and no function can know which from the string alone. The point is to stop pretending otherwise: apply a documented rule, and hand the caller a way to see which rows the rule had to guess on.
The part that matters more than either bug
Notice what changed in that rewrite besides the parsing: it returns null on failure, not 0.
That single choice matters more than the parsing logic, and it is where inherited spreadsheet code most often goes wrong. Returning 0 for "I could not read this" produces a guard like this: if (!expected || !actual || Math.abs(actual - expected) < 0.01) { return true; }
Zero is falsy, so an unreadable value makes !actual true, so the function reports a match without ever comparing anything. A blank cell, a typo and a correct value all produce the same answer. That is a guard which fails OPEN: on bad input it says yes.
When you are reconciling money, fail-open is the worst available default. And it is self-concealing, because the wrong answers look exactly like the right ones, so the only way anyone discovers the problem is by checking every row by hand, which defeats the purpose of the script.
The fix is a design habit rather than a patch. Return a distinguishable unknown: null, not 0, not an empty string. Make unknown fail closed, so unknown means not matched and it means flagged. Write the reason down where the user will see it, because a column with the confidence and the reason is worth more than a boolean: it tells someone which rows to actually look at. And never swallow errors into a log line, because a catch that logs and returns normally means the caller cannot tell success from failure.
A script that says "these 6 of 200 rows need a human, here is why" is far more valuable than one that silently claims all 200 are fine. The first one you can trust and stop checking. The second one you have to check forever.
List all function declarations and find duplicate names.
For each duplicate, log fn.toString() to see which copy is live.
Consolidate to one declaration per name BEFORE fixing any bug.
Grep for string-argument replaces on separators: .replace(',', and .replace('.', are the two to look for.
Grep for parse helpers returning 0 on failure.
Grep for guards of the shape if (!a || !b) return true.
Grep for catch blocks whose only action is a log.
Establish a regression baseline from data already in the sheet, so every fix is measured rather than asserted.
That last one is the difference between "I think it's fixed" and a number you can show someone.
Written by Daniel Muguira, Bright Bilby. If you have inherited a Google Sheets or Apps Script project that nobody trusts any more, I audit them and fix them, and the deliverable includes the evidence rather than a claim.