Hey there! Let's work through the two problems you hit when using setFormula() with VLOOKUP in Google Apps Script. I’ve run into both of these before, so I know exactly where to look.

1. Fixing the "Missing ) after argument list" Script Error #

This is a classic JavaScript syntax mistake, almost always caused by mismatched parentheses or unescaped quotes when building your VLOOKUP formula string.

Common Causes: #

• You forgot to close a parenthesis in your VLOOKUP formula (e.g., ending with FALSE instead of FALSE)).
• You used double quotes inside a double-quoted string without escaping them—Google Sheets formulas require quotes around range references sometimes, but JavaScript will interpret those as string terminators.

Solution: #

Use template literals (backticks `) to wrap your formula—this lets you use double quotes inside without escaping, and makes it easier to spot mismatched parentheses. Here’s a corrected example:

function addVlookup() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = spreadsheet.getSheetByName("YourSheetName");
  
  // Template literal handles quotes and parentheses cleanly
  const vlookupFormula = `VLOOKUP(A2, Sheet2!$A:$B, 2, FALSE)`;
  
  // Apply the formula to a cell
  targetSheet.getRange("B2").setFormula(vlookupFormula);
}

If you prefer using regular double quotes, escape internal quotes with a backslash:

const vlookupFormula = "VLOOKUP(A2, \"Sheet2!$A:$B\", 2, FALSE)";

Double-check that all opening parentheses ( have a matching closing )—count them if you have to!

2. Fixing the "Unknown range name: ROW" Sheet Error #

This error happens because Google Sheets thinks you’re referencing a named range called ROW, but you actually meant to use the ROW() function (which returns the current row number).

Common Cause: #

You omitted the parentheses after ROW in your formula. For example, writing VLOOKUP(ROW, Sheet2!A:B, 2, FALSE) instead of VLOOKUP(ROW(), Sheet2!A:B, 2, FALSE). Without parentheses, Sheets treats ROW as a named range instead of a function.

Solution: #

Add parentheses to call the ROW() function correctly. If you’re using it to get the current row for your lookup value, your formula should look like this:

const vlookupFormula = `VLOOKUP(ROW(), Sheet2!$A:$B, 2, FALSE)`;

If you did intend to use a named range called ROW, make sure you’ve properly created it in your spreadsheet:

• Go to Data > Named ranges
• Verify "ROW" exists and points to the correct range

Final Working Example #

Putting it all together, here’s a script that avoids both errors:

function workingVlookup() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Main");
  
  // Formula using ROW() function correctly, wrapped in template literal
  const formula = `VLOOKUP(ROW(), LookupData!$A:$C, 3, FALSE)`;
  
  // Apply to an entire column (optional)
  const range = sheet.getRange("C2:C100");
  range.setFormula(formula);
}

内容的提问来源于stack exchange，提问作者jptirona