Hi please help me with this Google Apps Script function that creates new entities in Pipedrive CRM based on data in a Google Sheet. The function iterates over rows in the Sheet, retrieves data for a company and a person, checks if the company exists in Pipedrive, creates the company if it does not exist, and then creates a new person associated with that company.
- Get the ID of the Google Sheet containing the data.
- Get the sheet named “Feuille 1” from the Google Sheet.
- Get the last row of data in the sheet.
- Loop through each row of data starting from row 2 (i.e., skipping the header row).
- Get the company name, person name, person email, and person phone number from the current row of the sheet.
- Check if the company already exists in Pipedrive by making a request to the Pipedrive API’s
/organizations/findendpoint using the company name as the search term. - If the company exists, retrieve its ID.
- If the company does not exist, create a new company in Pipedrive using the company name and retrieve its ID.
- Create a new person in Pipedrive using the person’s name, email, phone number, and the company ID.
- Log the results of each step for debugging purposes.
Everything works, except that I don’t know how to check if the person already exists, you don’t have to create it, if it exists, you get its ID.
Can you please help me, providing the full code which will also check if the person already exists, not create it. So this script will not create duplicates for company and person in pipedrive.
Please find bellow my code :
function createPipedriveEntity() {
var sheetId = ‘My sheet Id’; // Replace “My sheet Id” with the ID of your own spreadsheet
var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(“Sheet name”); // Replace “Sheet name” with the name of your spreadsheet
var last_row = sheet.getLastRow();
for (var row = 2; row <= last_row; row++) { // Start from the second row
var company_name = sheet.getRange(‘AT’ + row).getValue();
var person_name = sheet.getRange(‘X’ + row).getValue();
var person_email = sheet.getRange(‘N’ + row).getValue();
var person_phone = sheet.getRange(‘FQ’ + row).getValue();
// Check if the company already exists in Pipedrive
var organizationExists = false;
var apiToken = "INSERT_YOUR_API_TOKEN_HERE";
var organizationsUrl = "https://api.pipedrive.com/v1/organizations/find?term=" + company_name + "&api_token=" + apiToken;
var organizationsResponse = UrlFetchApp.fetch(organizationsUrl);
var organizations = JSON.parse(organizationsResponse.getContentText()).data;
if (organizations.length > 0) {
organizationExists = true;
var company_id = organizations[0].id;
Logger.log("Company found: " + company_name + " (ID: " + company_id + ")");
}
// If the company does not exist, create it in Pipedrive
if (!organizationExists) {
var url = "https://api.pipedrive.com/v1/organizations?api_token=" + apiToken;
var payload = {
"name": company_name
};
var options = {
"method": "post",
"contentType": "application/json",
"payload": JSON.stringify(payload)
};
var response = UrlFetchApp.fetch(url, options);
var company = JSON.parse(response);
var company_id = company.data.id;
Logger.log("Company created: " + company_name + " (ID: " + company_id + ")");
}
// Add the person to the company
var url = "https://api.pipedrive.com/v1/persons?api_token=" + apiToken;
var payload = {
"name": person_name,
"email": person_email,
"phone": person_phone,
"org_id": company_id
};
var options = {
"method": "post",
"contentType": "application/json",
"payload": JSON.stringify(payload)
};
var response = UrlFetchApp.fetch(url, options);
var person = JSON.parse(response);
var person_id = person.data.id;
Logger.log("Person added: " + person_name + " (ID: " + person_id + ")");
}
}
