２つのGoogleスプレッドシートのデータを電話番号をキーにして統合する

スプレッドシートの内容を合成する

【Google SpreadSheet A】: HubSpotの名刺スキャンで取り込んでエクスポートしたデータ
【Google SpreadSheet B】: Google Formで登録した追加情報データ
があります。AにもBにも電話番号の情報が入っているので、これをキーにして同一人物を特定し、データを統合したい、という事例です。HubSpotのAPIを使用してHubSpotのデータを直接更新していく方法もあると思いますが、今回は目視で確認したかったので、一度、両方のデータを【Google SpreadSheet C】に合成出力して、それをインポートする、という方法をとりました。

統合するために書いたスクリプト

効率的ではないかもしれませんが、とりあえず動きます。［拡張機能］→［Apps Script］からApps Scriptを出して下記を登録してください。

function formatPhoneNumber(phoneNumber) {

  //空白など
  if(!phoneNumber || phoneNumber.length < 3){
    return phoneNumber
  }

  // ハイフンを取り除く
  var cleanedNumber = phoneNumber.toString().replace(/-/g, '');

  // 数字以外を除去する
  var numericString = cleanedNumber.toString().replace(/\D/g, '');

  return numericString;
}

//電話番号の検索
function searchRow(GFormSheet, col, tel) {
  var rows = GFormSheet.getDataRange().getValues();
  tel = formatPhoneNumber(tel);

  if(!tel){
    return 0;
  }

  for (var i = 0; i < rows.length; i++) {
    if (formatPhoneNumber(rows[i][col]) === tel) {
      // 見つかった行の処理
      var rowNumber = i + 1; // 見つかった行の行番号
      return rowNumber;
    }
  } 
  return 0;
}

function merge() {

  ///////////////////////////////////////////////////////////////
  // Google Spreadシート統合マクロ
  // 2023 SD
  // 用途：電話番号を共通キーにして、指定した2つのGoogleスプレッドシートの行を合成出力します。
  // ここから下の設定を毎回、確認してください。

  //使用するスプレッドシート。URLから id を確認してください。
  var HubSpotSpreadSheet    = SpreadsheetApp.openById("【"1RZwP0...."などのGoogleスプレッドシート（HubSpot出力）のidをURLから見つけて指定】") //"HubSpotからExportしたデータ";
  var GoogleFormSpreadSheet = SpreadsheetApp.openById("【"1RZwP0...."などのGoogleスプレッドシート（GoogleForm出力）のidをURLから見つけて指定】") //"Googleフォーム（回答）"の内容;
  //結果を出力するスプレッドシートの情報
  var mySpreadSheet = SpreadsheetApp.getActiveSpreadsheet();

  //シートの取得。シートタブから名称を確認してください。
  var HubSheet   = HubSpotSpreadSheet.getSheetByName('シート1');
  var GFormSheet = GoogleFormSpreadSheet.getSheetByName('フォームの回答 1');
  var MySheet = mySpreadSheet.getSheetByName('シート1');

  //HubSpotシートにおける電話番号列(A列=0)
  var HubSpotTelephoneColumn = 2;
  //Google Formシートにおける電話番号列(A列=0)
  var GFormTelephoneColumn = 2;
  //書き出すスプレッドシートでは、何列目から追加データを書き出しますか？(A列=0)
  var MySpreadSheetAdditionalDataColumn = 5;

  // ここまで
  ///////////////////////////////////////////////////////////////

  var HrowsRange = HubSpotSpreadSheet.getDataRange();
  var GrowsRange = GoogleFormSpreadSheet.getDataRange();
  var Hrows = HrowsRange.getValues();
  var Grows = GrowsRange.getValues();
  
  // 行ごとに処理
  for (var i = 1; i < Hrows.length; i++) {
    var Hrow = Hrows[i];
    
      //書き込む自分のシートの最終行
      var lastRow = mySpreadSheet.getLastRow();

      //データ元
      var collength = HrowsRange.getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();

      //電話番号で検索
      //SpreadsheetApp.getUi().alert("telは"+Hrow[HubSpotTelephoneColumn]+"です");
      var telNum = Hrow[HubSpotTelephoneColumn];
      var res = searchRow(GFormSheet, GFormTelephoneColumn, telNum);
      if(res > 0){
        //found!
        //基本データ　コピー&ペースト
        var Hdata = [Hrow]; //HubSpot Data
        var pasteRange1 = MySheet.getRange(lastRow+1, 1, 1, Hdata[0].length);
        pasteRange1.setValues(Hdata);
        //SpreadsheetApp.getUi().alert("基本データをコピー完了");

        //Additional コピー&ペースト
        var Grow = Grows[res-1]; //GForm Data
        var additionaldata = [Grow];
        var pasteRange2 = MySheet.getRange(lastRow+1,MySpreadSheetAdditionalDataColumn,1, additionaldata[0].length);
        pasteRange2.setValues(additionaldata);
        //SpreadsheetApp.getUi().alert("追加データをコピー完了");
      }
  }
  SpreadsheetApp.getUi().alert("統合できたデータの出力が終わりました。");
}

このコードをこんなスプレッドシート【Google SpreadSheet C】に入れておきます。

メニューに登録

だれでもメニューから選択して実行できるように登録します。
「マクロをインポート」を選択してください。

merge() という関数をボタンを押して（メニューに）追加します。

メニューに表示される名前は、［マクロの管理］から変更できます。

このように、［拡張機能］→［マクロ］のメニューから表示されるようになりました。

終わりに

頼まれてここまで準備しましたが、実際に使用する営業の方は、手元のiPadで実際に動かして使用されていたので驚きました。PCではなくて、iPadでサクッと実行してHubSpotへのインポートまで完了なんて。使い方は人それぞれなんですね～。