Google Workspace割引クーポン無料配布中! 詳しくはこちら

スプレッドシートとGASを使って設備データベースを作る

  • URLをコピーしました!

こんにちは。ごすけです。

今回はスプレッドシートをデータベースとして活用した会社の「設備台帳」シートをご紹介します。

私の務めている会社で使っているものをベースに作成しました。

作ったスプレッドシート無料公開しています。

項目を変えれば設備台帳だけでなく、顧客管理従業員情報のデータベースとしても活用できるのではと思っています。

それではさっそく参りましょう!

この記事の内容
  • スプレッドシートで台帳管理をしたい
  • スプレッドシートの活用方法
  • Google Apps Scriptのコード解説

GAS初心者の方はこちらの記事から読むのがおすすめです。

この記事よりもとてもシンプルなGASを紹介しています。

タップできる目次
スポンサー

利用に関する注意事項

商用・非商用に関係なく無料でご利用頂くことが出来ますが、再配布や販売は禁止とさせていただいています。

ダウンロード後のファイルはご自由にお使いくだいさい。改変されてもチーム内で共有されても大丈夫です。

ただし、再配布などの行為を見つけてしまうと私としても嫌な気持ちになります。心無い行為だけはどうかお辞めください。

すべてのファイルは私自身のMac・iPad・iPhoneで最新のOSのもとで使っています。常に動作確認済のものを配布していますが、万一ファイルのご利用時に発生した損害等の責任を負うことはできません。

ご利用はあくまでも自己責任で、何卒ご理解の程お願いいたします。

一般公開しているスプレッドシート・GASのダウンロード方法に関してはこちらの記事を御覧ください。

スプレッドシートの準備

さっそく実装した内容の紹介です。

まずはシートを4つ用意しました。

  1. 新規登録 … データの登録シートです
  2. 抽出・更新 … 登録されているデータを抽出し、更新します
  3. 設備台帳 … 登録された設備のデータベースです
  4. リスト … 項目が決まっているものを誤って入力しないようリスト化しています。GASはありません。

スプレッドシートはこちら▼

Google Docs
設備台帳 新規登録 新規登録 登録設備,※設備No.は自動で登録 項目,記入欄,▼同じ設備名がある場合は候補が表示される 設備名,冷凍,冷凍装置,A 枝番,冷凍装置,B 導入日,冷凍装置,C 取...

コードの紹介

それではコードの紹介です。

ライブラリのUnderscoreを使用しているので、このままコピペしても動きません

使い方はポイント解説で紹介します。

設備データの新規登録

新規登録シートの動作です。

図形「設備登録」にスクリプトを割り当てています。

新規登録
function NewMachineRegister(){
  //スプレッドシートID
  var SpreadSheet = SpreadsheetApp.openById("1bH7ygAWlo5ufzSG99cxKzK8R8ouEq4lukNYyqPWBfkc");
  //シート名
  var SheetName = SpreadSheet.getSheetByName('新規登録');
  //登録する設備データを取得
  var SetRange = SheetName.getRange('B5:B26');

  //登録先のシート名
  var CopySheetName = SpreadSheet.getSheetByName('設備台帳');
  //登録先のデータの最終行を取得
  var LastRow = CopySheetName.getLastRow();
  //設備データを登録する
  SetRange.copyTo(CopySheetName.getRange(LastRow+1,3,1,24), SpreadsheetApp.CopyPasteType.PASTE_NO_BORDERS, true);

  //オリジナル番号を振る    
CopySheetName.getRange(LastRow+1,2).setFormula('=if(indirect(address(row(),3))="","","KM"&text(counta(indirect(address(2,3)):indirect(address(row(),3))),"0000"))');

}

設備データの抽出

次は設備データの抽出になります。

設備No.をキーとして、該当するデータが抽出されます。

図形「設備データ抽出」にスクリプトを割り当てています。

