How to Validate Doctor Data from a Google Spreadsheet

by Jake Tunney, 9-1-2021

Google Sheets comes with a JavaScript-based language called Apps Script. In this tutorial, we’ll use Apps Script to validate provider NPIs, names, and addresses stored in a Google spreadsheet. This is a quick way to get started using the Convergent provider data validating API, even if you’re not a programmer yourself.

The Convergent API Parts

You’ll need a free Convergent API key. If you don’t have one, sign up for an account here. You can do everything in this tutorial with a trial account, though you’ll be restricted to 5,000 validated providers in the first month.

Once you have a Convergent API key, head over to your Google Drive and create a spreadsheet.

The Google Parts

After creating a new Google sheet, create column headings for:

NPI - If you don’t have NPIs handy, this could be an issue, we’ll cover in a future tutorial how to lookup NPIs using just a provider’s name and state
NPI Match - Is this a valid NPI - you’ll get this in our API response
Provider First Name
Provider Last Name
Name Match - Does the provider’s name match the provider’s NPI?
Source Practice Address - The provider’s primary practice address
Address Match - Is the provider’s address validated?

Once you’ve done that, add a couple of rows of provider data. Your sheet should look like this:

With our data in place, we’re ready to write some code. Click Tools -> Script Editor.

The rest of this post walks you through the code to validate provider data from the spreadsheet. If you’d like to skip to the end and just grab the code, here’s the final product (take note of the placeholders for API Key):

If you want to see how we arrived at that result, we’ll walk you through it in the rest of the post.

function validateDocs() {

  var options = {
      muteHttpExceptions: true,
      'method' : 'get',
      'contentType': 'application/json',
      'headers' : {'Api-Key':'YOUR-API-KEY'}
    };

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var theSheet = activeSheet.getSheetByName("Sheet1"); 
  var NumberofColumns = theSheet.getMaxColumns();   
  var numRows = theSheet.getLastRow()-1;
  var startRow = 2;
  var dataRange = theSheet.getRange(startRow,1, numRows, NumberofColumns);
  var data = dataRange.getValues();

  for (i in data) {
    let row = data[i];
    let rowNum = parseInt(i) + startRow;
    
    // Convergent NPI Match
    let npi = encodeURIComponent(row[0]);
    let npiMatch = theSheet.getRange("B" + rowNum);

    let url1 = 'https://api.convergent-pd.com/provider/searchSingleBest?NPI=' + npi;
    var convergentResponse = UrlFetchApp.fetch(url1, options);

    try {
      let code = convergentResponse.getResponseCode();
      let contentText = convergentResponse.getContentText();

      if (code == 404) {
        npiMatch.setValue("NO MATCH");
      } else if (code == 400 && contentText == "Search returned more than one result.") {
        npiMatch.setValue("MORE THAN 1");
      } else {
        var jsonData = JSON.parse(convergentResponse);
        let response = !!jsonData.id;

        if (response) {
          npiMatch.setValue("MATCH");
          //populateConvergentFields(theSheet, rowNum, jsonData);
        } else {
          npiMatch.setValue("ERROR");
        }
      }
    }
    catch (err) {
      console.error(err);
      return;
    }
    
    // Convergent NPI and Name Match
    let firstName = encodeURIComponent(row[2]);
    let lastName = encodeURIComponent(row[3]);
    let npiAndNameMatch = theSheet.getRange("E" + rowNum);

    let url2 = 'https://api.convergent-pd.com/provider/searchSingleBest?NPI=' + npi + '&firstname=' + firstName + '&lastname=' + lastName;
    var convergentResponse = UrlFetchApp.fetch(url2, options);

    try {
      let code = convergentResponse.getResponseCode();
      let contentText = convergentResponse.getContentText();

      if (code == 404) {
        npiAndNameMatch.setValue("NO MATCH");
      } else if (code == 400 && contentText == "Search returned more than one result.") {
        npiAndNameMatch.setValue("MORE THAN 1");
      } else {
        var jsonData = JSON.parse(convergentResponse);
        let response = !!jsonData.id;

        if (response) {
          npiAndNameMatch.setValue("MATCH");
          //populateConvergentFields(theSheet, rowNum, jsonData);
        } else {
          npiAndNameMatch.setValue("ERROR");
        }
      }
    }
    catch (err) {
      console.error(err);
      return;
    }

    // Convergent NPI & Address Match
    let sourceaddress = encodeURIComponent(row[5]);
    let npiAndAddressMatch = theSheet.getRange("G" + rowNum);

    let url3 = 'https://api.convergent-pd.com/provider/searchSingleBest?NPI=' + npi + '&address=' + sourceaddress;
    var convergentResponse = UrlFetchApp.fetch(url3, options);  

    try {
      let code = convergentResponse.getResponseCode();
      let contentText = convergentResponse.getContentText();

      if (code == 404) {
        npiAndAddressMatch.setValue("NO MATCH");
      } else if (code == 400 && contentText == "Search returned more than one result.") {
        npiAndAddressMatch.setValue("MORE THAN 1");
      } else {
        var jsonData = JSON.parse(convergentResponse);
        let response = !!jsonData.id;

        if (response) {
          npiAndAddressMatch.setValue("MATCH");
        } else {
          npiAndAddressMatch.setValue("ERROR");
        }
      }
    }
    catch (err) {
      console.error(err);
      return;
    }
  }
}

