const sheet = SpreadsheetApp.getActiveSheet();
const apiKey = '';

function getUuids() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numRows = sheet.getLastRow()-1;   // Number of rows to process
  var dataRange = sheet.getRange(3, 3, numRows, 1);
  var values = dataRange.getValues();
  writeHeadings();

  for (email in values){
    var row = values[email];
    var email = row[0]
    Logger.log(email)
    var api_url = 'https://api.northpass.com/v2/people/?filter[email][eq]='+email;
    const settings = {
      async: true,
      crossDomain: true,
      method: 'GET',
      headers: {
        accept: 'application/json',
        'X-Api-Key': apiKey
      }
    };
    const sendMsg = UrlFetchApp.fetch(api_url, settings);
    var uuidResponse = sendMsg.getContentText();
    var parsedata = JSON.parse(uuidResponse);
    Logger.log(parsedata)
    try {
      var uuid = parsedata["data"][0]["id"];
      if (email != "") {
        findRow(email, uuid);
      }
    }
    catch(ex) {
      Logger.log(ex)
      continue
    }
    finally {
    }
   };
 };

function findRow(email, uuid){
  var sheetRow = SpreadsheetApp.getActiveSpreadsheet();
  var data = sheetRow.getDataRange().getValues();
  for(var i = 0; i<data.length;i++){
    if(data[i][2] == email){ //[1] because column B
      // Logger.log((i+1))
      var row = i+1;
      propstoSheet(uuid, row, email);
    }
  }
}

function propstoSheet(uuid, row, email) {
    var uuid_url = 'https://api.northpass.com/v2/properties/people/'+uuid;
    const settings = {
      async: true,
      crossDomain: true,
      url: uuid_url,
      method: 'GET',
      headers: {
        accept: 'application/json',
        'X-Api-Key': apiKey,
      }
    };
    const sendMsg = UrlFetchApp.fetch(uuid_url, settings);
    var txtResponse = sendMsg.getContentText();
    var parseProps = JSON.parse(txtResponse);
    var agency_name = parseProps["data"]["attributes"]["properties"]["agency_name"];
    if (agency_name == null ) {
      var agency_name = "No Agency"
    }

    // Write the Data to each row and column
    sheet.getRange(row, 16).setValue(agency_name);
    // Logger.log(row + "," + email);
  }

function writeHeadings() {
  // Write the new Column Headings
  sheet.getRange(1, 16).setValue("Agency Name");
}