//データの抽出
function DateUpdate(){

  //スプレッドシートID
  let originaldb = SpreadsheetApp.openById("1bH7ygAWlo5ufzSG99cxKzK8R8ouEq4lukNYyqPWBfkc");     
  //更新するシート名
  let originaldb_sheet = originaldb.getSheetByName("設備台帳");                                   
  //コピー元のシート名(copydb)
  let copydb_sheet = originaldb.getSheetByName("抽出・更新");                                           

  // キーワードの値取得
  let keyword_value = copydb_sheet.getRange("B3").getValue();
  //コピー先のスプレッドシート(OriginalDB)の全ての値を配列に格納
  let data = originaldb_sheet.getRange('B:X').getValues();
  //コピーする対象のキーワード
  let keyword_list = [keyword_value];
  //コピーするキーワードを含む列のデータ格納用配列
  let paste_list = [];

  //キーワード検索
  for (let i = 0; i < keyword_list.length ; i++){
    for (let j = 1; j < data.length ; j++){

      let keyword = data[j][0];                             
      //キーワードは0列(B列)に含まれている
      if(keyword === keyword_list[i]){
        //対象のキーワードの列の場合のみ、配列に格納する
        paste_list.push(data[j]);
      } 
    }
  }

  //ライブラリをロードする
  const _ = Underscore.load();
  //2次元配列の行と列を入れ替える
  const newArray = _.zip.apply(_, paste_list); 
  //コピー先のスプレッドシートのデータを全て削除しておく
  copydb_sheet.getRange(7, 2, newArray.length,1).clear();
  //行列を入れ替えたデータを貼り付ける
  copydb_sheet.getRange(7, 2, newArray.length,1).setValues(newArray);
  //書式を整える(枠追加、左寄り)
  copydb_sheet.getRange(7, 2, newArray.length,1).setBorder(true,true,true,true,true,true).setHorizontalAlignment('left');

}

設備データの更新

最後はデータの更新ボタンです。

設備は改造・修理・バージョンアップが発生するので更新できるようにしました。

図形「設備データ更新」にスクリプトを割り当てています。

//設備台帳の更新
function input_value(){
  
  //スプレッドシートIDとシート名
  var sh2 = SpreadsheetApp.openById("1bH7ygAWlo5ufzSG99cxKzK8R8ouEq4lukNYyqPWBfkc").getSheetByName("抽出・更新");
  //更新する値を取得する
  var sh2Value = sh2.getRange('B8:B29')
  //検索キーを取得 ※今回は設備No.
  var key = sh2.getRange("B3").getValue();

  //検索するシート名
  var sh = SpreadsheetApp.openById("1bH7ygAWlo5ufzSG99cxKzK8R8ouEq4lukNYyqPWBfkc").getSheetByName("設備台帳");
  //検索する列
  var col = "B";
  //転置して貼り付け
  var row = get_row(key, col, sh);
  sh2Value.copyTo(sh.getRange(row,3,1,24), SpreadsheetApp.CopyPasteType.PASTE_NO_BORDERS, true);

}

//設備台帳のシートと列をget_arrayに渡して値を配列
function get_row(key, col, sh){
  //受け取ったシートと列をget_arrayに渡して値を配列で取得
  var array = get_array(sh, col);
  //そのなかでキーワードが南岸目にあるか見つけて
  var row = array.indexOf(key) + 1;
  //返す
  return row;
}

function get_array(sh, col) {
  //受け取ったシートでデータが入力されている最終行を取得
  var last_row = sh.getLastRow();
  //受け取った列の1行目から最終行を選択
  var range = sh.getRange(col + "1:" + col + last_row)
  //値をすべて取得
  var values = range.getValues();
  //配列の入れ物を用意
  var array = [];
  //値の数だけ以下を繰り返す
  for(var i = 0; i < values.length; i++){
    //arrayに一つずつ値を入れて
    array.push(values[i][0]);
  }
  //全部入った配列を返す
  return array;
}

ポイント解説

CopyPasteTypeで転置して貼り付ける

