－ドｔGASでセルの値を他シートの列と部分一致で比較し、対応する列の値を取得したい

Question

スプレッドシートについての質問です。

・やりたいこと
シート1のA列には文章、シート2のA列とB列には苗字とIDが載っています。
シート1のA列の文章内に、シート2のA列の苗字があれば合致するIDをシート1のB列に表示させたいです。
いわば、VLOOKUP関数のようなことをしたいです。

・問題点
最初は関数で行おうとしたのですが、検索対象の文章はSlackなど外部ツールから自動で書き込まれる作りにしてあるため、関数が効かない状態です。（調べたところ関数を効かせるのは無理のようです）

・試したこと
色々似たようなことをしようとしているサイトを調べたがよく理解できず。
やりたいことに似た質問が見つからなかった。
配列を使うものと配列を使わないものの情報があり混乱してしまった。

コードは思いついたところまでなので、未完成なものなのですが
ご教授よろしくお願いいたします。

var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート2");
var range = sheet1.getRange("B2:C54);
//苗字、IDシートの検索対象範囲
var data = range.getValues();
var lastRow = sheet1.getDataRange().getLastRow();

for (var k = 0; k<=lastRow; k++)
{
  if(sheet1.getRange(k,3).getValue() == data)
  {

    sheet1.getRange(k,2).setValue(data) 
  }

}

配列を使うやり方がわからなかったので、関数を分けて以下のようにしてみましたが結局解決できませんでした。

　var IDsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("IDだよ");
   var range = IDsheet.getRange('A2:C42');
//苗字とIDが載っているシートと範囲
   var data = range.getValues();
   var kakikomisaki = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("名前");
//検索対象の本文とIDを書き込む先のシート
   
   var lastRow2 = kakikomisaki.getDataRange().getLastRow();
   var num;


function myFunction() {

  for (var k = 1; k <lastRow2; k++)
  {
    var getText = kakikomisaki.getRange(k,3).getValues();    
      
    ind(getText);    


    slackid = IDsheet.getRange(num,3).getValues();
    kakikomisaki.getRange(k,4).setValue(slackid);
  }


 }

function ind(getText)
{
  for (var k = 1; k<=data.length; k++)
  {
    var idd = IDsheet.getRange(k,2).getValues();

    var result = getText.indexOf(k,2);
    
    if (result != -1)
    {     
      k  = num;
      return num;
    }
  }
}     

Comments

[3_April_2021]

シートkakikomisakiにデータが入っている場合、kakikomisaki.getDataRange().getValues()に入っているものを確認してみてください。

[3_April_2021]

▼回答が投稿できないため

目標値が得られるか確認してください。
（必要に応じて詳細を調整してください）
```gs
const values1= sheet1.getDataRange().getValues();
const values2= sheet2.getDataRange().getValues();
for(let i1=0;i1<values1.length;i1=i1+1){
for(let i2=0;i2<values2.length;i2=i2+1){
if(values1[i1][0].includes(values2[i2][0])){
Logger.log(`"${values2[i2][0]}" in "${values1[i1][0]}"`);
}
}
}

[antiwebhook]

コメントありがとうございます。
kakikomisaki.getDataRange().getValues()のデータの中身と目標値の取得ができるか試してみます。
取り急ぎ、お礼を申し上げます。

[3_April_2021]

すみません、不親切な部分がありました。（おかしなミスがなければ）i1-1がsheet1,i2-1がsheet2の行数なので、必要に応じて表示してみてください。

[3_April_2021]

訂正: -1 > +1

[3_April_2021]

特にコードには軽くしか目を通していませんでした。コメントの回答をすべき質問ではありませんでしたね。提示されたコードで問題を解決したかもしれませんが、できれば、本来行おうとした方法での解決策を見つけてほしいですね。
- 冒頭説明が正しいなら、getRangeの使い方を確認してください
- >if(sheet1.getRange(k,3).getValue() == data): dataの値を確認してください
- indexOfの使い方が違います
- >slackid = IDsheet.getRange(num,3).getValues(): numはindから呼び出せてません
- getValues()とgetValue()の違いを確認してください
- var idd = IDsheet.getRange(k,2).getValues(): この文を残している意図がわかりません。

[3_April_2021]

- >slackid = IDsheet.getRange(num,3).getValues(): numはindから呼び出せてません
これは私の勘違いでした、失礼しました。

[antiwebhook]

ご指導ありがとうございます。
業務中につきまだ実践できていないのですが、一つ一つ確認したいと思います。

- getValues()とgetValue()の違いを確認してください
→上記に関しては理解が足りず混同してしまっていました。for文でセルを一つ一つ取得しているのでgetValueが正しいのだと思います。

- var idd = IDsheet.getRange(k,2).getValues(): この文を残している意図がわかりません。
→載せたコードですとこの後変数iddを用いて何かしたかったのですが、行き詰ってしまいそのまま載せてしまっていました。申しわけありません。

[3_April_2021]

問題背景を踏まえた助言をmayu-ユーザさんがくださったようですから、私の方はあくまで提示コードへの言及に留め続けようと思います。自己解決を妨げた一番の原因は「`getValue()`と`getValues()`の違いを認識していない」だと感じました。これらのメソッドを使用する際に忘れてはならないことは、取得の形式が異なることです*。 解決コードを作成する場合は、その内容を理解した上で、指摘された他の点を確認する方が効率的かもしれません。 回答を投稿することに成功した場合、この件に関する補足があるでしょう。
*getValues()とgetValue()のreturn項目。
https://developers.google.com/apps-script/reference/spreadsheet/range

[antiwebhook]

ご丁寧な説明、並びにリファレンスまで提示していただきありがとうございます。最初に載せたコードはいろいろとめちゃくちゃだと思うので一旦初心に立ち返って、3_April_2021さんが提示してくださった簡単なテストを実行しました。それを元に返信いたします。

回答で提示して頂いた簡単なテストではvalues1をLogger.logしたところ仰られた通り[[]]が出力されました。またvalues2はデータ数の数だけ括弧の中の値を取得することができました。やはり3_April_2021さんが仰られたようにgetValues()とgetValue()の違いを認識していないことが問題だと痛感しました。
このように例を用いて行うと理解が早いのですが、曖昧なまま色々なことに手を出してしまったのが逆効果だったようです。また配列に関する記事を添付してくださり、ありがとうございます。業務でC言語をかじっていた時期があったのですが、その時から配列に関しては苦手意識を持っていて今回も使うことを避けている傾向がありました。ご丁寧な解説やご意見をいただき本当に助けになりました。

ありがとうございます。

Answer 1

追記・修正欄へのコメントを踏まえた回答です。できればスプレッドシートを新たに作って、このような単純な表を使ったテストを試してみてください。
||A|B|C|
|:--|:--:|--:|
|1|A1|B1|C1|
|2|A2|B2|C2|
|3|A3|B3|C3|

const value = sheet.getRange('A1').getValue();
const values1 = sheet.getRange('A1').getValues();
const values2 = sheet.getRange('B1:C3').getValues();
//sheetには使用シートを入れる

最初に、'A1'===valueはtrue、'A1'===values1はfalseとなることを確認してください。valueとvalues1をLogger.log()で出してみると、後者には[[]]が付いているはずです。values2も同様に確認すると、 同じように[[]]が含まれているはずですが、[]内の[]は行数と同じ数だけ増えていて、それぞれに各行のデータが含まれているはずです。
まず、「配列を作る」「配列の項目を取得し変更する」を改めて確認してください。次にvalues2[0]の値とvalues2[0][0]の予測を試み、その後で結果を確認してみてください。 よくわからなければ、範囲を変更して試したり、外部ソース内で拾える語彙も活用して情報検索してみたりしてください。また、提示されたコードについてfor内の処理への理解にも自信がない場合は、「配列の長さを調べる」およびそこに紐づけられたページも参考にしてみてください。

Comments

[3_April_2021]

(横槍で申し訳ないんですが、アロー関数は大丈夫ですか?)

[antiwebhook]

アロー関数についてはあまり使ったことがなく、よく理解していません…
記述法などは調べています。

[3_April_2021]

アロー関数に関しては、mayu-ユーザさんのコード理解を妨げる1つの要因として疑っていたに過ぎません。該当コードの理解に関して、すでに解決したか一段落ついているなら、気にする必要はありません。

[3_April_2021]

念のために。締め切りなどの今後の行動を取るとき、少なくとも私の扱いについて深く悩む必要はありません。 理解の一助となれば良いと思っただけ、 それに対して十分以上に応える姿勢も見せていただけた、私はそれで満足です。

[antiwebhook]

色々とご教授いただき本当にありがとうございました。
3_April_2021さんのおかげで自分自身の苦手意識に気が付けました。
今後は配列など使って慣れていきたいと思います。
本当に親切な回答者様だと思います。ありがとうございました。

Answer 2

値の取得方法 及び antiwebhookさんが記述したコードのデバッグは
3_April_2021さんがわかりやすく丁寧な回答をなさっているので
取得した値(配列)の転記という部分についてだけ回答します。

シート1のA列には文章、シート2のA列とB列には苗字とIDが載っています。

この前提で話を進めます。

検索対象の文章はSlackなど外部ツールから自動で書き込まれる

|slackはチームコミュニケーションツールであるため、
どのチャンネルにおいても、利用者は( メンションなどで )複数のメンバーに宛てて
メッセージを投稿することが多いでしょう。

そうすると 文章の中に含まれる名字(slack上の表示名)の数は
１つより複数であるケースのほうが多いのではないでしょうか。
そのため、VLOOKUPのように
最初に一致した値を単体で返す関数の動作を模倣できたとして
それは本当にご希望の結果なのでしょうか。

シート1のA列の文章内に、シート2のA列の苗字があれば
合致するIDをシート1のB列に表示させたいです。

仮に谷という名字のメンバーがシート2のA列に存在するとしたら
この方の名字は、文章中に含まれる谷田など他人の名字にもヒットしますし、
同性の他人に宛てられた文章でもヒットします。
また、渓谷や日比谷公園といった固有名詞にもヒットしますから
こういった副作用は把握なさっているものとします。

以下、文章中に含まれる全ての名字を拾って単一のセルにID + 名字を記載するサンプル。

■シート1

が、プログラム実行前の状態だとします。

■シート2

	A	B
1	name	ID
2	谷	111
3	山本	222
4	山本	333
5	谷田	444
6	藤田	555
7	池山	666
8	大谷	777
9	高田	888

function sample() {
    let sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
    let sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート2");
    sheet1.getRange(2, 2, sheet1.getLastRow() - 1, 1).clearContent();
    // 1行目はヘッダのため、データ範囲から除外します
    const data1 = sheet1.getRange(2, 1, sheet1.getLastRow() - 1, 1).getValues();
    const data2 = sheet2.getRange(2, 1, sheet2.getLastRow() - 1, 2).getValues();
    
    const ret = data1.map(w =>
        [ data2.filter(v => w[0].indexOf(v[0]) > -1).map(f => `${f[1]}:${f[0]}`).join("\r\n") ]
    );
    sheet1.getRange(2, 2, ret.length, ret[1].length).setValues(ret);
    console.log(ret);
}

■シート1 ( プログラム実行後 )

なお、コード中のconst ret = data1...の部分はreduceメソッドを利用しても
同様の結果を取得することができるでしょう。

    const ret = data1.map(w => 
        [
            data2.reduce((arr, v) => 
                (w[0].indexOf(v[0]) > -1 && arr.push(`${v[1]}:${v[0]}`), arr)
              , []
            )
            .join("\r\n")
        ]
    );

Comments

[antiwebhook]

ご回答ありがとうございます。
値を転記する際での懸念点などを取り上げて問題提起をしてくださったおかげで課題が浮き彫りとなり、とても分かりやすかったです。
コードについて不明な点があったので質問させていただいてもよろしいでしょうか。
_____________________________________________________________________
const ret = data1.map(w => [ data2.filter(v => w[0].indexOf(v[0]) > -1).map(f => `${f[1]}:${f[0]}`).join("\r\n") ]);
_____________________________________________________________________

の箇所で登場する、vとwとfとは何なのでしょうか。宣言をしなくても使える機能でしょうか。mapメソッドに関連して色々と調べてみたのですがわかりませんでした。v[0]とあることからおそらく配列を表しているものだとは思いますが、命名等は不要なのでしょうか。
教えていただけると幸いでございます。

[mayu-]

> vとwとfとは何なのでしょうか。

map や filter メソッドのコールバック関数の引数になります。
w, v, f は全て、配列の要素の値として定義されており、
各引数に含まれる値は以下のようなイメージです。

( w は シートA の message のことです )
w =
[
　　[ '@谷さん@高田さん...希望日をお伝え下さい' ]
　, [ '札幌出張において...@藤田さん' ]
　, [ '日比谷公園イベント...' ]
];

( v は シートB の name と id のことです )
v =
[
　　[ '谷', 111 ]
　, [ '山本', 222 ]
　, [ '山本', 333 ]
　, [ '谷田', 444 ]
　, [ '藤田', 555 ]
　, [ '池山', 666 ]
　, [ '大谷', 777 ]
　, [ '高田', 888 ]
];

( f は w の文章にヒットした name と id のリストです )
f =
[
　　[ '谷', 111 ], [ '山本', 222 ], [ '山本', 333 ], [ '高田', 888 ] 　※A2
]

f =
[
　　[ '谷', 111 ], [ '谷田', 444 ], [ '藤田', 555 ], [ '大谷', 777 ] 　※A3
]

f =
[
　　[ '谷', 111 ] 　※A4
]

> 宣言をしなくても使える機能でしょうか。

はい。
var や let で宣言する必要はありません。

[antiwebhook]

ご丁寧な解説をありがとうございます。
具体例を入れて分かりやすく解説頂き、vやwがどのようなものか理解することができました。
これはJavaScriptの記事ですが、このv,w,fの3つの引数がvalue, index, arrayと同義という認識で合ってますでしょうか。
https://qiita.com/ayana_shi/items/51fca8911d95c27ab906
ネットの記事で見るとv,wなどを検索ワードに入れて調べても出てこなかったのでメソッドのリファレンスなどに載っているものなのでしょうか。
質問ばかりで申し訳ございません。

[mayu-]

> このv,w,fの3つの引数がvalue, index, arrayと同義という認識で合ってますでしょうか。

いえ。
filterメソッドは3つの引数を受け取ることができますけど
私が指定しているのは、第１引数の value( 配列の要素の値 )だけになります。
mapも同様で、第１引数の value だけを指定しています。

v, w はざっくり申し上げると、[反復処理の各回において使用される変数]です。
( GetValuesで取得した複数行の値を列挙する際に、行ごとの値を参照します )
f は配列の要素ではあるのですけど、結果を格納するための配列とも言えますね。
const ret ... は以下の記述と同等のロジックです。

// ※インデントの空白は全角です

for (var w of data1) {
　　var f = [];
　　for (var v of data2) {
　　　　if (w[0].indexOf(v[0]) > -1) {
　　　　　f.push(`${v[1]}:${v[0]}`);
　　　　}
　　}
　　console.log(w[0].toString().substring(0, 3), f);
}

ちなみに、v, w, f は私が勝手に付けた名前なので、ネットで調べても出てこないです...。

[antiwebhook]

お返事遅くなりすみません。
とてもわかりやすいご説明ありがとうございます。
おかげで各メソッドの引数について理解することができました。
ネットのどの説明よりもわかりやすかったです。
本当にありがとうございます。