AIを使って企業住所から近くの支店を割り振りしたお話

先ずはデータの区分け処理

企業情報が取得できたので、その企業住所から最も近い支店を割り振り、新規開拓がんばろうスプレッドシートを作成しました（させられました）

企業情報には住所が記載されておりますが、数百件あるのでこれを平等に支店に割り振るのは人力では至難の業です。ので、ここはChatGPTでGASコードを書いてもらいます。

エクセルデータをスプレッドシートにインポートして、以下のプロンプトをGPT4oに入力。まず褒める！

あなたはとても優秀なデータアナリストです。

https://docs.google.com/spreadsheetｘxxxxxxxxxxxxxxxxxxxxxxxxxxxx/d/ｘ/edit?hl=JA#gid=521602645

上記シートにある"住所"タブのJ列にある住所を
以下のサイト上にある支店の住所に近い振り分けを行い、一番右列に”営業先”列を追加し、それぞれの数字を記入せよ
https:/支店住所が記載されているサイトのurl

GoogleMap API取得

API必要といわれるので、取得します。このあたり分からなくなったらGPTに壁打ちしつつAPIを取得してきましょう。

手順

1. Google Cloud Platformにログイン:

   • Google Cloud Platformコンソールにログインします。

2. APIプロジェクトを選択:

   • 正しいプロジェクトが選択されていることを確認します。なければ新規作成

3. Geocoding APIの有効化:

   • ナビゲーションメニューから「APIとサービス」→「ライブラリ」に移動します。

   • 「Geocoding API」を検索し、有効になっていない場合は有効にします。

4. APIキーの確認:

   • ナビゲーションメニューから「APIとサービス」→「認証情報」に移動します。

   • APIキーを選択し、以下の点を確認します。

5. APIキーの制限設定:

   • 「アプリケーション制限」が「なし」または適切な制限（HTTPリファラ、IPアドレス）が設定されていることを確認します。

   • 「API制限」で「Geocoding API」が選択されていることを確認します。

6. APIキーをスクリプトに適用:

   • 取得したAPIキーを以下のスクリプトのYOUR_GOOGLE_MAPS_API_KEYの部分に置き換えます。

APIが取得出来たら、メモしておきGPTにGASコードを書いてもらいます。

const apiKey = 'YOUR_GOOGLE_MAPS_API_KEY';　←この箇所に取得したGoogle MAP APIキーをコピペ

function categorizeAddresses() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('住所');
  const addressColumn = 10; // J列
  const startRow = 2; // データが始まる行番号（ヘッダーがある場合）
  const numRows = sheet.getLastRow() - startRow + 1; // データの行数

  const addresses = sheet.getRange(startRow, addressColumn, numRows).getValues();
  const branches = getBranches();
  const categorizedData = [];
  const branchNames = [];

  addresses.forEach(row => {
    const address = row[0];
    const branchNumber = getNearestBranch(address, branches);
    const branchName = getBranchName(branchNumber);
    categorizedData.push([branchNumber]);
    branchNames.push([branchName]);
  });

  // "営業先"列を追加して書き込み
  const salesColumn = sheet.getLastColumn() + 1;
  sheet.getRange(startRow - 1, salesColumn).setValue('営業先');
  sheet.getRange(startRow, salesColumn, numRows, 1).setValues(categorizedData);

  // "対応支店名"列を追加して書き込み
  const branchNameColumn = sheet.getLastColumn() + 1;
  sheet.getRange(startRow - 1, branchNameColumn).setValue('対応支店名');
  sheet.getRange(startRow, branchNameColumn, numRows, 1).setValues(branchNameｓ);
}

function getBranches() {
  return [
    { number: 1, name: '埼玉支店', address: 'xxxxxxxx', lat: xxxxxxxx, lng: xxxxxxxx },
    { number: 2, name: '東京支店', address: 'xxxxxxxx', lat: xxxxxxxx, lng: xxxxxxxx },
    { number: 3, name: '名古屋支店', address: 'xxxxxxxx', lat: xxxxxxxx, lng: xxxxxxxx },
    { number: 4, name: '神奈川支店', address: 'xxxxxxxx', lat: xxxxxxxx, lng: xxxxxxxx },
    { number: 5, name: '群馬支店', address: 'xxxxxxxx',lat: xxxxxxxx, lng: xxxxxxxx }
  ];
}
xxxxxxxx
function getNearestBranch(address, branches) {
  const apiKey = 'YOUR_GOOGLE_MAPS_API_KEY';
  const geocode = getGeocode(address, apiKey);
  if (!geocode) return '不明';

  let nearestBranch = '不明';
  let shortestDistance = Infinity;

  branches.forEach(branch => {
    const distance = getDistance(geocode.lat, geocode.lng, branch.lat, branch.lng);
    if (distance < shortestDistance) {
      shortestDistance = distance;
      nearestBranch = branch.number;
    }
  });

  return nearestBranch;
}

function getGeocode(address, apiKey) {
  const baseUrl = 'https://maps.googleapis.com/maps/api/geocode/json';
  const response = UrlFetchApp.fetch(`${baseUrl}?address=${encodeURIComponent(address)}&key=${apiKey}`, { muteHttpExceptions: true });
  const data = JSON.parse(response.getContentText());
  
  if (data.status === 'OK') {
    const location = data.results[0].geometry.location;
    return { lat: location.lat, lng: location.lng };
  } else {
    Logger.log(`Geocoding failed for address: ${address}, status: ${data.status}, error_message: ${data.error_message}`);
    return null;
  }
}

function getDistance(lat1, lng1, lat2, lng2) {
  const R = 6371e3; // 地球の半径（メートル）
  const φ1 = lat1 * Math.PI/180;
  const φ2 = lat2 * Math.PI/180;
  const Δφ = (lat2-lat1) * Math.PI/180;
  const Δλ = (lng2-lng1) * Math.PI/180;

  const a = Math.sin(Δφ/2) * Math.sin(Δφ/2) +
            Math.cos(φ1) * Math.cos(φ2) *
            Math.sin(Δλ/2) * Math.sin(Δλ/2);
  const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));

  const distance = R * c;
  return distance;
}

function getBranchName(branchNumber) {
  const branchNames = {
    1: '埼玉支店',
    2: '東京支店',
    3: '名古屋支店',
    4: '神奈川支店',
    5: '群馬支店'
  };
  return branchNames[branchNumber] || '不明';
}

スプレッドシート上の”住所”タブにあるJ列からそれぞれの住所を抽出。
支店ごとの住所をGoogleマップより抜き出し、レコードがどの支店に最も近いかを把握、右列に該当支店名を記入していく内容となってます。コードそのものは重要ではなくて、これらをChatGPTで作れるという点がAI革命といった点なのでしょう。

以上、GPTの具体的な利用例でした。