GASでDB！スプレッドシートをデータベースとして使う方法【無料・ソースコードコピペOK】

今回は、スプレッドシートをデータベースのように利用する方法をご紹介。

この記事の内容をマスターすればあなたの情報管理能力をアップできるでしょう。

スプレッドシートなんて使ったことないという方でもわかりやすいように手順をステップごとに細かく解説。

また、完成品のスプレッドシートも提供しますので誰でも簡単にスプレッドシートをデータベースとして使い始められます。

ぜひ最後までご覧になってください。

＼ゲームで学ぶプログラミング／
独学でプログラミングを学んで開発者に！

→ Unity入門の森オリジナル本格ゲーム制作講座はこちら
本格ゲームの全ソースコード公開・画像＆動画による解説付き

スプレッドシートとデータベースの違い
- Google Apps Scriptを利用すればスプレッドシートをデータベースとして利用可能
Google Apps Script（GAS）とは
実践：入出荷管理システムをつくってみよう！
まとめ

スプレッドシートとデータベースの違い

スプレッドシートとデータベースの主な違いは、以下。

スプレッドシート
スプレッドシートは、表形式でデータを表示し、列と行を使用して構造化します。
データの取り扱いは比較的柔軟で、数式や関数を使ってデータを操作できます。通常、小規模なデータセットや計算が必要なデータに適しています。
データベース
データベースは、スプレッドシートに比べてより複雑で構造化された方法でデータを保存します。
テーブルと呼ばれる形式でデータを保存し、それぞれのテーブルは特定の列とそれに関連するデータ行で構成されます。
またリレーションという概念でデータ上で重複したデータが発生しないように連携して１つのデータを保管します。

総括すると、スプレッドシートは小規模なデータや単純な計算に適しており、データベースは、データを整理して管理することを得意としています。

Google Apps Scriptを利用すればスプレッドシートをデータベースとして利用可能

データの整理が得意なデータベースですが利用方法は非常に複雑で初心者には手が出ません。

スプレッドシートをデータベースのようなデータの整理に特化したシステムとして扱う方法としてGoogle Apps Scriptを利用する方法があります。

Google Apps Script（GAS）とは

GAS（Google Apps Script）は、Google Workspaceの機能を拡張し、カスタマイズするためのスクリプト言語です。

基本的な使い方や構文を理解することで、スプレッドシートをデータベースのように活用できます。

GASを利用するメリット、特徴は、以下になります。

Googleサービスとの連携が容易

GASは、Googleによって開発されているため他のGoogleサービス（スプレッドシートやGmail、Googleカレンダーなど）との連携が簡単に行えます。

Googleサービスの機能拡張を図りたい方やGoogleサービスを連携したい方にはおすすめの言語です。

コンパイル不要なため即座に変更反映が可能

GASは、スクリプト言語と呼ばれるコンパイルをせずに即時実行、展開ができる言語です。

Javaなどのコンパイル言語では、コンパイルと呼ばれるソースコードをアプリに変換する処理が必要ですがGASでは不要。

気軽に処理をカスタマイズして反映することができます。

定期実行やセキュリティ設定が簡単

GASは、トリガーと呼ばれるスケジュール実行機能を提供しています。

トリガーを利用すれば、定期的に自動実行するように設定できます。GAS画面の「トリガー」設定から簡単にスケジュールを組むことが可能です。

画面上で簡単な設定を行うだけで設定が可能。

また、セキュリティは、権限設定が細かく設けられており指定したユーザーや処理だけ許可するなどの設定が簡単に行えます。

スプレッドシートをデータベース化するために必要な機能

今回、スプレッドシートをデータベースとして利用するために以下の機能を実装します。

データの追加・更新機能

スプレッドシートに新しいデータを追加したり、既存のデータを更新する機能。

シート上に必要項目を入力し登録ボタンを押すことで対応するシートへデータを登録します。

データの自動更新機能

登録されたデータに応じて情報を自動的に更新します。

入力シートに登録されている内容に応じて情報を更新する機能を実装します。

データの検索機能

データベースは、自由にデータを検索して抽出できる必要があります。

今回は、登録されている情報をキーワードにて検索できる機能を実装します。

GASとは？何ができるの？初心者向け解説：Google Apps Scriptの基本と活用法

実践：入出荷管理システムをつくってみよう！

ここからは、サンプルとしてデータベースシステムとして利用するために必要とお伝えした先ほどの機能がすべて搭載されているシステムを開発していきます。

開発するシステムは、商品の入荷、出荷を管理するための入出荷管理アプリ。

機能としては、以下になります。

商品登録機能新商品を商品リストに追加します。商品は、管理をしやすくするために個別に管理番号と商品の種別を登録できます。管理番号は、自動的に連番で登録されます。
入出荷登録機能商品リストに記載されている商品の入荷、出荷情報を登録します。登録情報をもとに商品の在庫数を更新します。また、登録した入出荷情報は、日時を含めて履歴として格納します。これでいつどの商品が何個、入出荷したかが管理できます。
商品照会機能登録されている商品の在庫数を表示します。検索は、商品名で検索が行えます。

