请求修改Google Apps Script实现Pipedrive企业与联系人去重创建

修改说明 #

• 新增联系人存在性校验：通过邮箱/手机号匹配Pipedrive中已关联目标企业的联系人，避免重复创建
• 优化API请求逻辑，减少无效调用
• 细化日志输出，明确操作结果

function createPipedriveEntity() {
  // 配置信息，替换为你自己的内容
  var sheetId = 'My sheet Id'; // 替换为你的Google Sheet ID
  var sheetName = 'Sheet name'; // 替换为你的工作表名称
  var apiToken = "INSERT_YOUR_API_TOKEN_HERE"; // 替换为你的Pipedrive API令牌

  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
  var last_row = sheet.getLastRow();

  // 遍历表格行（从第2行开始，跳过表头）
  for (var row = 2; row <= last_row; row++) {
    // 从表格获取数据并去除首尾空格
    var company_name = sheet.getRange('AT' + row).getValue().trim();
    var person_name = sheet.getRange('X' + row).getValue().trim();
    var person_email = sheet.getRange('N' + row).getValue().trim();
    var person_phone = sheet.getRange('FQ' + row).getValue().trim();

    // 跳过空数据行
    if (!company_name || !person_name) {
      Logger.log("第" + row + "行数据为空，跳过");
      continue;
    }

    var company_id = null;

    // 1. 检查企业是否存在于Pipedrive
    var organizationsUrl = "https://api.pipedrive.com/v1/organizations/find?term=" + encodeURIComponent(company_name) + "&api_token=" + apiToken;
    var organizationsResponse = UrlFetchApp.fetch(organizationsUrl);
    var organizations = JSON.parse(organizationsResponse.getContentText()).data;
    
    if (organizations && organizations.length > 0) {
      company_id = organizations[0].id;
      Logger.log("第" + row + "行：企业已存在 - " + company_name + " (ID: " + company_id + ")");
    } else {
      // 企业不存在则创建
      var createCompanyUrl = "https://api.pipedrive.com/v1/organizations?api_token=" + apiToken;
      var companyPayload = {
        "name": company_name
      };
      var companyOptions = {
        "method": "post",
        "contentType": "application/json",
        "payload": JSON.stringify(companyPayload)
      };
      var companyResponse = UrlFetchApp.fetch(createCompanyUrl, companyOptions);
      var companyData = JSON.parse(companyResponse);
      company_id = companyData.data.id;
      Logger.log("第" + row + "行：企业已创建 - " + company_name + " (ID: " + company_id + ")");
    }

    // 2. 检查联系人是否已关联该企业
    var personExists = false;
    var person_id = null;
    // 优先用邮箱搜索，没有邮箱则用手机号
    var searchTerm = person_email || person_phone;
    if (searchTerm) {
      var findPersonUrl = "https://api.pipedrive.com/v1/persons/find?term=" + encodeURIComponent(searchTerm) + "&org_id=" + company_id + "&api_token=" + apiToken;
      var personResponse = UrlFetchApp.fetch(findPersonUrl);
      var persons = JSON.parse(personResponse.getContentText()).data;

      if (persons && persons.length > 0) {
        // 匹配姓名+邮箱/手机号，确保是同一个联系人
        for (var i = 0; i < persons.length; i++) {
          var existingPerson = persons[i];
          var hasMatchingEmail = person_email && existingPerson.email.some(item => item.value === person_email);
          var hasMatchingPhone = person_phone && existingPerson.phone.some(item => item.value === person_phone);
          
          if (existingPerson.name === person_name && (hasMatchingEmail || hasMatchingPhone)) {
            personExists = true;
            person_id = existingPerson.id;
            break;
          }
        }
      }
    }

    if (personExists) {
      Logger.log("第" + row + "行：联系人已存在 - " + person_name + " (ID: " + person_id + ")");
    } else {
      // 联系人不存在则创建并关联企业
      var createPersonUrl = "https://api.pipedrive.com/v1/persons?api_token=" + apiToken;
      var personPayload = {
        "name": person_name,
        "email": person_email ? [{"value": person_email, "primary": true}] : [],
        "phone": person_phone ? [{"value": person_phone, "primary": true}] : [],
        "org_id": company_id
      };
      var personOptions = {
        "method": "post",
        "contentType": "application/json",
        "payload": JSON.stringify(personPayload)
      };
      var createPersonResponse = UrlFetchApp.fetch(createPersonUrl, personOptions);
      var personData = JSON.parse(createPersonResponse);
      person_id = personData.data.id;
      Logger.log("第" + row + "行：联系人已创建 - " + person_name + " (ID: " + person_id + ")");
    }
  }
}

使用步骤 #

• 替换代码中'My sheet Id'为你的Google Sheet ID（可从Sheet网址中获取）
• 替换'Sheet name'为你的工作表名称（比如"客户列表"）
• 替换"INSERT_YOUR_API_TOKEN_HERE"为你的Pipedrive API令牌（在Pipedrive账户设置的API部分生成）
• 确认代码中的单元格范围（AT列企业名、X列联系人姓名、N列邮箱、FQ列手机号）是否与你的表格匹配，若不匹配修改对应列标识
• 打开Google Sheet，点击菜单栏「扩展程序」→「Apps脚本」，粘贴代码后保存，运行函数即可

内容的提问来源于stack exchange，提问作者Purchase Service