Sign in Subscribe

Automate Wikipedia Citation Sourcing with Google Sheets

Michael MacMillan

• 5 min read
Automate Wikipedia Citation Sourcing with Google Sheets

I’ve been dealing with a brand issue. A client I’m working with is a merger of multiple companies, and their homepage is currently outranked by their own press releases announcing the merger. Despite doing everything by the book, the launch underperformed in branded search. The homepage was buried, and press releases were taking over the top spots.

Wikipedia came up during next-step planning. We were tasked with finding reliable press coverage from the green-listed URLs here:
https://en.m.wikipedia.org/wiki/Wikipedia:Reliable_sources/Perennial_sources

You can use the site: operator and go source by source. But where’s the fun in that?

Instead, I built a Google Apps Script that lets you search dozens of trusted sites at once and returns only the URLs that actually mention your brand.

If you're working on a company page for Wikipedia, this tool gives you a repeatable way to surface citable sources, without burning hours running site searches by hand.

Here’s how to set it up.

Automating Searching for Brand Mentions Across Trusted Websites with Google Sheets

What You'll Need:

Step 1: Prepare Your Google Sheet

First, we need to structure a Google Sheet to hold our list of sources, our search term, and the results.

  1. Create a new Google Sheet.

  2. Rename the first sheet (at the bottom of the page) to Sources.

    • In cell A1, type the header: Domain.
    • Starting in cell A2, list the domains of the websites you want the script to search. Here is the list I am working with based WikiPedia's Trusted Sources:
      • abc.go.com
      • abcnews.go.com
      • africa.reuters.com
      • ap.org
      • apnews.com
      • asia.wsj.com
      • bbc.co.uk
      • bbc.com/news
      • bloomberg.ca
      • bloomberg.com
      • bloombergquint.com
      • bnnbloomberg.ca
      • cbc.ca/news
      • cbcnews.ca
      • economist.com
      • espresso.economist.com
      • europe.wsj.com
      • independent.co.uk
      • indy100.com
      • kqed.org
      • npr.org
      • nyt.com
      • nytimes.com
      • projects.propublica.org
      • propublica.org
      • reuters.com
      • theguardian.com
      • theguardian.ng
      • uk.reuters.com
      • wapo.st
      • washingtonpost.com
      • wbur.org
      • wired.co.uk
      • wired.com
      • wsj.com

  3. Create a second sheet by clicking the + icon at the bottom and rename it to Search and Results.

    • In cell A1, type: Enter Brand Name Here:. You will type your search query into cell B1.
    • In cell A3, type the header Source.
    • In cell B3, type the header Found URLs.
    • The script will populate the results below these headers.

Your sheet should now be perfectly set up for the script.

Step 2: Get Your Google API Credentials

To allow our script to perform Google searches, we need to use the Google Custom Search API. This requires two pieces of information: an API Key and a Search Engine ID.

Part A: Get Your API Key

  1. Go to the Google Cloud Console.
  2. Create a new project (or select an existing one). Give it a memorable name like "Brand Searcher".
  3. From the navigation menu (☰), go to APIs & Services > Library.
  4. In the search bar, type Custom Search API and press Enter.
  5. Click on the "Custom Search API" result and click the Enable button.
  6. Once enabled, go to APIs & Services > Credentials.
  7. Click + CREATE CREDENTIALS at the top and select API key.
  8. A box will appear with your new API key. Copy this key and save it somewhere safe for the next step. You can close the dialog box.

Part B: Get Your Search Engine ID (CX)

  1. Go to the Programmable Search Engine control panel.
  2. Click the Add button to create a new search engine.
  3. You can enter a sample website in the "What to search?" box (e.g., nytimes.com). Don't worry, we will override this later.
  4. Give your search engine a name and click Create.
  5. After it's created, click on Edit search engine.
  6. This is a critical step: In the "Basics" tab, make sure the setting for Search the entire web is turned ON. This allows the script to use the site: operator for any domain.
  7. While still in the "Basics" tab, find your Search engine ID and copy it. Keep it with your API key.

You now have the two credentials needed for the script.

Step 3: Create the Google Apps Script

Now it's time to add the automation magic to our spreadsheet.

  1. Go back to your Google Sheet.
  2. Click on Extensions > Apps Script.
  3. This will open a new tab with the script editor. Delete any placeholder code in the Code.gs file.
  4. Copy and paste the entire script below into the editor.
