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

function getUuids() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numRows = sheet.getLastRow()-1;   // Number of rows to process
  var dataRange = sheet.getRange(2, 3, numRows, 1);
  var values = dataRange.getValues();
  writeHeadings();
  
  for (email in values){
    var row = values[email];
    var email = row[0];
    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);
    try {
      var uuid = parsedata["data"][0]["id"];
      if (email != "") {
        Logger.log(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
      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);
    Logger.log(parseProps)
    var role = parseProps["data"]["attributes"]["properties"]["city"];
    var user_id = parseProps["data"]["attributes"]["properties"]["state"];
    var paid = parseProps["data"]["attributes"]["properties"]["store_name"];

    // Write the Data to each row and column
    sheet.getRange(row, 6).setValue(role);
    sheet.getRange(row, 7).setValue(user_id);
    sheet.getRange(row, 8).setValue(paid);
    // Logger.log(row + "," + email);
  }

function writeHeadings() {
  // Write the new Column Headings
  sheet.getRange(1, 6).setValue("City");
  sheet.getRange(1, 7).setValue("State");
  sheet.getRange(1, 8).setValue("Store Name");
}
