GASで二次元配列同士を比較し、合致するものを抽出、書き込みしたい

Question

実現したいこと

実際にはより表が複雑なのですが、以下の例を元に名字の合致するセルの横の職業セルをシートAのC列に書き込む機能を作成したいです。

前提

例：
シートAのA列に名字、B列に都道府県があり、シートBのA列に名字、B列に職業があるとします。
シートAは約1000行、シートBは100行のデータあります。
getValueとsetValueで行う方法はできるのですが、データ数が多く処理時間がかかるためgetValuesとsetValuesを用いて行いたいと考えています。

シートAから二次元配列で取得すると、以下のようになります。
[
[田中,東京]
[佐藤,千葉]
[岡田,福岡]
]

シートBから二次元配列で取得すると、以下のようになります。
[
[鈴木,学生]
[田中,会社員]
[山田,無職]
]

上記の配列例で言うと、A列の田中の名字がB列にもあるため、会社員の情報をシートAのC列に入れたいです。

該当のソースコード

エラーが出ているわけではないのですが、考え方が分かりません。
二次元配列同士を比較することはできないとどこかのページで見たため、flatメソッドを用いて一次元配列に変更した後、比較しようとしましたが処理時間も長いので恐らくベストな方法ではないと思っていますが、検索しても求めている方法が見つからない状態です。
for文を重ねて実行しているのが処理時間が遅い理由だと思いますが、代わりの方法が思いつきません…。

sheetA,sheetBの宣言は割愛します。

var array = [];
 for(i = 1;i<1000;i++) シートAまでの最終行まで1行ずつ取得
  {
    var sheetArange = sheetA.getRange(i,1,1,2).getValues();
    var flatA = sheetArange.flat([0]);
    for(r = 1;r<100;r++) シートBの最終行までを1行ずつ取得
    {
      var sheetBrange = sheetB.getRange(r,1,1,2).getValues();
      var flatB = sheetBrange.flat([0]);
      var ASearch = flatA[0];
      var BSearch = flatB[0];
      if(ASearch == BSearch)//if文かindexofで比較しようと思っていました。
      {
        array.pushで配列に一つ一つ入れた後、setValuesメソッドで行全体に書き込み
      }

    }

自分でも意味が分からないおかしいコードだとは思うのですが、これ以外にどのようにしたら実装できるかが全く思いつかず、とりあえず思いつくところまで書いてみた次第です。
二次元配列同士の比較の方法の考え方についてご教示いただけますと幸いです。

Comments

[Cocode]

> sheetA,sheetBの宣言は割愛します。
とありますが、今後はその記述も書くようにしていただけると幸いです。

質問投稿でコードを提示する際のポイントは、

・質問の意図と関係のない部分は省いたミニマムなコード
・かつ、第三者が不具合も含めてコピペするだけで再現できるコード

です！
今後、これらを意識してコードを載せていただけると回答を得られやすくなると思います〜。

[antiwebhook]

Cocodeさん
お返事遅くなりました。
早速コメント・ご回答いただきありがとうございます。

ご指摘の通り、コピペだけで再現できるコードという点で配慮に欠けていました。申し訳ないです。
次回よりその2点を軸にコードを掲載するようにします。

Answer 1

こちらでいかがでしょう？

説明はコード内のコメントに記載させていただきましたが、わかりにくい部分があればおっしゃってくださいませ。
補足説明いたします。

function writeJobs() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheetA = ss.getSheetByName('SheetA');
  const sheetB = ss.getSheetByName('SheetB');

  // シートAのA列（名前）だけ取得し、1次元配列化する
  const namesA = sheetA.getRange('A2:A').getValues().flatMap(arr => arr[0] || []);
  // [ '田中', '佐藤', '岡田' ]

  // シートBの値を全て取得する（1行目は項目名なので除く）
  const valuesB = sheetB.getDataRange().getValues().slice(1);
  // [ [ '鈴木', '学生' ],
  //   [ '田中', '会社員' ],
  //   [ '山田', '無職' ],
  //   [ '岡田', 'しゃちょー' ] ]

  // シートBの値を名前だけの1次元配列化する
  const namesB = valuesB.map(([name]) => name);
  // [ '鈴木', '田中', '山田', '岡田' ]
  
  // 職業名の2次元配列を新しく生成する
  const jobsToWrite = namesA.map(name => {
    // シートAの名前がBの名前リストの何番目にあるかインデックスを取得
    // 配列.indexOf(値)メソッドは、値が配列に存在する場合はインデックス番号を、ない場合は-1を戻す
    const index = namesB.indexOf(name);

    // 番号が−1でなかったら、シートBの値の2次元配列の、該当するインデックス番号の、B列の値を配列にいれる
    // 番号が−1（存在しない）場合は、空文字を配列にいれる
    return index != -1 ? [valuesB[index][1]] : [''];
  });
  // [ [ '会社員' ], [ '' ], [ 'しゃちょー' ] ]

  // シートAのC列に職業名を一括で書き込む
  sheetA.getRange(2, 3, jobsToWrite.length, 1).setValues(jobsToWrite);
}