function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Trusted Search')
      .addItem('Search Brand Name', 'searchBrandOnTrustedSources')
      .addToUi();
}

function searchBrandOnTrustedSources() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourcesSheet = ss.getSheetByName('Sources');
  const searchSheet = ss.getSheetByName('Search and Results');

  const brandName = searchSheet.getRange('B1').getValue();
  if (!brandName) {
    SpreadsheetApp.getUi().alert('Please enter a brand name in cell B1 of the "Search and Results" sheet.');
    return;
  }

  // --- IMPORTANT: PASTE YOUR CREDENTIALS BELOW ---
  const apiKey = 'YOUR_API_KEY';
  const searchEngineId = 'YOUR_SEARCH_ENGINE_ID';
  // ------------------------------------------------

  if (apiKey === 'YOUR_API_KEY' || searchEngineId === 'YOUR_SEARCH_ENGINE_ID') {
    SpreadsheetApp.getUi().alert('Please replace "YOUR_API_KEY" and "YOUR_SEARCH_ENGINE_ID" in the script with your actual credentials.');
    return;
  }

  const domains = sourcesSheet.getRange('A2:A' + sourcesSheet.getLastRow()).getValues().flat().filter(String);
  const results = [];

  // Clear previous results before starting the new search
  if (searchSheet.getLastRow() > 3) {
    searchSheet.getRange(4, 1, searchSheet.getLastRow() - 3, 2).clearContent();
  }
  
  SpreadsheetApp.flush(); // Apply the clearing immediately

  for (const domain of domains) {
    const query = `"${brandName}" site:${domain}`;
    const url = `https://www.googleapis.com/customsearch/v1?key=${apiKey}&cx=${searchEngineId}&q=${encodeURIComponent(query)}`;

    try {
      const response = UrlFetchApp.fetch(url, { 'muteHttpExceptions': true }); // Mute exceptions to see error codes
      const responseCode = response.getResponseCode();
      const responseText = response.getContentText();

      if (responseCode === 200) {
        const jsonResponse = JSON.parse(responseText);
        let foundUrls = '';
        if (jsonResponse.items) {
          foundUrls = jsonResponse.items.map(item => item.link).join('\n');
        }
        results.push([domain, foundUrls || 'No results found.']);
      } else {
        // Handle errors, like quota exceeded (429)
        const error = JSON.parse(responseText).error;
        results.push([domain, `Error ${error.code}: ${error.message}`]);
      }
    } catch (e) {
      results.push([domain, `Script Error: ${e.message}`]);
      Logger.log(`Error fetching for ${domain}: ${e.message}`);
    }
    
    // Write results incrementally
    if (results.length > 0) {
      searchSheet.getRange(3 + results.length, 1, 1, 2).setValues([results[results.length-1]]);
      SpreadsheetApp.flush(); // Display result as it comes in
    }
  }
}
  1. Crucially, replace YOUR_API_KEY and YOUR_SEARCH_ENGINE_ID in the script with the actual credentials you copied in Step 2.
  2. Click the Save project icon (looks like a floppy disk).

Your automated search tool is now ready to use.

  1. Return to your Google Sheet tab. You may need to refresh the page.
  2. A new menu item named Trusted Search should now appear in the main menu.
  3. Go to the Search and Results sheet and type a brand or topic into cell B1.
  4. Click Trusted Search > Search Brand Name.
  5. Authorization: The first time you run it, Google will ask for permission for the script to manage your spreadsheets and connect to external services. Click Continue and follow the prompts to allow access.
  6. The script will now run. You will see the results appearing row by row in the sheet, showing the source domain and any URLs that were found.

Troubleshooting Common Issues

If you run into problems, it's usually for one of two reasons:

  • You get an error like "Error 400: Invalid Value" or "Error 403: Permission Denied": This almost always means there is an issue with your API Key or Search Engine ID. Double-check that you have pasted them correctly into the script and that the Custom Search API is enabled.
  • You get an error like "Error 429: Quota exceeded": The Google Custom Search API has a free tier of 100 searches per day. Your script uses one query for every domain in your list. If you have 15 domains, you can run the full search about 6 times before hitting the limit. The quota resets every day at midnight Pacific Time. To perform more searches, you will need to enable billing on your Google Cloud project.

I’ll keep sharing tools like this, small systems that make strategic SEO work faster. If you build on this one, let me know.

Sign up for more like this.

Enter your email
Subscribe