事前準備：在庫管理用スプレッドシート作成

在庫管理システムを作成するためのスプレッドシートを作成します。

スプレッドシートの開き方はこちらを参考にしてください。

【GAS作成用】スプレッドシートの始め方

今回作成するスプレッドシートのシート名とそれぞれのシートデザインは以下。

入出荷登録シート

最大10件まで登録できるようにします。

画像のようなレイアウトで表を作成します。

項目は以下の通りです。

商品名

入庫種別

数量

入出荷種別は、プルダウンを利用しましょう。

プルダウンは、セルを右クリック、プルダウンで設定できます。

選択項目は、「入荷」、「出荷」を設定します。

わかりやすいように色を分けておきましょう。

今回は入荷を赤、出荷を青にしています

プルダウンを10までコピーしておきましょう。

登録実行用のボタンは、「挿入」→「図形描画」の順でクリックして図形作成します。

「図形」→「ベベル(面取り)」を選びます。

図形をクリックすると文字が入力できるので「登録」と入力します。

シート名は「入出荷登録シート」にしておきましょう。

商品登録シート

商品登録用フォームを画像のようなデザインで作成します。

項目は以下のとおり。

項目	設定内容
商品名
種別

シート名を「商品登録シート」にしておきましょう。

商品照会シート

2行目に検索エリア、5行目以降を検索結果表示エリアとします。

画像のように列名とボタンを設定します。

項目は以下の通り。

検索項目

商品名

リスト項目

管理番号

商品名

種別

数量

シート名は「商品照会シート」にしておきましょう。

履歴シート

履歴表示用の列名を画像の内容にて作成します。

項目は以下。

入出管理番号

管理番号

商品名

登録種別

数量

登録時間

シート名は「履歴シート」にしておきましょう。

在庫管理シート

在庫管理用の列名を画像の内容にて作成します。

項目は以下。

管理番号

商品名

種別

数量

シート名は「在庫管理シート」にしておきましょう。

種別管理シート

種別管理用の列名を画像の内容にて作成します。

今回種別は、固定値とするので事前に『果物』と『野菜』という商品種別を入力しておきます。

項目は以下。

種別番号	種別名
1	野菜
2	果物

シート名は「種別管理シート」にしておきましょう。

GASにて在庫管理システムを開発

ここからは、GASを利用してプログラムを作成していきます。

先程作成したGoogleスプレッドシートにて拡張機能-AppScriptをクリックします。

Google Apps Script画面に切り替わります。

まずは、簡単にAppsScript画面についてご説明します。

1.プロジェクト名

クリックすると変更できます。

2.スクリプトファイル

プロジェクトに配置されているスクリプトファイルを確認できます。

各項目横の+ボタンを押すことでファイルを新規で作成できます。

3.ツールバー

スクリプト作成時に必要な操作ボタンがまとめられています。

保存やデバッグ、実行ログ表示、操作を1つ戻るなど

4.サイドバー

GASの設定や統計情報などを確認する画面に切り替えるためのボタンがまとめられています。

5.エディターエリア

スクリプトファイルの編集を行うエリア

手始めにプロジェクト名を変更してみましょう。

今回は、在庫管理システムスクリプトなので「InventoryControlSystem」に変更しましょう。

プロジェクト名をクリックして名前を変更します。

続いてプログラムを記述していきます。

今回作成するプログラムの完成形は以下になります。

//********************************
//　基本設定情報
//********************************

//スプレッドシートのID
var SpreadSheetId = ''; // スプレッドシートIDは、URLの/d/から/editの間にある文字列をコピーして貼り付けてください。

//各シート名
var ArrivalShipping_SHEET_NAME = '入出荷登録シート';
var ItemRegister_SHEET_NAME = '商品登録シート';
var ItemInquiry_SHEET_NAME = '商品照会シート';
var History_SHEET_NAME = '履歴シート';
var ItemList_SHEET_NAME = '在庫管理シート';
var ItemType_SHEET_NAME = '種別管理シート';

//各シート情報を取得
var ss = SpreadsheetApp.openById(SpreadSheetId );
var ArrivalShipping_SHEET = ss.getSheetByName(ArrivalShipping_SHEET_NAME);
var ItemRegister_SHEET = ss.getSheetByName( ItemRegister_SHEET_NAME);
var ItemInquiry_SHEET = ss.getSheetByName(ItemInquiry_SHEET_NAME);
var History_SHEET = ss.getSheetByName(History_SHEET_NAME);
var ItemList_SHEET = ss.getSheetByName(ItemList_SHEET_NAME);
var ItemType_SHEET = ss.getSheetByName(ItemType_SHEET_NAME);

//********************************
//　商品登録処理
//********************************

