84 lines
2.4 KiB
JavaScript
84 lines
2.4 KiB
JavaScript
const sheet = SpreadsheetApp.getActiveSheet();
|
|
const apiKey = 'RfmxChNLLodO6M0Z88BwG9Xyu'
|
|
// const apiKey = '6JNiiDgAwWwYoH8YdEvrFwxc1';
|
|
|
|
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 != "") {
|
|
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 company = parseProps["data"]["attributes"]["properties"]["brand_group"];
|
|
var account_name = parseProps["data"]["attributes"]["properties"]["account_name"];
|
|
|
|
// Write the Data to each row and column
|
|
sheet.getRange(row, 15).setValue(company);
|
|
sheet.getRange(row, 16).setValue(account_name);
|
|
// Logger.log(row + "," + email);
|
|
}
|
|
|
|
function writeHeadings() {
|
|
// Write the new Column Headings
|
|
sheet.getRange(1, 15).setValue("Company");
|
|
sheet.getRange(1, 16).setValue("Account");
|
|
}
|