0

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.

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. For now with the code code bellow it's don't create duplicates only for company.

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 + ")");
}
}

I try evrything I can but i'am not developer, I spoke hours with my new friends chatgpt. But I know now... only a human can help me for this case.

Thanks a lot indvance !

Purchase Service
  • 1

1 Answers1

0

This seems problematic if you're only using the names to search... may want to think about using an email or something.

The below is untested but should get you there... I have it setup to change the persons ID to the org you have listed on the google sheet line if the person exists but their organization is either blank, or different. To turn that off just change the 'update_person_org ' constant at the top to false.

const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet name');
const apiToken = "INSERT_YOUR_API_TOKEN_HERE";
const update_person_org = true;

function checkPerson(name) {
  var personsUrl =  'https://api.pipedrive.com/v1/persons/search?&term='+name.replace(' ', '%20')+'&fields=name&exact_match=true&api_token='+apiToken;
  Logger.log(personsUrl)
  var personResponse = UrlFetchApp.fetch(personsUrl);
  var persons = JSON.parse(personResponse.getContentText()).data.items;
  if (persons.length > 0) {
    person_org_id = '';
    var first_person = persons[0]['item']
    person_id = first_person.id;
    if (first_person['organization']){person_org_id = first_person['organization']['id'];}
    return {personExists: true, person_id: person_id, person_org_id: person_org_id};
  }
  else {return {personExists: false, person_id: '', person_org_id: ''};}
}


function UpdatePerson(person_id, data){
  var url = 'https://api.pipedrive.com/v1/person/' + person_id+ '?api_token=' + apiToken;
  console.log(url);
  var options = {
    'method': 'put',
    'contentType': 'application/json',
    'payload': JSON.stringify(data)
  };
  var response = UrlFetchApp.fetch(url, options);
  return response.getResponseCode();
}


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 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
    else {
      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 + ")");
    }

    if (person_name && company_id) {
      var personCheck= checkPersonExists(person_name);
      Logger.log("Person Check: " + personCheck);
        // Add the person to the company
      if (!personCheck['personExists']) {
        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 + ")");
      }
      // If you have update_person_org set to true + the person was found but has either no org id or the org id is different --> update the persons org to the company id
    else 
      if (update_person_org && personCheck['personExists'] && personCheck['person_or g_id'] != company_id) {
        Logger.log('Updating Person Org from ' + person_org + ' to ' + company_id)
        var person_update = {id: person_id, org_id: company_id};
        var putResponse = UpdatePerson(person_id, person_update);
        Logger.log('Person Update Response Code: ' + putResponse);
      }
    }
  }
}
BG1983
  • 39
  • 6