【GAS】複数のNGワードを 見つける方法(Gスプレッドシート)

INDEX

GAS応用の一覧

前回

Googleシート YouTubeの関連動画を表示させない方法
  • YouTubeをウインドウいっぱいに表示させることができます。
  • 関連動画が表示されないので誘惑が減ります。
  •  

    NGワードを見つける

     

     

     

     

    複数のNGワードを 見つける

     

    • セル内に含まれるNGワードを判別できるようになります。
    • GASのカスタム関数の効果的な使い方がわかります。
    • 特定のシートから値を読み込むことができます。

     

    ご質問

    S T 
    チャンネル登録期間: 3 週間
     • 8 時間前
    こんにちは。以下を実現するために最もシンプルな関数を作りたいのですがどうすればいいでしょうか?
    ・あるセルの文章の中に特定の文字列が含まれる場合、“NGワード”とすぐ近くの別のセルに返したい。
    ・特定の文字列というのは、一つではなく多数あります。できればリストを参照する形にしたいです。
    例)
    特定のワードリスト:○にたい、○ぬ、辛い
    A1セルの文章:毎晩私は○にたいと思う
    A2セルの文章:たかしくんは楽しい
    ⇓
    この場合、A1セルにはNGワードリストにある「○にたい」が含まれている判定となるので、どこかのセルに「NGワードあり」といった感じで何か値を返したいです。
    A2セルには含まれていないので、NAなどを返したいです。
    COUNTIFSでちょっと頑張ろうとしたのですが、文字列を関数内に””で囲って繰り返し並べる長い数式になってしまい、もう少しスプレッドシートで効率的にできないかと思った次第です。
    宜しくお願い致します。

     

    動画はこちら

    【GAS】スプレッドシート。セルにNGワードが含まれているか別シートから読み込みチェックする方法(indexOf)

     

    スクリプト

    /**
    * NGワードが含まれているかチェックする。
    * 「NGワード」シートにあるNGワードが含まれているかチェックする。
    * @param {string} word チェックする文字列。
    * @return {boolen} true:NGワードあり false:NGワードなし
    * @customfunction 
    */
    function JUDGNGWORD( word )
    {
      var app = SpreadsheetApp;
      var ss = app.getActiveSpreadsheet().getSheetByName("NGワード");
      var lr  = ss.getLastRow();
      
      for( var i=2 ; i <= lr ;i++){
        var ckword = ss.getRange(i, 1).getValue();
        if(word.indexOf(ckword) >= 0 ){
          return(true);//NGワードが見つかった
        }
      }
      
      return(false);//NGワードなし
    }
    

     

    さらに

    NGワードを表示させる

    上記動画に対してご質問をいただきました。

    というわけでやっていきます。

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    シート

     

    スクリプト

    /**
    * NGワードが含まれているかチェックする。
    * 「NGワード」シートにあるNGワードが含まれているかチェックする。
    * @param {string} word チェックする文字列。
    * @return {number} number:NGワードの位置(行) false:NGワードなし
    * @customfunction 
    */
    function JUDGENGWORD( word )
    {
      var app = SpreadsheetApp;
      var ss = app.getActiveSpreadsheet().getSheetByName("NGワード");
      var lr  = ss.getLastRow();
      
      for( var i=2 ; i <= lr ;i++){
        var ckword = ss.getRange(i, 1).getValue();
        if(word.indexOf(ckword) >= 0 ){
          // return(true);//NGワードが見つかった
          return(i);//NGワードが見つかった行
        }
      }
      
      return(false);//NGワードなし
    }
    
    function testJUDENGWORD(){
      var ret = JUDGENGWORD("▲")
      console.log(ret)
    
    }

     

     

     

    さらに、さらに

    NGワードを一気に読み込む

     

    • セルの値を一気に読み込んで処理する方法がわかります。
    • 一気に読み込むので処理速度が速くなります。

     

     

     

    スクリプト

    function JudgeNGWord(word) {
      const NGWORDSHEET = "NGワード"
      const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(NGWORDSHEET)
      const lr = ss.getLastRow() //NGワードの最下行
    
      console.log(lr)
      let NgBuff = ss.getRange(2, 1, lr - 1).getValues()
    
    
      for (let i = 0; i < lr - 1; i++) {
        let ckword = NgBuff[i][0]
        console.log(ckword)
    
        if (word.indexOf(ckword) >= 0) {
          // return(true);//NGワードが見つかった
          return (ckword);//NGワードが見つかった行
        }
      }
    
      return (false);//NGワードなし
    }
    
    
    function test_JudgeNGWord() {
      console.log(JudgeNGWord("〇〇"))
    }

     

    動画はこちら

    【GAS31】②セルの値を範囲で取得し、範囲で返却する方法2重ループ

     

     

     

     

    さらにさらに。。。

    引数を一気に渡し一気に取得

    • 関数の引数をまとめて渡す方法およびまとめて返す方法がわかります。
    • 2重ループの行い方がわかります。

     

     

    動画はこちら

    【GAS31】②セルの値を範囲で取得し、範囲で返却する方法2重ループ

    スクリプト

    function JudgeNGWordRange(words) {
      let retbuf = []
    
    
    
      const NGWORDSHEET = "NGワード"
      const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(NGWORDSHEET)
      const lr = ss.getLastRow() //NGワードの最下行
    
      let NgBuff = ss.getRange(2, 1, lr - 1).getValues()
    
    
    
      for (let s = 0; s < words.length; s++) {    //チェックする文字列のループ
    
        let word = words[s][0]  //チェックする文字列
    
        let bNG = false   // true:NGワードが見つかった 
        for (let i = 0; i < lr - 1; i++) {
          let ckword = NgBuff[i][0]
    
          if (word.indexOf(ckword) >= 0) {    //NGワードが見つかった
            bNG = true
            retbuf.push(ckword)
            break
    
          }
        }
        if (!bNG)//NGワードが見つからなかったら
          retbuf.push(false)
    
      }
      return (retbuf)
    
    }
    

     

     

    以上です。

    最後までご覧いただきありがとうございます。

     

    つぎはこちら

    Googleシート 複数のNGワードを的確に抽出する方法
  • REGEXMATCH関数の使用方法がわかります。
  • TRANSPOSE()、ArrayFormula()の効果的な使用方法がわかります。
  •  

    質問対応の一覧

    コメント

    1. TK より:

      会社でスプレットシートを利用しているため、とても参考にさせていただいております。
      「NGワードを表示させる」を参考にさせていただき、NGワードの抽出を行うことができ、とても参考になりました。

      もし、可能であればぜひ動画を拝聴したいのですが、セルの中に文章を入れた際に、複数のNGワードが隣のセルに表示されるような仕組みを作ることは可能なのでしょうか。試しに作ってみたところ、一つの内容は表示されるのですが、NGワードが複数混じっていた場合はそのうちの一つしか表示することができませんでした。

      差し支えなければご紹介いただけると幸いです。

      • ひろし より:

        差し支えなければ、実際に作られたシートやGAS(?)を
        ご提供いただけないでしょうか?
        そうしていただけると、話が早いです。

        • TK より:

          ご返信ありがとうございます。
          お伝えの仕方が足りずすみません。

          文章を取り扱う仕事をしており、文章のチェックの利用時に、シートもGasもご紹介されているNGワードを表示させるの内容が使えそうだと思い全く同じ内容で転用させていただきました。

          GASなどプログラムを組めるほどの知識はないため、恥ずかしながらそのままの転用させていただいている状況です。

          セルが文章かつ複数のNGワードがあった場合、ご紹介いただいているGASだとNGワードのうちの一つが表示されるかと思うのですが、文章の中に複数のNGワードが含まれていた場合にどのNGワードが含まれているのか一斉に出てくるとさらに良いなと思い、ご相談させていただきました。

          現状の形式でも複数NGワードが含まれていた場合も、表示されたNGワードから修正していけばチェック機能として運用できるのですが、複数入っていた場合も列に文章を入れたセルのとなりの列に全てでできたらよいなと思った次第でした。

          このたびはご丁寧にご返信いただき恐れ入ります。

          • hiroshi より:

            TKさま
            返信ありがとうございます。
            で、問題となるシートのご提供は、難しいのでしょうか?

            私が、TKさまの悩みをシートに起こすのは骨が折れます。

            TKさまがシートを元に
            具体的に、
            「A2がこの場合、B3こうなってほしい」
            と言った、明確な要望をしていただけると
            回答できるかもしれません。

      • HIROSHI より:

        ちょっと意図しているものと異なるかもしれませんが
        やってみました。
        https://youtu.be/GjmE73gGaq4
        NGであれば、また、連絡ください。

    2. MS より:

      いつも学ばせていただいております。社内での実装を検討しているのですが、同一スプレッドシートのシートからNGワードを取得するではなく、別のスプレッドシートからNGワードの値を取得し、NGワードを判別したいスプレッドシートでそれを読み込んで表示させる事は可能なのでしょうか?

      openByidでよみこんだり、ライブラリを試してみたのですが、上手くいかず悩んでおりました、、、

      • hiroshi より:

        一気にやろうとせず、
        一旦展開して、
        「同一スプレッドシートのシートからNGワードを取得する」
        状態にするのは、いかがですか?

        • MS より:

          コメントありがとうございます!

          NGワードをオーナー以外分からない状態にしたいみたいで、同一スプレッドシートだと厳しいみたいなのですよね、、

          • hiroshi より:

            なるほど、であればNGワードの取得を別シートから
            読み込むようにすればいいと思います。

            NGシートが決め打ちでいいのであれば
            JudgeNGWordRange()内で、そのNGシードのIDを埋め込んでNgBuffに
            読み込んでしまえばいけるのでは。

            • MS より:

              何度もありがとうございます!!

              営業の成約した所をNGとしたいため、順次NGワードを更新していきたいと考えております。

              この場合でも同様のやり方で実現可能なのでしょうか?

    3. hiroshi より:

      MS :

      何度もありがとうございます!!

      営業の成約した所をNGとしたいため、順次NGワードを更新していきたいと考えております。

      この場合でも同様のやり方で実現可能なのでしょうか?

      ぜひ、やってみてください!

    タイトルとURLをコピーしました