CODEWITHSUNDEEP
google-apps-scriptoauth-2.0google-search-consoleuser-permissions
Zak
Zak

Reputation: 1

Resolving 403 Permission Error with Google Search Console API via Google Apps Script

I've been working on integrating Google Search Console data into a Google Sheet via Google Apps Script for a personal project. I'm reletively new to this so trying to understand it all. I've hit a roadblock with a persistent 403 permission error. Despite thorough checks and adjustments, the issue persists, and I'm reaching out for insights or suggestions that could help resolve it.

I've attempted to connect it to this Oauth2 library but not 100% sure this is connected properly

Error Message:

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 Code

// 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());
}

What I've Tried:

  1. Confirmed that the domain example.com is verified in my Google Search Console and that I have owner-level access.

  2. Checked that the Google Search Console API is enabled in the Google Cloud Console.

  3. Ensured the OAuth consent screen is configured correctly and published, with the correct scopes requested (however I do have a few sensitive scopes). It's in testing status, external user type, my email is listed in the test users.

  4. Verified that the CLIENT_ID and CLIENT_SECRET in my script match those in the Google Cloud Console.

  5. Added all necessary scopes to my script and the appsscript.json manifest file.

  6. Created and verified the redirect URI for the OAuth flow.

  7. Explored potential issues with having multiple OAuth 2.0 Client IDs and ensured consistency across configurations.

Questions:

Any guidance, insights, or suggestions would be greatly appreciated as I aim to resolve this and correctly fetch data from Google Search Console into Google Sheets.

Thank you in advance!

Upvotes: 0

Views: 289

Answers (0)

Related Questions