What we're going to end up with

This Google Sheets tool uses Apps Scripting to combine the Google Maps and Mozscape APIs into an all-in-one link finder.  Type in a search term and a lat/long, and the tool will find the top Google maps results. Once we have those results, we can discover who is linking to them, and produce a "link intersect" report.

What you'll need to follow along

  • Basic understanding of Google Sheets
  • Google Maps API Key
  • Mozscape API Key

Watch the video if you want to see it in action:

Follow this link to make your own copy of the spreadsheet.

Make a copy!

Getting your Google Maps API Key

You can visit the Google Cloud Console getting started page to set up an account. They'll require you to enter billing details, but the Maps API starts off with a $200 credit (at least at the time of this writing). You would have to use the API pretty heavily to exhaust the credit in a short time frame.

Once your account is set up, you can head to the credentials page that you see in the screenshot above and generate an API key.

Getting your Mozscape API Key

Head to the Moz pricing page to get set up with the Mozscape API. This is a fairly pricey API, but worth it if you're operating at scale as an agency.  Once your account is confirmed, copy the credentials out of the Access page and into your copy of the spreadsheet.

Enter your API keys and search details

You'll need to add your Google Maps and Mozscape API keys before trying to run the tool.  The API keys should be entered on the "API Keys" sheet, on rows 1 through 3.  Because you're working with your own copy of the sheet, your keys will remain private.

Add latitude, longitude, and a search term, and you should be ready to start scanning for links.

Authorize your Copy of the Spreadsheet

Because the spreadsheet uses Apps Script and makes requests to external services, Google will require you to give authorization and present you with a "buyer beware" type warning.

Once you've authorized the script, the next popup will require an additional authorization under the "Advanced" section.

Google makes this warning so pronounced because Apps scripts have a lot of power and flexibility, but that also comes with risks.  The code for this project is small enough that you can read over it, and see for yourself that it isn't doing anything nefarious.

I've included the code here at the bottom, but you can also read it within the Script Editor (available under the Tools menu).

Run the Gather Places Function

Clicking the Gather Places button will start a Google search centered around the latitude and longitude you entered.  Each search pass will collect up to 20 links, but you can run it as many times as you like to bring in more results.

Once you're happy with the number of search results, press the Gather Links button to scan for inbound links to those businesses.  The Mozscape Rows parameter controls how many links are presented for each original search result (but can only go up to 50).

You'll only want to click Gather Links once, unless you reset the spreadsheet.  Clicking it twice will just duplicate the original results.

Run the Analyze Function

Finally, run Analyze to aggregate the results.  This is where you can spot trends in a local market and identify the links you might be missing out on.

Apps Script Code

Google Apps Script is what makes it possible for this tool to communicate with Google Maps and the Mozscape API.  You can read the code any time you'd like by going to the Tools menu and clicking Script Editor, but I'll include the code here as well.  

I'd always recommend reading over any Apps Script that you're allowing to run under your account, just to be sure it isn't doing anything nefarious.

function placeSearch(apiKey, nextToken, searchQuery, searchRadius, lat, lng) {
  var searchApi = "https://maps.googleapis.com/maps/api/place/nearbysearch/json";
  
  searchApi += `?key=${encodeURIComponent(apiKey)}&`;
  searchApi += `keyword=${encodeURIComponent(searchQuery)}&`;
  searchApi += `location=${encodeURIComponent(lat + "," + lng)}&`;
  searchApi += `radius=${searchRadius}`;
  
  if (nextToken) {
    searchApi += `&pagetoken=${encodeURIComponent(nextToken)}`;
  }
  
  var response = UrlFetchApp.fetch(searchApi, {'muteHttpExceptions': true});
  return JSON.parse(response.getContentText());
}

function placeDetail(apiKey, placeId) {
  var detailApi = "https://maps.googleapis.com/maps/api/place/details/json";
  
  detailApi += `?key=${encodeURIComponent(apiKey)}&`;
  detailApi += `place_id=${encodeURIComponent(placeId)}&`;
  detailApi += `fields=website`;
  
  var response = UrlFetchApp.fetch(detailApi, {'muteHttpExceptions': true});
  return JSON.parse(response.getContentText()).result;
}