Validate a Doc from App Script

We’ll write this code in a couple of steps:

Set up our API
Grab the data in our spreadsheet
Loop over each doctor and call the Convergent API for each element you need to validate
Return that data and print the results back to the spreadsheet

First, let’s set up your function with some basics. Add these first lines of code in the app script editor. The “options” variable is what you’ll need to be able to call the Convergent API. Once you’ve registered for your account, add the API key. The second set of variables starting at “activeSheet” grab the data in our spreadsheet that we want to validate.

function validateDocs() {

  var options = {
      muteHttpExceptions: true,
      'method' : 'get',
      'contentType': 'application/json',
      'headers' : {'Api-Key':'YOUR-API-KEY'}
    };

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var theSheet = activeSheet.getSheetByName("Sheet1"); 
  var numberofColumns = theSheet.getMaxColumns();   
  var numRows = theSheet.getLastRow()-1;
  var startRow = 2;
  var dataRange = theSheet.getRange(startRow,1, numRows, NumberofColumns);
  var data = dataRange.getValues();
}

Next, let’s add the for loop we need to traverse the data and apply the queries.

for (i in data) {
    let row = data[i];
    let rowNum = parseInt(i) + startRow;
}

Let’s validate that our NPIs are actual NPIs. Within the for loop, add the following code. You’ll notice where we ping the Convergent “searchSingleBest” API on the third line, passing the NPIs in the first column. The searchSingleBest endpoint is very similar to our regular search endpoint; however, it uses stricter searching rules and only returns the single best result instead of an array of results. We return “NO MATCH,” “MORE THAN 1,” or “MATCH.”

MATCH is a Convergent-validated provider
NO MATCH means the data is inaccurate and needs review
MORE THAN 1 means the parameters supplied are not specific enough to return a unique provider

// Convergent NPI Match
    let npi = encodeURIComponent(row[0]);
    let npiMatch = theSheet.getRange("B" + rowNum);

    let url1 = 'https://api.convergent-pd.com/provider/searchSingleBest?NPI=' + npi;
    var convergentResponse = UrlFetchApp.fetch(url1, options);

    try {
      let code = convergentResponse.getResponseCode();
      let contentText = convergentResponse.getContentText();

      if (code == 404) {
        npiMatch.setValue("NO MATCH");
        console.log("[Row: " + rowNum + " NO MATCH]");
      } else if (code == 400 && contentText == "Search returned more than one result.") {
        npiMatch.setValue("MORE THAN 1");
      } else {
        var jsonData = JSON.parse(convergentResponse);
        let response = !!jsonData.id;

        if (response) {
          npiMatch.setValue("MATCH");
          //populateConvergentFields(theSheet, rowNum, jsonData);
        } else {
          npiMatch.setValue("ERROR");
        }
      }
    }
    catch (err) {
      console.error(err);
      return;
    }

