谷歌表格DeepL API脚本报错：httpRequestWithRetries_未定义求助

我尝试在谷歌表格中使用DeepL API函数将日文文本翻译成英文，复制了官方示例脚本并替换了API密钥，但调用函数=deepL(cell, target_lan, source_lan)时出现以下错误：

ReferenceError: httpRequestWithRetries_ is not defined (line 84).

我使用的代码如下：

/**
 * Translates from one language to another using the DeepL Translation API.
 *
 * Note that you need to set your DeepL auth key by calling DeepLAuthKey() before use.
 *
 * @param {"Hello"} input The text to translate.
 * @param {"en"} sourceLang Optional. The language code of the source language.
 *   Use "auto" to auto-detect the language.
 * @param {"es"} targetLang Optional. The language code of the target language.
 *   If unspecified, defaults to your system language.
 * @param {"def3a26b-3e84-..."} glossaryId Optional. The ID of a glossary to use
 *   for the translation.
 * @param {cell range} options Optional. Range of additional options to send with API translation
 *   request. May also be specified inline e.g. '{"tag_handling", "xml"; "ignore_tags", "ignore"}'
 * @return Translated text.
 * @customfunction
 */


function DeepLTranslate(input,
                        sourceLang,
                        targetLang,
                        glossaryId,
                        options
) {
    if (input === undefined) {
        throw new Error("input field is undefined, please specify the text to translate.");
    } else if (typeof input === "number") {
        input = input.toString();
    } else if (typeof input !== "string") {
        throw new Error("input text must be a string.");
    }
    // Check the current cell to detect recalculations due to reopening the sheet
    const cell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getCurrentCell();

    if (disableTranslations) {
        Logger.log("disableTranslations is active, skipping DeepL translation request");
        return cell.getDisplayValue();
    }

    if (activateAutoDetect &&
            cell.getDisplayValue() !== "" &&
            cell.getDisplayValue() !== "Loading...") {
        Logger.log("Detected cell-recalculation, skipping DeepL translation request");
        return cell.getDisplayValue();
    }

    if (!targetLang) targetLang = selectDefaultTargetLang_();
    let formData = {
        'target_lang': targetLang,
        'text': input
    };
    if (sourceLang && sourceLang !== 'auto') {
        formData['source_lang'] = sourceLang;
    }
    if (glossaryId) {
        formData['glossary_id'] = glossaryId;
    }
    if (options) {
        if (!Array.isArray(options) ||
            !Object.values(options).every(function(value) {
                return Array.isArray(value) && value.length === 2;
            })) {
            throw new Error("options must be a range with two columns, or have the form '{"opt1", "val1"; "opt2", "val2"}'");
        }

        for (let i = 0; i < options.length; i++) {
            const items = options[i];
            const key = items[0];
            const value = items[1];
            formData[key] = value;
        }
    }

    const response = httpRequestWithRetries_('post', '/v2/translate', formData, input.length);
    checkResponse_(response);
    const responseObject = JSON.parse(response.getContentText());
    return responseObject.translations[0].text;
}

问题原因与解决方法 #

错误的核心是你只复制了DeepLTranslate主函数，而脚本依赖的多个辅助函数和全局变量都缺失了，包括：

• httpRequestWithRetries_()：处理带重试逻辑的API请求
• checkResponse_()：验证API响应是否正常
• selectDefaultTargetLang_()：自动选择默认目标语言
• DeepLAuthKey()：设置和存储你的DeepL API密钥
• 全局变量disableTranslations、activateAutoDetect：控制翻译行为的开关

你需要将完整的脚本复制到谷歌表格的脚本编辑器中，确保所有依赖的函数和变量都存在。以下是补充完整后的完整脚本：

// Set your DeepL API auth key here by calling DeepLAuthKey("your-key") once, or set it in the script properties.
let authKey;
let disableTranslations = false;
let activateAutoDetect = true;

/**
 * Sets the DeepL API authentication key. Run this once to store your key.
 * @param {string} key Your DeepL API authentication key.
 */
function DeepLAuthKey(key) {
  const properties = PropertiesService.getScriptProperties();
  properties.setProperty('DEEPL_AUTH_KEY', key);
  authKey = key;
  Logger.log('DeepL API key set successfully.');
}

/**
 * Translates from one language to another using the DeepL Translation API.
 *
 * Note that you need to set your DeepL auth key by calling DeepLAuthKey() before use.
 *
 * @param {"Hello"} input The text to translate.
 * @param {"en"} sourceLang Optional. The language code of the source language.
 *   Use "auto" to auto-detect the language.
 * @param {"es"} targetLang Optional. The language code of the target language.
 *   If unspecified, defaults to your system language.
 * @param {"def3a26b-3e84-..."} glossaryId Optional. The ID of a glossary to use
 *   for the translation.
 * @param {cell range} options Optional. Range of additional options to send with API translation
 *   request. May also be specified inline e.g. '{"tag_handling", "xml"; "ignore_tags", "ignore"}'
 * @return Translated text.
 * @customfunction
 */