function reset() {
  var apiSheet = SpreadsheetApp.getActive().getSheetByName("API Keys");
  var locationSheet = SpreadsheetApp.getActive().getSheetByName("Locations");
  var linksSheet = SpreadsheetApp.getActive().getSheetByName("Links");
  var interSheet = SpreadsheetApp.getActive().getSheetByName("Link Intersect");
  
  locationSheet.getRange(2, 1, locationSheet.getLastRow(), locationSheet.getLastColumn()).clear();
  linksSheet.getRange(2, 1, linksSheet.getLastRow(), linksSheet.getLastColumn()).clear();
  interSheet.getRange(2, 1, interSheet.getLastRow(), interSheet.getLastColumn()).clear();
  
  apiSheet.getRange("B9").setValue("");
}

function analyze() {
  var linksSheet = SpreadsheetApp.getActive().getSheetByName("Links");
  var interSheet = SpreadsheetApp.getActive().getSheetByName("Link Intersect");

  var data = linksSheet.getDataRange().getValues();
  var hits = {};
  for (var i = 1; i < data.length; i++) {
    var domain = data[i][1];
    if (hits[domain]) {
      hits[domain] += 1;  
    } else {
      hits[domain] = 1;
    }    
  }
  
  var dataRange = interSheet.getRange(2, 1, Object.keys(hits).length, interSheet.getLastColumn());
  var dataValues = [];
  for (var domain of Object.keys(hits)) {
    dataValues.push([domain, hits[domain]]);
  }
  dataRange.setValues(dataValues);
  dataRange.sort({column: 2, ascending: false});
}

function gatherLinks() {
  var mozApi = "https://lsapi.seomoz.com/v2/links/";
  
  var apiSheet = SpreadsheetApp.getActive().getSheetByName("API Keys");
  var locationSheet = SpreadsheetApp.getActive().getSheetByName("Locations");
  var linksSheet = SpreadsheetApp.getActive().getSheetByName("Links");
  
  var data = apiSheet.getDataRange().getValues();
  var mozApiKey = data[1][1];
  var mozSecret = data[2][1];
  var mozRows = data[3][1];

  var authHeader = "Basic " + Utilities.base64Encode(mozApiKey + ':' + mozSecret);
  var options = {
    headers: {Authorization: authHeader},
    method: "post",
    contentType: "application/json",
  };
  
  var data = locationSheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    options.payload = JSON.stringify({target: data[i][1], filter: "external", source_scope: "root_domain", limit: mozRows});
    
    var response = UrlFetchApp.fetch(mozApi, options);
    var responseData = JSON.parse(response.getContentText());
    var dataValues = [];
    for (var j = 0; j < responseData.results.length; j++) {
      dataValues.push([
        data[i][0],
        responseData.results[j].source.root_domain,
        responseData.results[j].source.page,
        responseData.results[j].source.domain_authority,
        responseData.results[j].source.page_authority
      ]);
    }

    if (!dataValues.length) {
      continue; 
    }
    
    linksSheet.insertRowsAfter(linksSheet.getLastRow(), dataValues.length);
    
    var newDataRange = linksSheet.getRange(
      linksSheet.getLastRow() + 1, 
      1, 
      dataValues.length, 
      linksSheet.getLastColumn()
    );
    
    newDataRange.setValues(dataValues);
  }
}

function gatherPlaces() {
  var apiSheet = SpreadsheetApp.getActive().getSheetByName("API Keys");
  var locationSheet = SpreadsheetApp.getActive().getSheetByName("Locations");
  
  var data = apiSheet.getDataRange().getValues();
  
  var mapsApiKey = data[0][1];
  var lat = data[4][1];
  var lng = data[5][1];
  var searchRadius = data[6][1];
  var searchQuery = data[7][1];
  var nextToken = data[8][1];
  
  var responseData = placeSearch(mapsApiKey, nextToken, searchQuery, searchRadius, lat, lng);
  apiSheet.getRange("B9").setValue(responseData.next_page_token);
  
  for (var place of responseData.results) {
      var detail = placeDetail(mapsApiKey, place.place_id);
      if (!detail.website) {
        continue; 
      }
    
      locationSheet.appendRow([place.name, detail.website]);
  }
}

I hope this project can give your link building efforts a boost.  If you have any feedback or suggestions, please reach out to me on Twitter at @zchtodd.