解决Google Apps Script调用Search Console API的403权限错误

我正在做一个个人项目，用Google Apps Script把Google Search Console数据同步到Google表格，但一直遇到403权限错误，反复检查调整都没解决，求帮助。我用了OAuth2库，但不确定配置是否正确。

错误信息 #

Exception when calling the API: Exception: Request failed for https://www.googleapis.com returned code 403. Truncated server response: { "error": { "code": 403, "message": "User does not have sufficient permission for site 'http://example.com/'. See also: https://s... (use muteHttpExceptions option to examine full response)

Apps Script代码 #

// Replace with your own credentials and website URL
var CLIENT_ID = '...';
var CLIENT_SECRET = '...';
var WEBSITE_URL = 'example.com';

/**
 * Configures the OAuth2 service.
 */
function getService() {
  return OAuth2.createService('google')
    .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
    .setTokenUrl('https://accounts.google.com/o/oauth2/token')
    .setClientId(CLIENT_ID)
    .setClientSecret(CLIENT_SECRET)
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties())
    .setScope([
      'https://www.googleapis.com/auth/script.external_request',
      'https://www.googleapis.com/auth/webmasters.readonly',
      'https://www.googleapis.com/auth/spreadsheets',
      'https://www.googleapis.com/auth/script.container.ui',
      'https://www.googleapis.com/auth/script.scriptapp'
    ]);
}


/**
 * Handles the OAuth callback.
 */
function authCallback(request) {
  var service = getService();
  var authorized = service.handleCallback(request);
  if (authorized) {
    return HtmlService.createHtmlOutput('Success! You can now close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Access Denied. You can close this tab');
  }
}

/**
 * Simplified check for script authorization. Directs users more clearly on how to authorize.
 */
function checkAuthorization() {
  var service = getService();
  if (!service.hasAccess()) {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Authorize the script by visiting this url: ' + authorizationUrl);
    SpreadsheetApp.getUi().alert('Please check the logs for the authorization URL. Visit it and re-run the desired action.');
  } else {
    Logger.log('The script is already authorized.');
    SpreadsheetApp.getUi().alert('The script is already authorized.');
  }
}

/**
 * Adds a custom menu to the Google Sheets UI.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('GSC Data Tools')
    .addItem('Authorize', 'checkAuthorization')
    .addItem('Fetch GSC Data', 'getGSCData') // Added menu item for fetching data
    .addToUi();
  createTimeDrivenTriggers(); // Ensure triggers are set up
}

/**
 * Function to fetch data from Google Search Console and update the sheet.
 */
function getGSCData() {
  var today = new Date();
  var oneWeekAgo = new Date(today.getFullYear(), today.getMonth(), today.getDate() - 7);
  var startDate = formatDate(oneWeekAgo);
  var endDate = formatDate(today);

  var service = getService();
  if (service.hasAccess()) {
    var url = 'https://www.googleapis.com/webmasters/v3/sites/' + encodeURIComponent(WEBSITE_URL) + '/searchAnalytics/query';
    var options = {
      method: 'post',
      contentType: 'application/json',
      headers: { Authorization: 'Bearer ' + service.getAccessToken() },
      payload: JSON.stringify({ startDate: startDate, endDate: endDate, dimensions: ['query'], rowLimit: 1000 }),
      muteHttpExceptions: false // Changed to false to enable error logging
    };

    try {
      var response = UrlFetchApp.fetch(url, options);
      if (response.getResponseCode() == 200) {
        var result = JSON.parse(response.getContentText());
        var rows = result.rows;

        if (rows && rows.length > 0) {
          updateSheetWithGSCData(rows);
        } else {
          Logger.log('No data returned for the specified period.');
          SpreadsheetApp.getUi().alert('No data returned for the specified period.');
        }
      } else {
        Logger.log('Error fetching data: ' + response.getResponseCode());
        SpreadsheetApp.getUi().alert('Error fetching data. Please check the logs.');
      }
    } catch (e) {
      Logger.log('Exception when calling the API: ' + e.toString());
      SpreadsheetApp.getUi().alert('Exception when calling the API. Please check the logs.');
    }
  } else {
    Logger.log('No access to the service. Please re-authorize.');
    SpreadsheetApp.getUi().alert('No access to the service. Please re-authorize through the custom menu.');
  }
}

/**
 * Updates the Google Sheet with data fetched from Google Search Console.
 */
function updateSheetWithGSCData(rows) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.insertRowBefore(2); // Always insert a new row for new data

  // Headers
  sheet.getRange('A1').setValue('Keyword');
  sheet.getRange('B1').setValue('Impressions');
  sheet.getRange('C1').setValue('Clicks');
  sheet.getRange('D1').setValue('Position');

  // Data
  var startingRow = 2;
  rows.forEach(function(row, index) {
    var currentRow = sheet.getRange('A' + (index + startingRow) + ':D' + (index + startingRow));
    currentRow.setValues([[row.keys[0], row.impressions, row.clicks, row.position]]);
  });
}

/**
 * Utility function to format dates.
 */
function formatDate(date) {
  if (!date || !(date instanceof Date) || isNaN(date.getTime())) {
    console.log("Invalid or undefined date provided to formatDate");
    date = new Date(); // Use current date as a fallback
  }
  var month = date.getMonth() + 1;
  var day = date.getDate();
  month = month < 10 ? '0' + month : month;
  day = day < 10 ? '0' + day : day;
  return date.getFullYear() + '-' + month + '-' + day;
}

/**
 * Sets up time-driven triggers to run the data fetch function automatically.
 */
function createTimeDrivenTriggers() {
  // Deletes any existing triggers for the function
  var existingTriggers = ScriptApp.getProjectTriggers().filter(function(trigger) {
    return trigger.getHandlerFunction() === 'getGSCData';
  });
  existingTriggers.forEach(function(trigger) {
    ScriptApp.deleteTrigger(trigger);
  });

  // Creates a new trigger for every 7 days
  ScriptApp.newTrigger('getGSCData')
    .timeBased()
    .everyDays(7)
    .create();
}

/**
 * Logs the redirect URI for the OAuth2 service.
 */
function logRedirectUri() {
  var service = getService();
  Logger.log(service.getRedirectUri());
}

已尝试的排查步骤 #

• 确认域名example.com已在Google Search Console中验证，且我拥有所有者权限。
• 确认Google Search Console API已在Google Cloud Console中启用。
• 确认OAuth consent screen配置正确且已发布，请求了正确的权限范围（包含部分敏感范围），当前处于测试状态，外部用户类型，我的邮箱已列入测试用户。
• 验证脚本中的CLIENT_ID和CLIENT_SECRET与Google Cloud Console中的一致。
• 已将所有必要的权限范围添加到脚本和appsscript.json清单文件中。
• 创建并验证了OAuth流程的重定向URI。
• 排查了多个OAuth 2.0客户端ID可能导致的问题，确保配置一致。

疑问 #

• 通过Apps Script访问Google Search Console数据，除文档说明外是否有其他已知问题或额外权限要求？
• 我的OAuth2库实现是否需要检查？
• 错误是否与Google Search Console中的属性验证方式（域名类型vs前缀类型）有关？我注意到错误中显示的是http://example.com/，而脚本中是example.com。
• 是否有人解决过类似的权限问题，或能发现我配置中的潜在错误？

恳请提供指导、见解或建议，帮助我解决该问题，实现将Google Search Console数据正确获取到Google表格中。感谢您的帮助！

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