function DeepLTranslate(input, sourceLang, targetLang, glossaryId, options) {
  if (input === undefined) {
    throw new Error("input field is undefined, please specify the text to translate.");
  } else if (typeof input === "number") {
    input = input.toString();
  } else if (typeof input !== "string") {
    throw new Error("input text must be a string.");
  }

  const cell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getCurrentCell();

  if (disableTranslations) {
    Logger.log("disableTranslations is active, skipping DeepL translation request");
    return cell.getDisplayValue();
  }

  if (activateAutoDetect && cell.getDisplayValue() !== "" && cell.getDisplayValue() !== "Loading...") {
    Logger.log("Detected cell-recalculation, skipping DeepL translation request");
    return cell.getDisplayValue();
  }

  if (!authKey) {
    const properties = PropertiesService.getScriptProperties();
    authKey = properties.getProperty('DEEPL_AUTH_KEY');
    if (!authKey) {
      throw new Error("DeepL API key not set. Please run DeepLAuthKey(\"your-key\") first.");
    }
  }

  if (!targetLang) targetLang = selectDefaultTargetLang_();
  let formData = {
    'target_lang': targetLang,
    'text': input
  };
  if (sourceLang && sourceLang !== 'auto') {
    formData['source_lang'] = sourceLang;
  }
  if (glossaryId) {
    formData['glossary_id'] = glossaryId;
  }
  if (options) {
    if (!Array.isArray(options) || !Object.values(options).every(function(value) {
      return Array.isArray(value) && value.length === 2;
    })) {
      throw new Error("options must be a range with two columns, or have the form '{\"opt1\", \"val1\"; \"opt2\", \"val2\"}'");
    }

    for (let i = 0; i < options.length; i++) {
      const items = options[i];
      const key = items[0];
      const value = items[1];
      formData[key] = value;
    }
  }

  const response = httpRequestWithRetries_('post', '/v2/translate', formData, input.length);
  checkResponse_(response);
  const responseObject = JSON.parse(response.getContentText());
  return responseObject.translations[0].text;
}

/**
 * Sends an HTTP request to the DeepL API with retries for transient errors.
 * @param {string} method HTTP method (get/post).
 * @param {string} path API endpoint path.
 * @param {Object} formData Form data for POST requests.
 * @param {number} textLength Length of the input text for rate limiting.
 * @return {HTTPResponse} The API response.
 */
function httpRequestWithRetries_(method, path, formData, textLength) {
  const baseUrl = authKey.endsWith(':fx') ? 'https://api-free.deepl.com' : 'https://api.deepl.com';
  const url = baseUrl + path;
  const maxRetries = 3;
  let retryCount = 0;

  while (retryCount <= maxRetries) {
    try {
      const options = {
        'method': method,
        'headers': {
          'Authorization': 'DeepL-Auth-Key ' + authKey
        },
        'payload': formData,
        'muteHttpExceptions': true
      };

      // Rate limiting: free tier allows 500k characters/month, paid varies. Adjust as needed.
      if (authKey.endsWith(':fx')) {
        Utilities.sleep(Math.max(1000, textLength * 2)); // Simple rate limit for free tier
      }

      const response = UrlFetchApp.fetch(url, options);
      const statusCode = response.getResponseCode();

      if (statusCode >= 500 && statusCode < 600) {
        throw new Error('Transient server error: ' + statusCode);
      }

      return response;
    } catch (e) {
      retryCount++;
      if (retryCount > maxRetries) {
        throw new Error('Failed after ' + maxRetries + ' retries: ' + e.message);
      }
      Utilities.sleep(2000 * retryCount); // Exponential backoff
    }
  }
}

/**
 * Checks the API response for errors and throws an exception if needed.
 * @param {HTTPResponse} response The API response.
 */
function checkResponse_(response) {
  const statusCode = response.getResponseCode();
  const responseText = response.getContentText();

  if (statusCode !== 200) {
    let errorMessage = 'DeepL API request failed with status ' + statusCode;
    try {
      const errorObject = JSON.parse(responseText);
      if (errorObject.message) {
        errorMessage += ': ' + errorObject.message;
      }
    } catch (e) {
      // Ignore parsing errors, use basic message
    }
    throw new Error(errorMessage);
  }
}

/**
 * Selects the default target language based on the user's system language.
 * @return {string} The default target language code.
 */
function selectDefaultTargetLang_() {
  const userLocale = Session.getActiveUserLocale();
  const langMap = {
    'en': 'EN',
    'es': 'ES',
    'fr': 'FR',
    'de': 'DE',
    'ja': 'JA',
    'zh': 'ZH'
    // Add more language mappings as needed
  };
  const langCode = userLocale.split('_')[0];
  return langMap[langCode] || 'EN'; // Default to English if not found
}

后续步骤 #

1. 将上述完整脚本粘贴到谷歌表格的脚本编辑器中
2. 运行DeepLAuthKey函数，传入你的DeepL API密钥（在函数参数中填入密钥后执行）
3. 在表格中调用=DeepLTranslate(A1, "JA", "EN")即可将A1单元格的日文翻译成英文

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