---

（コメント欄のご質問への回答）

③8行目のarrや19行目のnameは宣言不要で使えるものでしょうか？

そちらはアロー関数の引数ですので宣言しません。
宣言するのは変数や定数ですよね。
アロー関数に関しては先人等が分かりやすい説明をたくさん残してくださっていますので、ぜひ検索してみてください。

// アロー関数
const namesA = sheetA.getRange('A2:A').getValues().flatMap(arr => arr[0] || []);

// 普通の関数
const namesA = sheetA.getRange('A2:A').getValues().flatMap(function(arr) {return arr[0] || []});

---

①8行目「シートAのA列（名前）だけ取得し、1次元配列化する」の箇所の以下のコードですが、flatをmapメソッドで配列全体に実行しているものと推察しますが、flatMapの後の（）内で何をしているのかが良くわかりません。=>や||はどういった意味を持つのでしょうか。 =>は「～より大きい」のような比較演算子ではないですよね？
追記：もしかしてこれがアロー関数ってやつですかね…？
const namesA = sheetA.getRange('A2:A').getValues().flatMap(arr => arr[0] || []);

回りくどいこのやり方じゃない方が分かりやすかったなぁ…と後悔しておりますすみません…。
おっしゃる通りアロー関数です。

=>や||はどういった意味を持つのでしょうか。

=>は、アロー関数の記号です。等号・不等号<=ではありません。

||は、論理和です。
if文でいずれかに該当するかどうかtrue/falseを判定したいときによく使われると思いますが、
左辺がtrueっぽい値なら左辺の値を、左辺がfalseっぽい値で右辺がtrueっぽい値なら右辺の値を、どちらもfalseっぽい値ならfalseを戻すのにも使えます。

const a = '';
const b = 'bbb';

console.log(a || b); // 'bbb'

私のコードの8行目では、A列の値を.getValues()で取得しています。
[ ['田中'], ['佐藤'], ['岡田'], [''], [''], [''], [''], [''], [''], [''], [''], [''] ]
取得できる値は2次元配列配列で、↑という状態なので、['']を削除しつつ、['田中']たちを1次元配列化しています。

.flatMap()メソッド自体については、ご存知でなければ一度お調べになってください。

---

②30行目 returnの後の ? [valuesB[index][1]] : ['']; の?と: ['']はどういった意味を持つのでしょうか。 ※記号（？）で検索を掛けても記事が見つからず…

?と:記号は、三項演算子という、if文を1行でかける記法です。
詳細は三項演算子について一度お調べください。

条件 ? trueの時の処理や値 : falseの時の処理や値;

['']については、シートBに該当する名前がないので職業欄になにも入力しないので、空文字''を配列の中にいれています。

Comments

[antiwebhook]

Cocodeさん
ご回答いただきありがとうございます。

コメントを事細かに記入していただいたおかげで、全体のイメージはつかむことができました。
知識不足で申し訳ありませんが、何点かわからない箇所がありましたので、質問させてください。

①8行目「シートAのA列（名前）だけ取得し、1次元配列化する」の箇所の以下のコードですが、flatをmapメソッドで配列全体に実行しているものと推察しますが、flatMapの後の（）内で何をしているのかが良くわかりません。||はどういった意味を持つのでしょうか。
const namesA = sheetA.getRange('A2:A').getValues().flatMap(arr => arr[0] || []);

②8行目のarrや19行目のnameは宣言不要で使えるものでしょうか？

調べていく段階でいくつか理解できましたので、質問を編集しました。
以上2点、無知ですみませんがご説明いただけますと大変助かります。。。

[Cocode]

回答を更新しました〜！

[antiwebhook]

入れ違いで質問を更新してしまいました。
じっくり読ませていただきます！！！ありがとうございます！！！！！！！！！！