Google Sheets调用Google Ads API脚本报404错误，如何修复？

问题描述 #

我配置了一段Google Apps Script用于从Google Ads API获取关键词创意，但调用时出现404错误。脚本代码如下：

const scriptProperties = PropertiesService.getScriptProperties();
function GIdea(keywords = "Demo") {
    var accessToken = getOAuthToken(); 
    var CLIENTID = scriptProperties.getProperty('CLIENTID');
    var endpoint = 'https://googleads.googleapis.com/v10/customers/' + CLIENTID +'/keywordPlans:generateKeywordIdeas';
    var requestBody = {
      "customerId": CLIENTID,
      "language": "ENGLISH",
      "geo_target_constants": [],
      "include_adult_keywords": false,
      "page_size": 10,
      "keyword_plan_network": "GOOGLE_SEARCH_AND_PARTNERS",
      "keyword_seed": keywords
    };
    var options = {
      'method': 'post',
      'contentType': 'application/json',
      'headers': {
        'Authorization': 'Bearer ' + accessToken
      },
      'payload': JSON.stringify(requestBody)
    };
    var response = UrlFetchApp.fetch(endpoint, options);
    var ideas = JSON.parse(response.getContentText());
    return ideas;
}
function getOAuthToken() {
  var CLIENTID = scriptProperties.getProperty('CLIENTID');
  var SECRETFORADS = scriptProperties.getProperty('SECRETFORADS');
  var service = OAuth2.createService('GoogleAds')
    .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
    .setTokenUrl('https://accounts.google.com/o/oauth2/token')
    .setClientId(CLIENTID)
    .setClientSecret(SECRETFORADS)
    .setScope('https://www.googleapis.com/auth/adwords');
  if (!service.hasAccess()) {
    return 'No access to Google Ads API';
  }
  var url = API_URL;
  var headers = {
    "Authorization": "Bearer " + service.getAccessToken(),
    "Content-Type": "application/json",
    "muteHttpExceptions": false
  };
  var options = {
    "method": "get",
    "headers": headers,
  };
  var response = UrlFetchApp.fetch(url, options);
  var jsonResponse = JSON.parse(response.getContentText());
  return jsonResponse
}

错误信息：

Exception: Request failed for https://googleads.googleapis.com returned code 404. Truncated server response: <titl... (use muteHttpExceptions option to examine full response) (line 70).

在Google Sheets单元格调用GIdea函数时收到上述404错误，无法获取预期结果。

问题分析与修复步骤 #

1. 修复getOAuthToken函数的核心错误 #

这个函数存在两个致命问题：

• 未定义API_URL变量：代码中直接使用var url = API_URL;，但API_URL从未声明赋值，导致请求无效地址触发404。
• 错误返回值：GIdea需要的是OAuth访问令牌字符串，但函数返回的是某个API的JSON响应体，完全不符合要求。

修复后的getOAuthToken：

function getOAuthToken() {
  var CLIENTID = scriptProperties.getProperty('CLIENTID');
  var SECRETFORADS = scriptProperties.getProperty('SECRETFORADS');
  var service = OAuth2.createService('GoogleAds')
    .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
    .setTokenUrl('https://accounts.google.com/o/oauth2/token')
    .setClientId(CLIENTID)
    .setClientSecret(SECRETFORADS)
    .setScope('https://www.googleapis.com/auth/adwords')
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties());
  
  if (!service.hasAccess()) {
    var authUrl = service.getAuthorizationUrl();
    throw new Error(`请访问此链接授权：${authUrl}`);
  }
  
  // 直接返回访问令牌字符串
  return service.getAccessToken();
}

// 必须添加授权回调函数
function authCallback(request) {
  var service = OAuth2.createService('GoogleAds')
    .setClientId(scriptProperties.getProperty('CLIENTID'))
    .setClientSecret(scriptProperties.getProperty('SECRETFORADS'))
    .setScope('https://www.googleapis.com/auth/adwords')
    .setPropertyStore(PropertiesService.getUserProperties());
  
  var isAuthorized = service.handleCallback(request);
  return HtmlService.createHtmlOutput(isAuthorized ? '授权成功，可关闭页面。' : '授权失败，请重试。');
}

2. 修正请求体格式 #

