GASでスプレッドシートのA列から文字列(A)を探し、右隣に文字列(B)を入力。なければ、最終行に文字列Aと文字列Bを入力したいです

今の状況

GASでLINEbotを作っています。
色々なコマンドを追加していているのですが、
コマンドが反応してほしくない時に反応しないように、ミュート機能を追加しようと思います。
そのでスプレッドシートにミュートのオンオフを挿入して、
メッセージが来たとき、セルを参照して、コマンドを実行するかどうかを判定しています。

したいこと

ユーザから「ミュート」という言葉を受けたら、
送信したユーザーのuserIdをスプレッドシートのA列から探し、
userIdが見つかった場合は、その隣のセルにcom_offと挿入したいです！
userIdが見つからなかった場合は、A列の最終行にuserIdを同行B列にcom_offを、
入れたいです。

試したこと

上記のようにA1にオンオフを入れてみたのですが、これだと、どこかでオフにすると全グループ、トークでオフになってしまうため、個々にオンオフを設定したいです。
そのため、userIdをA列から参照して同行のB列の取得または入力をしたいです。
どうすればいいか教えてください。

今使っているソースコード

GAS
1 if (SpreadsheetApp.getActiveSheet().getRange(1, 1).getValue() == 'com_on'){
2//メッセージを返すためのソース
3}

補足情報（FW/ツールのバージョンなど）

gooogle apps script

行動規範の内容に同意します

回答1件

ベストアンサー

難しく考える必要はありません。
１行目からSheet.getLastRow()で取得した最終行まで各行をループしなが１列目のユーザーIDと一致するか比較していくだけです。
一致する行があれば、その行の２列目の値を書き換えます。
一致する行がなければ、最終行の次の行にユーザーIDと値を書き込みます。

JavaScript
1const userId = ".....";
2
3const sheet = SpreadsheetApp.getActiveSheet();
4const lastRow = sheet.getLastRow();
5
6let isUserFound = false;
7for (let row = 1; row <= lastRow; row++) {
8    if (sheet.getRange(row, 1).getValue() === userId) {
9        //userIdが見つかっら、データを更新
10        sheet.getRange(row, 2).setValue("com_off");
11        isUserFound = true;
12        break;
13    }
14}
15if (!isUserFound) {
16    //userIdが見つからなければ、最終行の次の行のデータを更新
17    const newLastRow = lastRow + 1;
18    sheet
19        .getRange(newLastRow, 1, 1, 2)
20        .setValues([[userId, "com_off"]]);
21}

ただ実際の実装では上のサンプルの様に行ごとにgetRange()してgetValue()するんじゃなくて、必要な範囲をまとめてgetRange()してからgetValues()して得た配列を使ってループした方がいいです。その方が速いので。

(追記)

あと、ここでかいたcom_on,offを探し、onだった場合こうするとやる場合

トグルするとしたらこんな感じでしょうか。

JavaScript
1function myFunction() {
2    const userId = "xyz";
3
4    const sheet = SpreadsheetApp.getActiveSheet();
5    const lastRow = sheet.getLastRow();
6    const data = sheet.getRange(1, 1, lastRow, 2).getValues();
7
8    const userRow = data.find((rowData) => rowData[0] === userId);
9    if (userRow) {
10        if (userRow[1] === "com_off") {
11            userRow[1] = "com_on";
12        } else {
13            userRow[1] = "com_off";
14        }
15    } else {
16        data.push([userId, "com_off"]);
17    }
18
19    sheet.getRange(1, 1, data.length, 2).setValues(data);
20}

投稿2020/05/23 12:59

編集2020/05/23 16:40

draq

総合スコア2573

Yo_4040

2020/05/23 15:42

userIdを自分用に少し変えて、最後のgetRangeとsetValuesがなぜか反応しなかったので ``` sheet .getRange(newLastRow, 1) .setValues([[userId]]) sheet .getRange(newLastRow, 2) .setValues([["com_off"]]); ``` としたら出来ました！ GASでもまとめて出来るのですか!? あと、ここでかいたcom_on,offを探し、onだった場合こうするとやる場合、どのようにif文を書けば良いのでしょうか？どこにifを入れるのか分かりかねます。。