function ItemRegister () {
  // 登録情報を取得
  let item_name =  ItemRegister_SHEET.getRange(2,2).getValue();
  let item_type =  ItemRegister_SHEET.getRange(3,2).getValue();
  let itme_last_row = ItemList_SHEET.getLastRow();
  let type_last_row = ItemType_SHEET.getLastRow();
  let item_type_code;
  // チェック用のフラグ変数を作成
  let cancel_flag = false;
  let itemtype_flag = false;
  // 種別登録済みチェック
  for(let i= 2; i<=type_last_row; i++){
       if(ItemType_SHEET.getRange(i,2).getValue() == item_type){
      item_type_code =ItemType_SHEET.getRange(i,1).getValue()
      itemtype_flag = true;
    }
  }

  // 重複登録チェック処理
  // 商品管理シートの２行目（１行目は、列名なのでスキップ）から最終行までをループ
  for(let i=2; i<=itme_last_row; i++){
    // 商品名が一致する商品があればすでに登録済みの商品があるということなので処理を中断するようにフラグを立てる
    if(ItemList_SHEET.getRange(i,2).getValue() == item_name){
      cancel_flag = true;
    }
  }

  if(cancel_flag == false && itemtype_flag == true){
    if(itme_last_row==1){
      ItemList_SHEET.getRange(itme_last_row+1,1).setValue(itme_last_row+1);
    }else{
        var sheet = ItemList_SHEET;
        //降順用配列
        var arrayDataDesc = sheet.getRange(2, 1,sheet.getLastRow()).getValues();
        //数値(Number)フィルタ
        var arrayDataDesc = arrayDataDesc.filter(Number);
        //降順に並べ替え
        var arraySortedDesc = arrayDataDesc.sort(function(a,b){return b-a});
        //降順に並べ替えた配列の先頭 = 最大値
        var max =  arraySortedDesc[0];
        ItemList_SHEET.getRange(itme_last_row+1,1).setValue(Number(max)+1);
    }
    // 商品を登録
    ItemList_SHEET.getRange(itme_last_row+1,2).setValue(item_name);
    ItemList_SHEET.getRange(itme_last_row+1,3).setValue(item_type_code);
    ItemList_SHEET.getRange(itme_last_row+1,4).setValue(0);
    Browser.msgBox("商品名：『"+item_name+"』"+"種別：『"+item_type+"』を追加しました。");
  }else if(cancel_flag == true){
    Browser.msgBox("商品名：『"+item_name+"』"+"種別：『"+item_type+"』は、すでに登録されています。");   
  }else if(itemtype_flag == false){
    Browser.msgBox("種別：『"+item_type+"』は、種別管理シートに未登録の種別です。");   
  }
}

//********************************
//入出荷登録処理
//********************************