Google Ads API的keywordSeed字段要求是对象格式，且字段名需用驼峰式，原代码的写法不符合API规范：

var requestBody = {
  "customerId": CLIENTID.replace(/-/g, ''), // 移除客户ID中的连字符
  "language": "en", // 使用ISO 639-1语言代码，而非全称
  "geoTargetConstants": [],
  "includeAdultKeywords": false,
  "pageSize": 10,
  "keywordPlanNetwork": "GOOGLE_SEARCH_AND_PARTNERS",
  "keywordSeed": {
    "keywords": [keywords] // 传入数组格式的关键词
  }
};

3. 完善请求选项与端点验证 #

• 客户ID必须是不带连字符的纯数字，否则端点URL无效。
• 添加developer-token请求头（必填参数，需在Script Properties中配置DEVELOPER_TOKEN）。
• 启用muteHttpExceptions以便查看完整错误响应：

var options = {
  'method': 'post',
  'contentType': 'application/json',
  'headers': {
    'Authorization': 'Bearer ' + accessToken,
    'developer-token': scriptProperties.getProperty('DEVELOPER_TOKEN')
  },
  'payload': JSON.stringify(requestBody),
  'muteHttpExceptions': true
};

4. 基础配置验证 #

• 确保Google Cloud项目已启用Google Ads API。
• 确认OAuth客户端ID的权限配置正确，且已完成授权流程。
• 检查客户ID对应的Google Ads账号已链接到Cloud项目，API访问权限已开启。

完整修正后的代码 #

const scriptProperties = PropertiesService.getScriptProperties();

function GIdea(keywords = "Demo") {
  try {
    var accessToken = getOAuthToken(); 
    var CLIENTID = scriptProperties.getProperty('CLIENTID').replace(/-/g, '');
    var endpoint = `https://googleads.googleapis.com/v10/customers/${CLIENTID}/keywordPlans:generateKeywordIdeas`;
    
    var requestBody = {
      "customerId": CLIENTID,
      "language": "en",
      "geoTargetConstants": [],
      "includeAdultKeywords": false,
      "pageSize": 10,
      "keywordPlanNetwork": "GOOGLE_SEARCH_AND_PARTNERS",
      "keywordSeed": {
        "keywords": [keywords]
      }
    };
    
    var options = {
      'method': 'post',
      'contentType': 'application/json',
      'headers': {
        'Authorization': 'Bearer ' + accessToken,
        'developer-token': scriptProperties.getProperty('DEVELOPER_TOKEN')
      },
      'payload': JSON.stringify(requestBody),
      'muteHttpExceptions': true
    };
    
    var response = UrlFetchApp.fetch(endpoint, options);
    var responseCode = response.getResponseCode();
    
    if (responseCode !== 200) {
      var errorText = response.getContentText();
      throw new Error(`请求失败，状态码：${responseCode}，错误信息：${errorText}`);
    }
    
    var ideas = JSON.parse(response.getContentText());
    return ideas;
  } catch (error) {
    return `错误：${error.message}`;
  }
}

function getOAuthToken() {
  var CLIENTID = scriptProperties.getProperty('CLIENTID');
  var SECRETFORADS = scriptProperties.getProperty('SECRETFORADS');
  
  var service = OAuth2.createService('GoogleAds')
    .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
    .setTokenUrl('https://accounts.google.com/o/oauth2/token')
    .setClientId(CLIENTID)
    .setClientSecret(SECRETFORADS)
    .setScope('https://www.googleapis.com/auth/adwords')
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties());
  
  if (!service.hasAccess()) {
    var authUrl = service.getAuthorizationUrl();
    throw new Error(`请访问此链接授权：${authUrl}`);
  }
  
  return service.getAccessToken();
}

function authCallback(request) {
  var service = OAuth2.createService('GoogleAds')
    .setClientId(scriptProperties.getProperty('CLIENTID'))
    .setClientSecret(scriptProperties.getProperty('SECRETFORADS'))
    .setScope('https://www.googleapis.com/auth/adwords')
    .setPropertyStore(PropertiesService.getUserProperties());
  
  var isAuthorized = service.handleCallback(request);
  return HtmlService.createHtmlOutput(isAuthorized ? '授权成功，可关闭页面。' : '授权失败，请重试。');
}

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