2020年9月12日土曜日

GASで栄養成分表示（推定値）の計算結果を保存する。（指定したフォルダへのスプレッドシートの新規作成 + α）

-----
・追記（2024-9-7）

日本食品標準成分表2023年版対応のスプレッドシートを note にて公開しました。
-----

以前、栄養成分表示（推定値）を計算するスプレッドシートを作りましたが、計算結果の保存はできませんでした。

そのため、原材料や内容量の変更の際に原材料索引番号の検索から計算を始めねばならず面倒でした。ということで、都度手動保存も手間ですので簡単なGASで自動保存できるようにしてみました。

「計算結果」はあらかじめ用意したシートにリストで、「原材料・分量」は指定したフォルダに新しいスプレッドシートを作成してまるっと保存します。計算結果のリストには「原材料・分量」の載ったスプレッドシートへのリンクを記載するようにします。


① 計算結果を保存するシートを追加

以前作った計算用のスプレッドシートに以下のような計算結果を保存するシートを追加します。シート名は「保存された計算結果」にしておきます（後ほど作成するコードでこの名前でシートを指定しているため）。


ダウンロード → 栄養成分計算用スプレッドシート
（ダウンロードは note より）

・追記（2022-8-17）
スプレッドシートを最新版（2020年版対応v02）に差し替えました。

・追記（2021-4-17）
時々、アクセス権のリクエストをいただきますが、↓の方法で編集可能なコピーを作成してご使用ください。

リンクより「保存された計算結果」のシートを追加済みのスプレッドシートをダウンロードできます。PCでGoogleアカウントにログインした状態で、リンク先にて「ファイル」から「コピーを作成」で編集可能なファイルが作成されます。


② フォルダーを準備

商品ごとの原材料・分量を記載したスプレッドシートを保存しておくためのフォルダーを準備（新規作成）します。


③ コードの作成

スプレッドシートの「ツール」→「スクリプト エディタ」からスクリプトファイルを作成します。コードは以下の通りです。

function save() {
    var spsh = SpreadsheetApp.openById("スプレッドシートID");
    var shtIngr = spsh.getSheetByName("原材料・分量の入力");
    var shtCalc = spsh.getSheetByName("栄養成分の計算結果");
    var shtSave = spsh.getSheetByName("保存された計算結果");
    var ingrLastRow = shtIngr.getLastRow();
    var ingrData = shtIngr.getRange(1,1,ingrLastRow,8).getValues();
    var calcData = shtCalc.getRange(2,1,1,7).getValues();
    
    //新しいスプレッドシートの作成
    var fileName = shtCalc.getRange(2,1).getValue() + "_" + shtCalc.getRange(2,2).getValue(); 
    var folderId = "フォルダーID";
    var fileId = SpreadsheetApp.create(fileName).getId();
    var file = DriveApp.getFileById(fileId);
    var folder = DriveApp.getFolderById(folderId);
    folder.addFile(file);    
    DriveApp.getRootFolder().removeFile(file);
    
    //新しいスプレッドシートに原材料を保存
    var newSpsh = SpreadsheetApp.openById(fileId);
    var newSht = newSpsh.getActiveSheet();
    newSht.getRange(1,1,ingrLastRow,8).setValues(ingrData);
    newSht.setFrozenRows(1);
    
    //計算結果を「保存された計算結果」シートに保存
    var newUrl = "https://docs.google.com/spreadsheets/d/" + fileId;
    var listLastRow = shtSave.getLastRow() + 1;
    shtSave.getRange(listLastRow,1,1,7).setValues(calcData);
    shtSave.getRange(listLastRow,8).setValue(newUrl);
}

書き換えが必要なのは2か所です。

・2行目の「スプレッドシートID」

スプレッドシートを開いた状態でURLの下記部分に表示されます。ちょっと長めです。 

https://docs.google.com/spreadsheets/d/ここの値をコピペ/edit#gid=0


・12行目の「フォルダーID」

フォルダーを開いた状態でURLの下記部分に表示されます。こちらもちょっと長め。

https://drive.google.com/drive/folders/ここの値をコピペ


ということで完成です。save関数を実行すると「計算結果」と「原材料・分量」をまとめて保存することができます。

GASの初回実行時に承認が必要となります。無料のGoogleアカウントの場合「このアプリは確認されていません」というページが表示されます。ここで「安全なページに戻る」をクリックすると実行できませんので、「詳細」→「プロジェクト名（安全ではないページ）に移動」から承認します。


GASによるスプレッドシートの新規作成は以下のサイトを参考にさせていただきました。



at
Labels: , ,