設備データの新規登録・データ書き換えは転置して貼り付けを実行しました。

▼参考サイト▼

くらぶろぐ
GASでスプレッドシートの指定範囲をコピーする方法 GASでスプレッドシートの指定範囲をコピーする方法を紹介します!

Underscoreライブラリを使って値の行列を入れ替える

データの抽出は苦戦しました…。

サイトを参考にしながら

  • 設備台帳のデータをすべて配列に格納
  • 該当するキーワードを含む列を格納

の2段階で作っています。

そしてシートへの書き込みはUnderscoreライブラリを追加して2次元配列の行と列を入れ替えました。

1行を入れ替えずにそのまま抽出しても良かったのですが、縦にしてあげたほうが見やすいですよね…。

▼参考サイト

いつも隣にITのお仕事
Google Apps Scriptで二次元配列の行と列を入れ替える方法とその革命的な効果 Google Apps Scriptでスプレッドシートのデータは二次元配列として操作できますが、列方向に検索したいときなどはUnderscoreのzipメソッドで行と列を入れ替えると革命的に...
あわせて読みたい
列内で特定の値に一致する行番号を取得する 日々Google Apps Scriptを書く中で、気づいたことや作ったものなどを更新しています。

Googleフォームを使わなかった理由

Googleを使っているのであればGoogleフォームとスプレッドシートを使った登録方法が有名だと思います。

ただこの方法だとデータを更新する際に直接データベースとなっているシートを変更する必要があります。

これだと別のデータを誤って変更してしまいますよね。

データが増えていけばこのリスクはさらに上がっていきます。

メンテナンスを考慮して、今回はGASのみで実装してみました。

このプログラムのイマイチな点

自分では形になっていると思うのですが、これをやりたかったと思う機能が3つあります。

1つ目は検索機能を実装できなかったことです。

スプレッドシートのFilter関数を使って候補は出るようにしましたが、

本当はネットショッピングのようにキーワードを入れると候補一覧が表示されて

クリックすると詳細が確認できる…といった機能を実装したかったですね。

2つ目がif文を使っていないので赤字で怖い文字がでます…苦笑

if文入れてない。これだと知らない人が使ってしまうと慌ててしまう…

そして最後3つ目が、色んなサイトを見ながら実装したのでconstやletやvarを乱用して統一性がありません

自分が使うからとりあえず良いかと思いつつ、人に見てもらうコードであれば良くないですよね。

このシートの必要性が出た時に直そうと思います(^^;)

まとめ

今回はスプレッドシートとGASを使って設備台帳を作成し、データの登録や更新をできるようにしました。

少し中身を変えていただければ顧客リストなどといった変更する可能性があるデータベースに応用できないかとも考えています。

人数も少ない会社なのでプログラムを使って、効率化を勧めていけるよう精進して参ります。

今西さんのUdemy講座

UdemyのGAS関連で最も視聴されている教材です。

私が一番最初にGASを学ぶために購入した動画でもあります。

基礎からGASを学びたいのであれば絶対に外せない1つだと思います。

自身も月2〜3回で開催するセールを活用して90%オフで購入しました。

間違ってもセールを開催していない時に購入しないでください

Google Apps Scriptおすすめ書籍

Google Apps Scriptを勉強するなら以下の書籍がおすすめです。

私も実際に購入して読み返しています。

【小言】

エクセルみたいにユーザーフォームを使えないかな…

そんなことを考えていた時に、

HTMLを使ってスプレッドシートをデータベース化しているツワモノを見つけました!!

Web制作の勉強もしていたので、スキルを活かせそうと少しワクワクしました。

次はこれやりたいと思っています!

参考サイト▼

まさきのエンジニア図書館
Google Apps Scriptでフォームを作ってスプレッドシートに登録する方法 今回はGoogleAppsScriptを使ってフォームを画面を作成し、入力内容をGoogleスプレッドシートに登録して管理する方法をご紹介します。

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!
  • URLをコピーしました!
タップできる目次