How to create a Google Sheet Sitemap Reader

To set up your Google Sheet and script with Google Search Console (GSC) integration, here’s a comprehensive guide, including steps for configuring Google Cloud Console. Let’s go through each step carefully:

 

Step 1: Set Up Your Google Cloud Console Project

  1. Sign in to Google Cloud Console: Go to Google Cloud Console and log in with your Google account.
  2. Create a New Project:
    • Click on the project dropdown at the top of the page and select “New Project.”
    • Name your project, select your organization (if applicable), and click “Create.”
  3. Enable the Google Search Console API:
    • In the left-hand menu, navigate to APIs & Services > Library.
    • Search for “Google Search Console API” and select it.
    • Click “Enable” to activate the API for your project.
    • Do the Same for “Google Sheets API”

 

Step 2: Set Up OAuth Credentials

  1. Go to Credentials: Under APIs & Services, select Credentials from the left menu.
  2. Create OAuth Client ID:
    • Click + Create Credentials and choose OAuth client ID.
    • If prompted, configure the consent screen by filling out necessary details (e.g., app name and email). Save and proceed.
    • Select “Application type” as Web application if you intend to use it with App Scripts.
    • Give it a name and click Create. Your Client ID and Client Secret will be generated.
  3. Authorise the Script to Access Your Google Account:
    • Under Authorised redirect URIs, add https://script.google.com/macros/d/{SCRIPT_ID}/usercallback (replace {SCRIPT_ID} with your script’s ID if running on Google Apps Script).
  4. Go to ‘ OAuth consent screen ’ and add the relevant email address to ‘test users’

 

Step 3: Set Up Google Apps Script in Google Sheets

  1. Open Google Sheets: Create or open a Google Sheet where you want to run the script.
  2. Access Google Apps Script:
    • In Google Sheets, go to Extensions > Apps Script.
  3. Add Your Script: 
    function testGSCAPI() {
  const url = '<https://alexanderhousecare.com/sitemap.xml>';
  const response = UrlFetchApp.fetch(`https://www.googleapis.com/webmasters/v3/sites/${encodeURIComponent(url)}/sitemaps`, {
    method: 'get',
    headers: {
      Authorization: `Bearer ${ScriptApp.getOAuthToken()}`,
    },
    muteHttpExceptions: true
  });
  
  Logger.log(response.getContentText());
}

function getGSCData() {
  // Log the active user for verification
  Logger.log(Session.getActiveUser().getEmail());
  
  // Get the active spreadsheet and select the first sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CCG');
  
  // Get all URLs from column A (assuming data starts at row 2)
  const urlsRange = sheet.getRange('A2:A51');  // Adjust range as needed
  const urls = urlsRange.getValues();  // Get URLs in column A
  
  // Clear previous data in columns B, C, D
  sheet.getRange('B2:D51').clearContent();
  
  // Loop through each URL in column A
  urls.forEach(function(row, i) {
    const url = row[0];  // URL from column A
    if (url) {
      try {
        // Make a request to the GSC API for each URL's sitemap details
        const response = UrlFetchApp.fetch(`https://www.googleapis.com/webmasters/v3/sites/${encodeURIComponent(url)}/sitemaps`, {
          method: 'get',
          headers: {
            Authorization: `Bearer ${ScriptApp.getOAuthToken()}`,  // OAuth token automatically handled
          },
          muteHttpExceptions: true  // Mute exceptions to handle errors gracefully
        });
        
        // Parse the API response
        const data = JSON.parse(response.getContentText());

        // Check if the request was successful (status code 200)
        if (response.getResponseCode() === 200) {
          const sitemap = data.sitemap[0]; // Assuming we want the first sitemap
          const initialSubmitted = sitemap.lastSubmitted || "N/A";  // Date the sitemap was initially submitted
          const lastCrawled = sitemap.lastDownloaded || "N/A";  // Date the sitemap was last crawled
          const pagesFound = sitemap.contents ? sitemap.contents[0].indexed : "N/A";  // Number of pages found

          // Write the results back to the sheet in columns B, C, and D
          sheet.getRange(i + 2, 2).setValue(initialSubmitted);  // Column B: Sitemap submitted date
          sheet.getRange(i + 2, 3).setValue(lastCrawled);  // Column C: Last crawled date
          sheet.getRange(i + 2, 4).setValue(pagesFound);  // Column D: Pages found
        } else {
          // Log the error or write it to the sheet for debugging
          Logger.log(`Error fetching data for ${url}: ${response.getContentText()}`);
          sheet.getRange(i + 2, 2, 1, 3).setValue("Error fetching data");  // Indicate the error in the sheet
        }
        
      } catch (error) {
        Logger.log('Error fetching sitemap data for ' + url + ': ' + error);
        sheet.getRange(i + 2, 2, 1, 3).setValue("Error fetching data");  // Indicate the error in the sheet
      }
    }
  });
}
  4. Go to project Settings and tick ‘Show “appsscript.json” manifest file in editor’. Now go to ‘Editor’ and enter the below code. 
    {
  "timeZone": "Europe/London",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "<https://www.googleapis.com/auth/webmasters.readonly>",
    "<https://www.googleapis.com/auth/spreadsheets>",
    "<https://www.googleapis.com/auth/script.external_request>",
    "<https://www.googleapis.com/auth/userinfo.email>"
  ]
}
  5. Go to settings and provide ‘Google Cloud Platform (GCP) Project’ project number

 

Step 5: Authorisation

  1. Run Your Script: Run your script for the first time by clicking the play button in Google Apps Script.
  2. Authorise Access:
    • You’ll be prompted to review permissions.
    • Since this project is new, Google may show a warning about the app being unverified. Click on “Advanced” and proceed by selecting Go to [Project Name] (unsafe).
  3. Grant Permissions: Approve the permissions required for your script to access your Google Search Console data and your spreadsheet.

 

Step 6: Testing the Script

  1. Run the Script Again:
    • Go back to Google Apps Script and run the script. It should log the active user, pull data from column A, clear columns B-D, and populate sitemap details.
  2. Verify Output:
    • Check columns B-D in your Google Sheet for data like “Sitemap submitted date,” “Last crawled date,” and “Pages found.”

 

Additional Notes

  • Error Handling: The script includes error handling. If there are issues with a particular URL, the script will log the error and populate the sheet with “Error fetching data.”
  • API Limits: Be mindful of Google Search Console API limits. If you experience quota issues, try reducing the number of URLs or adding error handling with exponential backoff for rate limiting.

With these steps, you should be able to set up the script from scratch, configure Google Cloud Console, and enable OAuth authorisation for data fetching. Let me know if you need any clarifications!

Other articles you may be interested in.