如何将导入的Google App Script库函数用作表格单元格自定义公式？

Why You're Seeing the #NAME? Error #

Google Sheets only recognizes functions defined directly in the spreadsheet's bound script as custom functions for cell formulas. Imported library functions aren't automatically exposed to the formula environment, which is why =SheetUtilities.foo() or =foo() throws an error.

Solution 1: Manual Wrappers (For a Small Number of Functions) #

If your library only has a few functions, the simplest fix is to write basic wrapper functions in your SheetTest's bound script:

// Wrapper for SheetUtilities.foo() - now usable as =foo() in cells
function foo() {
  return SheetUtilities.foo(...arguments);
}

// For functions with parameters, e.g., SheetUtilities.bar(a, b)
function bar(a, b) {
  return SheetUtilities.bar(a, b);
}

Save the script, refresh your SheetTest spreadsheet, and you'll be able to call =foo() or =bar(1, 2) directly in cells.

Solution 2: Auto-Generate Wrappers (For Large Libraries) #

If your SheetUtilities library has dozens of functions, manual wrappers are tedious. Use this script to auto-generate wrappers for all public library functions:

function createLibraryWrappers() {
  // Get all public function names from the library
  const allLibraryProps = Object.getOwnPropertyNames(SheetUtilities);
  const targetFunctions = allLibraryProps.filter(prop => 
    typeof SheetUtilities[prop] === 'function' && !prop.startsWith('_')
  );

  // Generate wrapper function code
  let wrapperCode = '';
  targetFunctions.forEach(funcName => {
    wrapperCode += `function ${funcName}() {
  return SheetUtilities.${funcName}(...arguments);
}\n\n`;
  });

  // Write the wrappers to your bound script (requires Apps Script API enabled)
  const scriptProject = ScriptApp.getProject();
  const codeFiles = scriptProject.getFiles();
  let codeFile;

  // Locate your main code file (adjust the name if you use a different filename)
  while (codeFiles.hasNext()) {
    const file = codeFiles.next();
    if (file.getName() === 'Code.gs') {
      codeFile = file;
      break;
    }
  }

  if (codeFile) {
    // Remove existing wrappers to avoid duplicates
    const existingCode = codeFile.getContent();
    const cleanedCode = existingCode.replace(/function \w+\(\) {[\s\S]*?return SheetUtilities\.\w+\(\.\.\.arguments\);\n}/g, '');
    codeFile.setContent(cleanedCode + '\n\n' + wrapperCode);
    Logger.log(`Successfully generated ${targetFunctions.length} library function wrappers`);
  } else {
    Logger.log("Couldn't find Code.gs - adjust the filename in the script to match your project");
  }
}

Setup Steps: #

1. Enable the Apps Script API for your project: Click the gear icon (Project Settings) in the script editor, then check "Enable Apps Script API".
2. Run createLibraryWrappers and authorize the required permissions.
3. Refresh your spreadsheet—all library functions will now work as cell custom functions.

Making the Library Universal Across Spreadsheets #

To use SheetUtilities in any new spreadsheet without repeating setup:

• Create a template spreadsheet: Pre-configure it with the library import and auto-generated wrappers. Copy this template whenever you need a new sheet with library access.
• Save the wrapper generation script as a code snippet: Keep it handy, and paste/run it in the bound script of any new spreadsheet to quickly set up all wrappers.

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