Zapier を使った HubSpot と Asana の連携：集計ロジックも追加する方法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

// Code by Zapier (JavaScript)
// Input: dealAmounts (カンマ区切りの金額文字列)
const amounts = inputData.dealAmounts.split(',').map(Number);

const total = amounts.reduce((sum, val) => sum + val, 0);
const average = total / amounts.length;
const max = Math.max(...amounts);
const min = Math.min(...amounts);

output = {
  total: total,
  average: Math.round(average),
  count: amounts.length,
  max: max,
  min: min,
  summary: `合計: ¥${total.toLocaleString()} / 平均: ¥${Math.round(average).toLocaleString()} / 件数: ${amounts.length}`
};

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

// Code by Zapier (JavaScript)
// Input: dealStage, dealAmount
const stage = inputData.dealStage;
const amount = Number(inputData.dealAmount);

let project = '';
let section = '';
let priority = 'medium';

if (stage === 'closedwon') {
  project = 'オンボーディング';
  section = amount >= 1000000 ? 'エンタープライズ' : 'スタンダード';
  priority = amount >= 1000000 ? 'high' : 'medium';
} else if (stage === 'contractsent') {
  project = '契約管理';
  section = '契約書送付済み';
} else {
  project = '商談進行中';
  section = stage;
}

output = {
  project: project,
  section: section,
  priority: priority
};

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

# Code by Zapier (Python)
# Input: created_dates (カンマ区切りの日付文字列)
from datetime import datetime
from collections import Counter

dates = input_data['created_dates'].split(',')
months = [datetime.strptime(d.strip(), '%Y-%m-%d').strftime('%Y-%m') for d in dates]
monthly_counts = Counter(months)

summary_lines = [f"{month}: {count}件" for month, count in sorted(monthly_counts.items())]

output = {
    'monthly_summary': '\n'.join(summary_lines),
    'total': len(dates),
    'latest_month': max(monthly_counts, key=monthly_counts.get)
}

1
2
3
4
5

# Zapier Functions (Python)
# シークレットは環境変数のように参照可能
import os

hubspot_token = os.environ['HUBSPOT_API_KEY']  # Secrets に登録した値

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# Zapier Functions (Python)
import pandas as pd

# HubSpot から取得したディールデータ
deals = pd.DataFrame(input_data['deals'])

# ステージ別の集計
stage_summary = deals.groupby('stage').agg(
    count=('amount', 'count'),
    total=('amount', 'sum'),
    average=('amount', 'mean')
).round(0)

output = {
    'report': stage_summary.to_string(),
    'total_pipeline': int(deals['amount'].sum())
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

// Code by Zapier (JavaScript)
// Input Data:
//   hubspotToken - HubSpot Private App のアクセストークン
//   dealId       - 対象の HubSpot Deal ID
//   taskName     - Asana タスク名（Line Item の名前に使用）
//   hours        - Asana タスクの作業時間（カスタムフィールド等から取得）
//   hourlyRate   - 時間単価

const dealId = inputData.dealId;
const taskName = inputData.taskName;
const hours = Number(inputData.hours);
const hourlyRate = Number(inputData.hourlyRate || 5000);
const amount = hours * hourlyRate;

// HubSpot API で Line Item を作成し、Deal に紐付ける
const response = await fetch(
  'https://api.hubapi.com/crm/v3/objects/line_items',
  {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${inputData.hubspotToken}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      properties: {
        name: taskName,
        price: amount,
        quantity: 1,
        description: `Asana タスク「${taskName}」: ${hours}時間 × ¥${hourlyRate.toLocaleString()}`
      },
      associations: [
        {
          to: { id: dealId },
          types: [
            {
              associationCategory: 'HUBSPOT_DEFINED',
              associationTypeId: 20
            }
          ]
        }
      ]
    })
  }
);

const result = await response.json();

if (!response.ok) {
  throw new Error(`HubSpot API error: ${JSON.stringify(result)}`);
}

output = {
  lineItemId: result.id,
  lineItemName: taskName,
  amount: amount,
  status: 'created'
};

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

// 既存の Product ID を指定して Line Item を作成
body: JSON.stringify({
  properties: {
    hs_product_id: inputData.productId,  // HubSpot の Product ID
    quantity: Number(inputData.hours),
    // price は Product のデフォルト価格が使われる（上書きも可能）
  },
  associations: [
    {
      to: { id: dealId },
      types: [
        { associationCategory: 'HUBSPOT_DEFINED', associationTypeId: 20 }
      ]
    }
  ]
})

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

// Code by Zapier (JavaScript)
// Input Data:
//   tasks - JSON 文字列（Asana タスクの配列）
//   hubspotToken, dealId

const tasks = JSON.parse(inputData.tasks);
const results = [];

for (const task of tasks) {
  const res = await fetch(
    'https://api.hubapi.com/crm/v3/objects/line_items',
    {
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${inputData.hubspotToken}`,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({
        properties: {
          name: task.name,
          price: task.amount,
          quantity: 1
        },
        associations: [
          {
            to: { id: inputData.dealId },
            types: [
              { associationCategory: 'HUBSPOT_DEFINED', associationTypeId: 20 }
            ]
          }
        ]
      })
    }
  );

  const data = await res.json();
  results.push({ id: data.id, name: task.name, status: res.ok ? 'ok' : 'error' });
}

output = {
  created: results.filter(r => r.status === 'ok').length,
  errors: results.filter(r => r.status === 'error').length,
  details: JSON.stringify(results)
};