【Google Apps Scriptとスプレッドシートで在庫管理】 入力された文字を別シートにて検索し、その隣にある文字を書き出す

#はじめに
タイトルを読んでも意味不明だと思うので、まずはこちらの動画を見て欲しい。
<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">【Google Apps Scriptで業務効率化】<br>入力された文字を別シートにて検索して、その隣にある文字を書き出す！ <a href="https://t.co/HGpJIlXw9J">pic.twitter.com/HGpJIlXw9J</a></p>&mdash; げん げんと (@gento34165638) <a href="https://twitter.com/gento34165638/status/1272865111196069888?ref_src=twsrc%5Etfw">June 16, 2020</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

これで大体何を実現したいのか、お分りいただけたかと思う。。:sweat_smile:

#経緯
僕のパートナーが趣味でアクセサリーと作っているのだが、ある日**「出来上がったアイテムの原価を知りたい！」**というご注文を頂いた。。。

保有する全ての材料は、ご丁寧にスプレッドシートにまとめられているようだ。

`使った材料１つあたりの金額　× 使った個数　＝　出来上がったアイテムの原価`
をスプレッドシートを使って求めたいようだが、、、、

**「使った材料１つあたりの金額」**を、在庫一覧のシートからいちいちコピペするのがめんどくさい！ということで、

####「プログラミングのスキルは、一番身近な人の役に立ってこそだろう！」と思い、この僕が立ち上がったのである。

#用意するもの
##在庫をまとめたシート
<img width="647" alt="スクリーンショット 2020-06-16 21.22.13.jpg" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/167220/077a8d63-ffac-f599-b9bc-87128bef307d.jpeg">

##作ったアイテムをまとめたシート
<img width="742" alt="スクリーンショット 2020-06-16 21.37.07.jpg" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/167220/351f8ed2-e590-e9e7-ded1-6ccf89d7636c.jpeg">

#全体の流れ
①「作ったアイテム」のシートで入力された材料名を`onEdit(e)`で取得
②①で取得した材料名を、同じ**スプレッドシート内の「在庫」シートにて検索**
③「在庫」シートにて検索したセルの、１つ横のセルの値**（これが「材料１つあたりの金額」）**を取得
④①で入力されたセルの１つ横のセルに、③で取得した「材料１つあたりの金額」を書き出す

大体の処理の流れはこんな感じ。

#全てのコード

```javascript:コード.gs
function onEdit(e) {
  var range = e.range;
  var value = e.value;
  
    // 編集のあったシート
  var sheet = e.source.getActiveSheet();
     // 編集したセル
  var cell = e.source.getActiveRange();
   
     // シート「作ったアイテム」の2列目のセル（材料名）が編集されたときのみ実行される
     if (sheet.getName() == "作ったアイテム" && cell.getColumn() == 2) {
    
     const price = findText(value);
    if(price){
      cell.offset(0, 1).setValue(price);
    }
  }
}

function findText(newValue) {
  var targetCol = 1;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
    //シート「在庫」を取得
  var sheet = ss.getSheetByName('在庫');
    
    try{
        //シート「作ったアイテム」で入力された文字列を探す
        var textFinder = sheet.createTextFinder(newValue);
        var ranges = textFinder.findAll();
        //入力された文字のセル範囲を取得
        var textPos = ranges[0].getA1Notation();
        //入力された文字の位置を取る
        var textPosCell = sheet.getRange(textPos);
        //入力された文字の右隣のセルの値を取る
        var targetValue = textPosCell.offset(0, 1).getValue();
        return targetValue;
  
      }catch(e){
        result = "エラーの内容:" + e;
        Logger.log(result);
      }
 
}
```

#軽く解説
##スプレッドシートに編集があった時、発動する
```javascript
function onEdit(e)
```

この関数でスプレッドシートに編集があった時、発動するようにしておく。
ただし、このままだと全てのセルに編集があった時に発動してしまうので、このように条件分岐しておく。

```javascript
// 編集のあったシート
  var sheet = e.source.getActiveSheet();
     // 編集したセル
  var cell = e.source.getActiveRange();

     // シート「作ったアイテム」の2列目のセル（材料名）が編集されたときのみ実行される
     if (sheet.getName() == "作ったアイテム" && cell.getColumn() == 2) {

     const price = findText(value);
    if(price){
      cell.offset(0, 1).setValue(price);
    }
  }
```

##createTextFinderで単語を検索
この関数についてはこちらの記事で詳しく解説されてます:point_down:
[Google Apps Script でスプレッドシート内の文字列を検索する TextFinder を試してみる](https://qiita.com/wezardnet/items/210eafa0530ec0c4a2c7)

なぜ`ranges[0]`となるのか？

```javascript
var ranges = textFinder.findAll();
var textPos = ranges[0].getA1Notation();
```

それは`findAll()`で探している為、検索にかけた単語が複数見つかる事もあり得る。なので、`ranges`は配列みたいなものという事になる。





#最後に
僕はスプレッドシートが特になってきました。

**追記**
続きを書きました:point_down:
[【Google Apps Scriptとスプレッドシートで在庫管理】選択範囲の中で取得した値を使って、別シートで計算する](https://qiita.com/kokogento/items/a2aa79525b0b150c816f)