Now we’ll make sure that the doctor’s name tied to the NPI are accurate matches. Paste the following code within the for loop after the above:

// Convergent NPI and Name Match
    let firstName = encodeURIComponent(row[2]);
    let lastName = encodeURIComponent(row[3]);
    let npiAndNameMatch = theSheet.getRange("E" + rowNum);

    let url2 = 'https://api.convergent-pd.com/provider/searchSingleBest?NPI=' + npi + '&firstname=' + firstName + '&lastname=' + lastName;
    var convergentResponse = UrlFetchApp.fetch(url2, options);

    try {
      let code = convergentResponse.getResponseCode();
      let contentText = convergentResponse.getContentText();

      if (code == 404) {
        npiAndNameMatch.setValue("NO MATCH");
      } else if (code == 400 && contentText == "Search returned more than one result.") {
        npiAndNameMatch.setValue("MORE THAN 1");
      } else {
        var jsonData = JSON.parse(convergentResponse);
        let response = !!jsonData.id;

        if (response) {
          npiAndNameMatch.setValue("MATCH");
          //populateConvergentFields(theSheet, rowNum, jsonData);
        } else {
          npiAndNameMatch.setValue("ERROR");
        }
      }
    }
    catch (err) {
      console.error(err);
      return;
    }

Finally, let’s validate our addresses. Addresses are always a bit tricky with docs as they move around and add/remove locations quite a bit. Luckily, Convergent is scouring 100s of public and private sources of data to determine the most accurate, up-to-date addresses. This API query returns a MATCH if we have the source address within Convergent associated with the NPI. A future tutorial will go over how to return our addresses if the source address is invalid. We’re standardizing addresses on the way in so don’t worry if your address data is a tad messy.

// Convergent NPI & Address Match
    let sourceaddress = encodeURIComponent(row[5]);
    let npiAndAddressMatch = theSheet.getRange("G" + rowNum);

    let url3 = 'https://api.convergent-pd.com/provider/searchSingleBest?NPI=' + npi + '&address=' + sourceaddress;
    var convergentResponse = UrlFetchApp.fetch(url3, options);  

    try {
      let code = convergentResponse.getResponseCode();
      let contentText = convergentResponse.getContentText();

      if (code == 404) {
        npiAndAddressMatch.setValue("NO MATCH");
      } else if (code == 400 && contentText == "Search returned more than one result.") {
        npiAndAddressMatch.setValue("MORE THAN 1");
      } else {
        var jsonData = JSON.parse(convergentResponse);
        let response = !!jsonData.id;

        if (response) {
          npiAndAddressMatch.setValue("MATCH");
        } else {
          npiAndAddressMatch.setValue("ERROR");
        }
      }
    }
    catch (err) {
      console.error(err);
      return;
    }

Run the Script

Now that we have all the code set up, make sure “validateDocs” function is selected, then click “Run.”

This will print out the Convergent responses in the spreadsheet like below:

These example providers show how we have valid data across the board except for Dr. Matthew Silverman’s practice address. If we want to go ahead and get his Convergent address, we can parse the JSON response and return that data in the adjacent column(s). We’ll go over how to do that in subsequent tutorials.

If you’d like to get started with the Convergent API, go ahead and grab your credentials here.

How to Validate Doctor Data from a Google Spreadsheet

The Convergent API Parts

The Google Parts

Validate a Doc from App Script

Run the Script

Automation and Closed-Loop Health IT

Maryland HIE improves provider data workflows/transparency with Leap Orbit Provider Directory API