function ArrivalShipping(){
    let arrivalShipping_last_row = ArrivalShipping_SHEET.getLastRow();
    let max = 0;
    let cancel_flag = true;
    let missMatchlist =[];
    let missMatchlistCount = 0;
    let message;
    for(let i=2; i<=arrivalShipping_last_row; i++){//入出荷登録商品取得ループ
      // 入出荷したい商品情報を取得
      let item_match_flag = false;  
      let itemList_last_row = ItemList_SHEET.getLastRow();     
      let set_item_name = ArrivalShipping_SHEET.getRange(i,2).getValue();
      let set_item_type = ArrivalShipping_SHEET.getRange(i,3).getValue();
      let set_item_count = ArrivalShipping_SHEET.getRange(i,4).getValue();
      if(ArrivalShipping_SHEET.getRange(i,2).getValue() != ""){//登録対象があるかチェック
        for(let l=2; l<=itemList_last_row; l++){//登録資材ループ　登録資材に入出荷したい資材があるかチェック
          let item_name=ItemList_SHEET.getRange(l,2).getValue();
          var item_code=ItemList_SHEET.getRange(l,1).getValue();
          if(item_name == set_item_name){
            // 数量変更処理
            let item_count = ItemList_SHEET.getRange(l,4).getValue();
            let sum_item_count;
            if(set_item_type == '入荷'){
              sum_item_count = item_count + set_item_count;
            }else if (set_item_type == '出荷'){
              sum_item_count =  item_count - set_item_count;
            }
            ItemList_SHEET.getRange(l,4).setValue(sum_item_count);
            item_match_flag = true;
            break;
          }
        }
        if(item_match_flag == true){
          let history_last_row = History_SHEET.getLastRow();
          if(history_last_row == 1){
            max = "1";
          }else{
            var sheet = History_SHEET;
            //降順用配列
            var arrayDataDesc = sheet.getRange(2, 1,sheet.getLastRow()).getValues();
                //数値(Number)フィルタ
            var arrayDataDesc = arrayDataDesc.filter(Number);
            //降順に並べ替え
            var arraySortedDesc = arrayDataDesc.sort(function(a,b){return b-a});
            //降順に並べ替えた配列の先頭 = 最大値
            max =  arraySortedDesc[0]; 
          }
          History_SHEET.getRange(history_last_row+1,1).setValue(Number(max)+1);
          History_SHEET.getRange(history_last_row+1,2).setValue(item_code);
          History_SHEET.getRange(history_last_row+1,3).setValue(set_item_name);
          History_SHEET.getRange(history_last_row+1,4).setValue(set_item_type);
          History_SHEET.getRange(history_last_row+1,5).setValue(set_item_count);
          var date = new Date();
          History_SHEET.getRange(history_last_row+1,6).setValue(Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy-MM-dd: HH:mm:ss'));
          cancel_flag = false;
        }else{
          missMatchlistCount = missMatchlist.push(set_item_name);
        }
      }
    }
    if(cancel_flag == true){
      Browser.msgBox("入出荷が１件も入力されていません");   
    }else if(missMatchlistCount > 0){
      for(let i=0; i< missMatchlistCount; i++){
        if(i == 0){
          message = missMatchlist[i];
        }else{
          message = message + ',' + missMatchlist[i];
        }
      }
      Browser.msgBox("入出荷情報を登録しましたが次の商品は、未登録商品です。"+message);   
    }else{
      Browser.msgBox("入出荷情報を登録しました。");   

    }
  
}

//********************************
//　商品検索処理
//********************************

function Search_Item(){
  // 検索情報を取得
  let item_name =  ItemInquiry_SHEET.getRange(2,2).getValue();
  ItemInquiry_SHEET.getRange(6,1).setValue('=QUERY('+ItemList_SHEET_NAME+'!A:D,"WHERE B = \'' + item_name + '\' ",0)');
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

//********************************

//　基本設定情報

//********************************

//スプレッドシートのID

var SpreadSheetId = ''; // スプレッドシートIDは、URLの/d/から/editの間にある文字列をコピーして貼り付けてください。

//各シート名

var ArrivalShipping_SHEET_NAME = '入出荷登録シート';

var ItemRegister_SHEET_NAME = '商品登録シート';

var ItemInquiry_SHEET_NAME = '商品照会シート';

var History_SHEET_NAME = '履歴シート';

var ItemList_SHEET_NAME = '在庫管理シート';

var ItemType_SHEET_NAME = '種別管理シート';

//各シート情報を取得

var ss = SpreadsheetApp.openById(SpreadSheetId );

var ArrivalShipping_SHEET = ss.getSheetByName(ArrivalShipping_SHEET_NAME);

var ItemRegister_SHEET = ss.getSheetByName( ItemRegister_SHEET_NAME);

var ItemInquiry_SHEET = ss.getSheetByName(ItemInquiry_SHEET_NAME);

var History_SHEET = ss.getSheetByName(History_SHEET_NAME);

var ItemList_SHEET = ss.getSheetByName(ItemList_SHEET_NAME);

var ItemType_SHEET = ss.getSheetByName(ItemType_SHEET_NAME);

//********************************

//　商品登録処理

//********************************

function ItemRegister () {

// 登録情報を取得

let item_name = ItemRegister_SHEET.getRange(2,2).getValue();

let item_type = ItemRegister_SHEET.getRange(3,2).getValue();

let itme_last_row = ItemList_SHEET.getLastRow();

let type_last_row = ItemType_SHEET.getLastRow();

let item_type_code;

// チェック用のフラグ変数を作成

let cancel_flag = false;

let itemtype_flag = false;

// 種別登録済みチェック

for(let i= 2; i<=type_last_row; i++){

if(ItemType_SHEET.getRange(i,2).getValue() == item_type){

item_type_code =ItemType_SHEET.getRange(i,1).getValue()

itemtype_flag = true;

}

// 重複登録チェック処理

// 商品管理シートの２行目（１行目は、列名なのでスキップ）から最終行までをループ

for(let i=2; i<=itme_last_row; i++){

// 商品名が一致する商品があればすでに登録済みの商品があるということなので処理を中断するようにフラグを立てる

if(ItemList_SHEET.getRange(i,2).getValue() == item_name){

cancel_flag = true;

}

if(cancel_flag == false && itemtype_flag == true){

if(itme_last_row==1){

ItemList_SHEET.getRange(itme_last_row+1,1).setValue(itme_last_row+1);

}else{

var sheet = ItemList_SHEET;

//降順用配列

var arrayDataDesc = sheet.getRange(2, 1,sheet.getLastRow()).getValues();

//数値(Number)フィルタ

var arrayDataDesc = arrayDataDesc.filter(Number);

//降順に並べ替え

var arraySortedDesc = arrayDataDesc.sort(function(a,b){return b-a});

//降順に並べ替えた配列の先頭 = 最大値

var max = arraySortedDesc[0];

ItemList_SHEET.getRange(itme_last_row+1,1).setValue(Number(max)+1);

}

// 商品を登録

ItemList_SHEET.getRange(itme_last_row+1,2).setValue(item_name);

ItemList_SHEET.getRange(itme_last_row+1,3).setValue(item_type_code);

ItemList_SHEET.getRange(itme_last_row+1,4).setValue(0);

Browser.msgBox("商品名：『"+item_name+"』"+"種別：『"+item_type+"』を追加しました。");

}else if(cancel_flag == true){

Browser.msgBox("商品名：『"+item_name+"』"+"種別：『"+item_type+"』は、すでに登録されています。");

}else if(itemtype_flag == false){

Browser.msgBox("種別：『"+item_type+"』は、種別管理シートに未登録の種別です。");

}

//********************************

//入出荷登録処理

//********************************

function ArrivalShipping(){

let arrivalShipping_last_row = ArrivalShipping_SHEET.getLastRow();

let max = 0;

let cancel_flag = true;

let missMatchlist =[];

let missMatchlistCount = 0;

let message;

for(let i=2; i<=arrivalShipping_last_row; i++){//入出荷登録商品取得ループ

// 入出荷したい商品情報を取得

let item_match_flag = false;

let itemList_last_row = ItemList_SHEET.getLastRow();

let set_item_name = ArrivalShipping_SHEET.getRange(i,2).getValue();

let set_item_type = ArrivalShipping_SHEET.getRange(i,3).getValue();

let set_item_count = ArrivalShipping_SHEET.getRange(i,4).getValue();

if(ArrivalShipping_SHEET.getRange(i,2).getValue() != ""){//登録対象があるかチェック

for(let l=2; l<=itemList_last_row; l++){//登録資材ループ　登録資材に入出荷したい資材があるかチェック

let item_name=ItemList_SHEET.getRange(l,2).getValue();

var item_code=ItemList_SHEET.getRange(l,1).getValue();

if(item_name == set_item_name){

// 数量変更処理

let item_count = ItemList_SHEET.getRange(l,4).getValue();

let sum_item_count;

if(set_item_type == '入荷'){

sum_item_count = item_count + set_item_count;

}else if (set_item_type == '出荷'){

sum_item_count = item_count - set_item_count;

}

ItemList_SHEET.getRange(l,4).setValue(sum_item_count);

item_match_flag = true;

break;

}

if(item_match_flag == true){

let history_last_row = History_SHEET.getLastRow();

if(history_last_row == 1){

max = "1";

}else{

var sheet = History_SHEET;

//降順用配列

var arrayDataDesc = sheet.getRange(2, 1,sheet.getLastRow()).getValues();

//数値(Number)フィルタ

var arrayDataDesc = arrayDataDesc.filter(Number);

//降順に並べ替え

var arraySortedDesc = arrayDataDesc.sort(function(a,b){return b-a});

//降順に並べ替えた配列の先頭 = 最大値

max = arraySortedDesc[0];

}

History_SHEET.getRange(history_last_row+1,1).setValue(Number(max)+1);

History_SHEET.getRange(history_last_row+1,2).setValue(item_code);

History_SHEET.getRange(history_last_row+1,3).setValue(set_item_name);

History_SHEET.getRange(history_last_row+1,4).setValue(set_item_type);

History_SHEET.getRange(history_last_row+1,5).setValue(set_item_count);

var date = new Date();

History_SHEET.getRange(history_last_row+1,6).setValue(Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy-MM-dd: HH:mm:ss'));

cancel_flag = false;

}else{

missMatchlistCount = missMatchlist.push(set_item_name);

}

if(cancel_flag == true){

Browser.msgBox("入出荷が１件も入力されていません");

}else if(missMatchlistCount > 0){

for(let i=0; i< missMatchlistCount; i++){

if(i == 0){

message = missMatchlist[i];

}else{

message = message + ',' + missMatchlist[i];

}

Browser.msgBox("入出荷情報を登録しましたが次の商品は、未登録商品です。"+message);

}else{

Browser.msgBox("入出荷情報を登録しました。");

}

//********************************

//　商品検索処理

//********************************

function Search_Item(){

// 検索情報を取得

let item_name = ItemInquiry_SHEET.getRange(2,2).getValue();

ItemInquiry_SHEET.getRange(6,1).setValue('=QUERY('+ItemList_SHEET_NAME+'!A:D,"WHERE B = \'' + item_name + '\' ",0)');

}

この内容をエディタエリアにコピペして、SpreadSheetIdを設定し保存すればすぐに動かす事ができます。

ただしボタンの割当などが必要になります。

ボタンのスクリプト割当は後半に解説します。

初回実行時は「承認が必要です」と出てくるので権限を確認し動くようにしてください。

詳しいやり方はこちら。

GAS実行時「承認が必要です」「このアプリはGoogleで確認されていません」の対策まとめ

ここから各処理事に処理内容を説明していきます。

設定情報箇所

最初にシステムで利用するスプレッドシートの情報を設定、取得しています。

エディタエリアに以下のコードを記入します。

//********************************
//　基本設定情報
//********************************

//　在庫管理スプレッドシートのID
var SpreadSheetId = ''

//　在庫管理スプレッドシートの各シート名
var ArrivalShipping_SHEET_NAME = '入出荷登録シート';
var ItemRegister_SHEET_NAME = '商品登録シート';
var ItemInquiry_SHEET_NAME = '商品照会シート';
var History_SHEET_NAME = '履歴シート';
var ItemList_SHEET_NAME = '在庫管理シート';
var ItemType_SHEET_NAME = '種別管理シート';

//　在庫管理スプレッドシートの各シート情報
var ss = SpreadsheetApp.openById(SpreadSheetId );
var ArrivalShipping_SHEET = ss.getSheetByName(ArrivalShipping_SHEET_NAME);
var ItemRegister_SHEET = ss.getSheetByName( ItemRegister_SHEET_NAME);
var ItemInquiry_SHEET = ss.getSheetByName(ItemInquiry_SHEET_NAME);
var History_SHEET = ss.getSheetByName(History_SHEET_NAME);
var ItemList_SHEET = ss.getSheetByName(ItemList_SHEET_NAME);
var ItemType_SHEET = ss.getSheetByName(ItemType_SHEET_NAME);

//********************************

//　基本設定情報

//********************************

//　在庫管理スプレッドシートのID

var SpreadSheetId = ''

//　在庫管理スプレッドシートの各シート名

var ArrivalShipping_SHEET_NAME = '入出荷登録シート';

var ItemRegister_SHEET_NAME = '商品登録シート';

var ItemInquiry_SHEET_NAME = '商品照会シート';

var History_SHEET_NAME = '履歴シート';

var ItemList_SHEET_NAME = '在庫管理シート';

var ItemType_SHEET_NAME = '種別管理シート';

//　在庫管理スプレッドシートの各シート情報

var ss = SpreadsheetApp.openById(SpreadSheetId );

var ArrivalShipping_SHEET = ss.getSheetByName(ArrivalShipping_SHEET_NAME);

var ItemRegister_SHEET = ss.getSheetByName( ItemRegister_SHEET_NAME);

var ItemInquiry_SHEET = ss.getSheetByName(ItemInquiry_SHEET_NAME);

var History_SHEET = ss.getSheetByName(History_SHEET_NAME);

var ItemList_SHEET = ss.getSheetByName(ItemList_SHEET_NAME);

var ItemType_SHEET = ss.getSheetByName(ItemType_SHEET_NAME);

SHEET_IDには、スプレッドシートのIDを記入します。

IDは、スプレッドシートのURLで確認することができます。

スプレッドシートIDの確認場所

スプレッドシートのURL

https://docs.google.com/spreadsheets/d/<スプレッドシートID>/edit

各シートの情報は、以下のようにシート名を指定することでシート情報を取得できます。

商品登録機能

続いて商品の登録機能を実装します。

設定情報エリアの後ろに以下のコードを記述します。

//********************************
//　商品登録処理
//********************************

function ItemRegister () {
  // 登録情報を取得
  let item_name =  ItemRegister_SHEET.getRange(2,2).getValue();
  let item_type =  ItemRegister_SHEET.getRange(3,2).getValue();
  let itme_last_row = ItemList_SHEET.getLastRow();
  let type_last_row = ItemType_SHEET.getLastRow();
  let item_type_code;
  // チェック用のフラグ変数を作成
  let cancel_flag = false;
  let itemtype_flag = false;
  // 種別登録済みチェック
  for(let i= 2; i<=type_last_row; i++){
       if(ItemType_SHEET.getRange(i,2).getValue() == item_type){
      item_type_code =ItemType_SHEET.getRange(i,1).getValue()
      itemtype_flag = true;
    }
  }

  // 重複登録チェック処理
  // 商品管理シートの２行目（１行目は、列名なのでスキップ）から最終行までをループ
  for(let i=2; i<=itme_last_row; i++){
    // 商品名が一致する商品があればすでに登録済みの商品があるということなので処理を中断するようにフラグを立てる
    if(ItemList_SHEET.getRange(i,2).getValue() == item_name){
      cancel_flag = true;
    }
  }

  if(cancel_flag == false && itemtype_flag == true){
    if(itme_last_row==1){
      ItemList_SHEET.getRange(itme_last_row+1,1).setValue(itme_last_row+1);
    }else{
        var sheet = ItemList_SHEET;
        //降順用配列
        var arrayDataDesc = sheet.getRange(2, 1,sheet.getLastRow()).getValues();
        //数値(Number)フィルタ
        var arrayDataDesc = arrayDataDesc.filter(Number);
        //降順に並べ替え
        var arraySortedDesc = arrayDataDesc.sort(function(a,b){return b-a});
        //降順に並べ替えた配列の先頭 = 最大値
        var max =  arraySortedDesc[0];
        ItemList_SHEET.getRange(itme_last_row+1,1).setValue(Number(max)+1);
    }
    // 商品を登録
    ItemList_SHEET.getRange(itme_last_row+1,2).setValue(item_name);
    ItemList_SHEET.getRange(itme_last_row+1,3).setValue(item_type_code);
    ItemList_SHEET.getRange(itme_last_row+1,4).setValue(0);
    Browser.msgBox("商品名：『"+item_name+"』"+"種別：『"+item_type+"』を追加しました。");
  }else if(cancel_flag == true){
    Browser.msgBox("商品名：『"+item_name+"』"+"種別：『"+item_type+"』は、すでに登録されています。");   
  }else if(itemtype_flag == false){
    Browser.msgBox("種別：『"+item_type+"』は、種別管理シートに未登録の種別です。");   
  }
}

//********************************

//　商品登録処理

//********************************

function ItemRegister () {

// 登録情報を取得

let item_name = ItemRegister_SHEET.getRange(2,2).getValue();

let item_type = ItemRegister_SHEET.getRange(3,2).getValue();

let itme_last_row = ItemList_SHEET.getLastRow();

let type_last_row = ItemType_SHEET.getLastRow();

let item_type_code;

// チェック用のフラグ変数を作成

let cancel_flag = false;

let itemtype_flag = false;

// 種別登録済みチェック

for(let i= 2; i<=type_last_row; i++){

if(ItemType_SHEET.getRange(i,2).getValue() == item_type){

item_type_code =ItemType_SHEET.getRange(i,1).getValue()

itemtype_flag = true;

}

// 重複登録チェック処理

// 商品管理シートの２行目（１行目は、列名なのでスキップ）から最終行までをループ

for(let i=2; i<=itme_last_row; i++){

// 商品名が一致する商品があればすでに登録済みの商品があるということなので処理を中断するようにフラグを立てる

if(ItemList_SHEET.getRange(i,2).getValue() == item_name){

cancel_flag = true;

}

if(cancel_flag == false && itemtype_flag == true){

if(itme_last_row==1){

ItemList_SHEET.getRange(itme_last_row+1,1).setValue(itme_last_row+1);

}else{

var sheet = ItemList_SHEET;

//降順用配列

var arrayDataDesc = sheet.getRange(2, 1,sheet.getLastRow()).getValues();

//数値(Number)フィルタ

var arrayDataDesc = arrayDataDesc.filter(Number);

//降順に並べ替え

var arraySortedDesc = arrayDataDesc.sort(function(a,b){return b-a});

//降順に並べ替えた配列の先頭 = 最大値

var max = arraySortedDesc[0];

ItemList_SHEET.getRange(itme_last_row+1,1).setValue(Number(max)+1);

}

// 商品を登録

ItemList_SHEET.getRange(itme_last_row+1,2).setValue(item_name);

ItemList_SHEET.getRange(itme_last_row+1,3).setValue(item_type_code);

ItemList_SHEET.getRange(itme_last_row+1,4).setValue(0);

Browser.msgBox("商品名：『"+item_name+"』"+"種別：『"+item_type+"』を追加しました。");

}else if(cancel_flag == true){

Browser.msgBox("商品名：『"+item_name+"』"+"種別：『"+item_type+"』は、すでに登録されています。");

}else if(itemtype_flag == false){

Browser.msgBox("種別：『"+item_type+"』は、種別管理シートに未登録の種別です。");

}

商品登録処理では、入力項目の資材名と種別を取得。

登録したい商品が在庫管理シートにすでに登録済みかの確認と種別管理シートに記載されている種別が設定されているかを確認します。

問題なければ在庫管理シートの末行に商品を追加して管理番号を設定します。

管理番号は、登録済みの商品で一番数値の高い管理番号に1足した値を設定します。

入出荷登録機能

商品の入荷、出荷を登録する処理を実装します。

以下のソースを末行に追加します。

//********************************
//入出荷登録処理
//********************************

function ArrivalShipping(){
    let arrivalShipping_last_row = ArrivalShipping_SHEET.getLastRow();
    let max = 0;
    let cancel_flag = true;
    let missMatchlist =[];
    let missMatchlistCount = 0;
    let message;
    for(let i=2; i<=arrivalShipping_last_row; i++){//入出荷登録商品取得ループ
      // 入出荷したい商品情報を取得
      let item_match_flag = false;  
      let itemList_last_row = ItemList_SHEET.getLastRow();     
      let set_item_name = ArrivalShipping_SHEET.getRange(i,2).getValue();
      let set_item_type = ArrivalShipping_SHEET.getRange(i,3).getValue();
      let set_item_count = ArrivalShipping_SHEET.getRange(i,4).getValue();
      if(ArrivalShipping_SHEET.getRange(i,2).getValue() != ""){//登録対象があるかチェック
        for(let l=2; l<=itemList_last_row; l++){//登録資材ループ　登録資材に入出荷したい資材があるかチェック
          let item_name=ItemList_SHEET.getRange(l,2).getValue();
          var item_code=ItemList_SHEET.getRange(l,1).getValue();
          if(item_name == set_item_name){
            // 数量変更処理
            let item_count = ItemList_SHEET.getRange(l,4).getValue();
            let sum_item_count;
            if(set_item_type == '入荷'){
              sum_item_count = item_count + set_item_count;
            }else if (set_item_type == '出荷'){
              sum_item_count =  item_count - set_item_count;
            }
            ItemList_SHEET.getRange(l,4).setValue(sum_item_count);
            item_match_flag = true;
            break;
          }
        }
        if(item_match_flag == true){
          let history_last_row = History_SHEET.getLastRow();
          if(history_last_row == 1){
            max = "1";
          }else{
            var sheet = History_SHEET;
            //降順用配列
            var arrayDataDesc = sheet.getRange(2, 1,sheet.getLastRow()).getValues();
                //数値(Number)フィルタ
            var arrayDataDesc = arrayDataDesc.filter(Number);
            //降順に並べ替え
            var arraySortedDesc = arrayDataDesc.sort(function(a,b){return b-a});
            //降順に並べ替えた配列の先頭 = 最大値
            max =  arraySortedDesc[0]; 
          }
          History_SHEET.getRange(history_last_row+1,1).setValue(Number(max)+1);
          History_SHEET.getRange(history_last_row+1,2).setValue(item_code);
          History_SHEET.getRange(history_last_row+1,3).setValue(set_item_name);
          History_SHEET.getRange(history_last_row+1,4).setValue(set_item_type);
          History_SHEET.getRange(history_last_row+1,5).setValue(set_item_count);
          var date = new Date();
          History_SHEET.getRange(history_last_row+1,6).setValue(Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy-MM-dd: HH:mm:ss'));
          cancel_flag = false;
        }else{
          missMatchlistCount = missMatchlist.push(set_item_name);
        }
      }
    }
    if(cancel_flag == true){
      Browser.msgBox("入出荷が１件も入力されていません");   
    }else if(missMatchlistCount > 0){
      for(let i=0; i< missMatchlistCount; i++){
        if(i == 0){
          message = missMatchlist[i];
        }else{
          message = message + ',' + missMatchlist[i];
        }
      }
      Browser.msgBox("入出荷情報を登録しましたが次の商品は、未登録商品です。"+message);   
    }else{
      Browser.msgBox("入出荷情報を登録しました。");   

    }
  
}

//********************************

//入出荷登録処理

//********************************

function ArrivalShipping(){

let arrivalShipping_last_row = ArrivalShipping_SHEET.getLastRow();

let max = 0;

let cancel_flag = true;

let missMatchlist =[];

let missMatchlistCount = 0;

let message;

for(let i=2; i<=arrivalShipping_last_row; i++){//入出荷登録商品取得ループ

// 入出荷したい商品情報を取得

let item_match_flag = false;

let itemList_last_row = ItemList_SHEET.getLastRow();

let set_item_name = ArrivalShipping_SHEET.getRange(i,2).getValue();

let set_item_type = ArrivalShipping_SHEET.getRange(i,3).getValue();

let set_item_count = ArrivalShipping_SHEET.getRange(i,4).getValue();

if(ArrivalShipping_SHEET.getRange(i,2).getValue() != ""){//登録対象があるかチェック

for(let l=2; l<=itemList_last_row; l++){//登録資材ループ　登録資材に入出荷したい資材があるかチェック

let item_name=ItemList_SHEET.getRange(l,2).getValue();

var item_code=ItemList_SHEET.getRange(l,1).getValue();

if(item_name == set_item_name){

// 数量変更処理

let item_count = ItemList_SHEET.getRange(l,4).getValue();

let sum_item_count;

if(set_item_type == '入荷'){

sum_item_count = item_count + set_item_count;

}else if (set_item_type == '出荷'){

sum_item_count = item_count - set_item_count;

}

ItemList_SHEET.getRange(l,4).setValue(sum_item_count);

item_match_flag = true;

break;

}

if(item_match_flag == true){

let history_last_row = History_SHEET.getLastRow();

if(history_last_row == 1){

max = "1";

}else{

var sheet = History_SHEET;

//降順用配列

var arrayDataDesc = sheet.getRange(2, 1,sheet.getLastRow()).getValues();

//数値(Number)フィルタ

var arrayDataDesc = arrayDataDesc.filter(Number);

//降順に並べ替え

var arraySortedDesc = arrayDataDesc.sort(function(a,b){return b-a});

//降順に並べ替えた配列の先頭 = 最大値

max = arraySortedDesc[0];

}

History_SHEET.getRange(history_last_row+1,1).setValue(Number(max)+1);

History_SHEET.getRange(history_last_row+1,2).setValue(item_code);

History_SHEET.getRange(history_last_row+1,3).setValue(set_item_name);

History_SHEET.getRange(history_last_row+1,4).setValue(set_item_type);

History_SHEET.getRange(history_last_row+1,5).setValue(set_item_count);

var date = new Date();

History_SHEET.getRange(history_last_row+1,6).setValue(Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy-MM-dd: HH:mm:ss'));

cancel_flag = false;

}else{

missMatchlistCount = missMatchlist.push(set_item_name);

}

if(cancel_flag == true){

Browser.msgBox("入出荷が１件も入力されていません");

}else if(missMatchlistCount > 0){

for(let i=0; i< missMatchlistCount; i++){

if(i == 0){

message = missMatchlist[i];

}else{

message = message + ',' + missMatchlist[i];

}

Browser.msgBox("入出荷情報を登録しましたが次の商品は、未登録商品です。"+message);

}else{

Browser.msgBox("入出荷情報を登録しました。");

}

登録処理では、入出荷登録シートに記載の内容を履歴シートへ登録し在庫管理シート上の対象商品の数量を入荷なら加算、出荷なら減算します。

在庫管理シートに存在しない商品が入力されていた場合は、未登録商品であることを伝えます。

照会機能

最後に在庫データを検索して表示する機能を作成します。

以下のコードを末行に追加します。

//********************************
//　商品検索処理
//********************************

function Search_Item(){
  // 検索情報を取得
  let item_name =  ItemInquiry_SHEET.getRange(2,2).getValue();
  ItemInquiry_SHEET.getRange(6,1).setValue('=QUERY('+ItemList_SHEET_NAME+'!A:D,"WHERE B = \\'' + item_name + '\\' ",0)');
}