【GAS】スプシをインポート、マッチングして最終列に転記する

アクティブなシートに別のスプシをインポートしてマッチングする

このGoogle Apps Script（GAS）のコードは、Googleスプレッドシート上で、2つの別のスプレッドシートファイルをインポートして、それらのデータを突き合わせるプログラムです。具体的には、以下の処理を行います。

概要（流れ）

自身のスプレッドシートを取得し、最後の行と最後の列を取得する。
1つ目の外部スプレッドシートを取得し、最後の行と最後の列を取得する。
1つ目の外部スプレッドシートのデータを自身のスプレッドシートにコピーする。
2つ目の外部スプレッドシートを取得し、最後の行と最後の列を取得する。
2つ目の外部スプレッドシートのデータと、自身のスプレッドシートのデータを突き合わせ、新しい列にマッチング結果を表示する。
2つ目の外部スプレッドシートを閉じ、削除する。
処理完了を通知する。

詳細解説

１．自身のスプレッドシートを取得し、最後の行と最後の列を取得する。
＊現在アクティブなスプレッドシートを取得し、その最後の行と最後の列を取得しています。

var ss1 = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss1.getActiveSheet();
var lastRow1 = sheet1.getLastRow();
var lastColumn1 = sheet1.getLastColumn();

２．1つ目の外部スプレッドシートを取得し、最後の行と最後の列を取得する。
外部のスプレッドシートをURLから取得し、そのファイルIDを指定して、DriveAppでファイルを取得しています。その取得したファイルをopen()メソッドで開き、最初のシートを取得しています。また、そのシートの最後の行と最後の列を取得しています。

var file1 = DriveApp.getFileById(UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/【ファイルID】/export?format=xlsx").getBlob().setName('temp_file').getId());
var ss2 = SpreadsheetApp.open(file1);
var sheet2 = ss2.getSheets()[0];
var lastRow2 = sheet2.getLastRow();
var lastColumn2 = sheet2.getLastColumn();

３．1つ目の外部スプレッドシートのデータを自身のスプレッドシートにコピーする。

sheet1.getRange(1, 1, lastRow1, lastColumn1).clearContent();
sheet2.getRange(1, 1, lastRow2, lastColumn2).copyTo(sheet1.getRange(1, 1), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false

4．「突合処理」を行う
まず、myDicという空のオブジェクトを作成します。
その後、sheet3の2行目から最終行までを順番に読み取り、各行の1列目の値をキー、2列目の値を値として、myDicに格納していきます。つまり、sheet3のデータをキーと値のペアに変換して、myDicというオブジェクトに格納することで、データを効率的に扱えるようにします。

５．次に、sheet1の2行目から最終行までを順番に読み取ります。
各行の1列目の値をキーとして、myDicから値を取得します。もしmyDicにそのキーが存在している場合、sheet1の最終列にその値をセットします。このようにして、sheet1のデータを、sheet3のデータと突合し、一致する場合には値をセットすることができます。

最後に、sheet1の最終列に「新規追加」という文字列をセットしています。これは、突合い処理の結果、sheet3に存在するがsheet1に存在しないデータを示すためのものです。

 // 突合処理
  var myDic = {};
  for (var i = 2; i <= lastRow3; i++) {
    var key = sheet3.getRange(i, 1).getValue();
    var value = sheet3.getRange(i, 2).getValue();
    myDic[key] = value;
  }

 for (var i = 2; i <= lastRow1; i++) {
    var key = sheet1.getRange(i, 1).getValue();
    if (myDic[key]) {
      sheet1.getRange(i, lastColumn1 + 1).setValue(myDic[key]);
    }
  }

sheet1.getRange(1, lastColumn1 + 1).setValue("新規追加");

完全なコード

function importAndMatch() {
  // このスプレッドシート
  var ss1 = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss1.getActiveSheet();
  var lastRow1 = sheet1.getLastRow();
  var lastColumn1 = sheet1.getLastColumn();

  // 1つ目のインポートするファイルを開く
  var file1 = DriveApp.getFileById(UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/【ファイルID】/export?format=xlsx").getBlob().setName('temp_file').getId());
  var ss2 = SpreadsheetApp.open(file1);
  var sheet2 = ss2.getSheets()[0];
  var lastRow2 = sheet2.getLastRow();
  var lastColumn2 = sheet2.getLastColumn();

  // ファイルをインポート
  sheet1.getRange(1, 1, lastRow1, lastColumn1).clearContent();
  sheet2.getRange(1, 1, lastRow2, lastColumn2).copyTo(sheet1.getRange(1, 1), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  // 2つ目のインポートするファイルを開く
  var file2 = DriveApp.getFileById(UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/【ファイルID】/export?format=xlsx").getBlob().setName('temp_file').getId());
  var ss3 = SpreadsheetApp.open(file2);
  var sheet3 = ss3.getSheets()[0];
  var lastRow3 = sheet3.getLastRow();
  var lastColumn3 = sheet3.getLastColumn();

  // 突合処理
  var myDic = {};
  for (var i = 2; i <= lastRow3; i++) {
    var key = sheet3.getRange(i, 1).getValue();
    var value = sheet3.getRange(i, 2).getValue();
    myDic[key] = value;
  }

  for (var i = 2; i <= lastRow1; i++) {
    var key = sheet1.getRange(i, 1).getValue();
    if (myDic[key]) {
      sheet1.getRange(i, lastColumn1 + 1).setValue(myDic[key]);
    }
  }

  sheet1.getRange(1, lastColumn1 + 1).setValue("新規追加");

  // ファイルを閉じる
  ss3.getSpreadsheetTimeZone();
  ss3.saveAndClose();
  file2.setTrashed(true);

  sheet1.getRange("A1").activate();
  SpreadsheetApp.flush();

  SpreadsheetApp.getUi().alert("完了しました。");
}