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

12009 ワード
google JavaScript GoogleAppsScript GoogleSpreadSheet JavaScript テキストリンク

はじめに

タイトルを読んでも意味不明だと思うので、まずはこちらの動画を見て欲しい。

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

経緯

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

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

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

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

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

用意するもの

在庫をまとめたシート

作ったアイテムをまとめたシート

全体の流れ

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

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

全てのコード

コード.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);
      }

}

軽く解説

スプレッドシートに編集があった時、発動する

function onEdit(e)

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

// 編集のあったシート
  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で単語を検索

この関数についてはこちらの記事で詳しく解説されてます
Google Apps Script でスプレッドシート内の文字列を検索する TextFinder を試してみる

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

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

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

最後に

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

追記
続きを書きました
【Google Apps Scriptとスプレッドシートで在庫管理】選択範囲の中で取得した値を使って、別シートで計算する