2019年4月30日 / 最終更新日時 : 2023年5月31日 officeの杜 Google

Google Apps Scriptで使う情報はプロパティを利用しよう【GAS】

プログラムを開発していると、一時的な情報もしくは恒久的な情報を用いて様々な処理の分岐や、パラメータを用いて処理を続行するといった処理が出てきます。例えば、ユーザ毎に「認証を実行した結果得た、Access Tokenを格納しておく」であったり、ユーザの氏名や個人情報、プログラムで使う固定的な値（施設名リストなどを格納したスプレッドシートのIDなど）などなど。

これらの情報をスクリプト内に直書きだとセキュリティ的にまずかったり、また変更が生じる場合にスクリプトを書き直す必要があったり。かといってスプレッドシートのセルに保存というのはあまりにも雑。ということでよく使うのがプロパティサービス。地味な機能なのですが、このプロパティを使う事で、プログラムのメンテナンス性やセキュリティ向上など様々な効用をもたらしてくれます。今回はこのプロパティサービスを見てゆきたいと思います。

1 今回使用するスプレッドシート
2 プロパティサービスについて
3 使ってみる
4 トリッキーなテクニック
5 関連リンク

今回使用するスプレッドシート

プロパティサービスを使ったスプレッドシート

プロパティサービスについて

概要

プロパティサービスとは、ファイルもしくはユーザ単位などでプログラム内で利用する「値」などを格納しておく保管場所です。その値も様々な値を格納する事が可能で、以下の特徴があります。

プロジェクト内であればグローバルに読み書きが可能である。
値は素の文字列だけでなく、JSON形式でも保存する事が可能である
スクリプトプロパティという形式の場合、スクリプトエディタから編集が可能になっている。
ユーザプロパティなどはスクリプトエディタからの編集は出来るものの使えません。Issue Trackerにも残ったまま放置されてるバグのようです。また他のユーザからは見えない。
GASからユーザプロパティを操作することは可能。LocalStorageのような扱い。でも、ブラウザに保存されてるわけじゃない。
キー名と値というペアで保存されている
プロジェクトをまたいで利用したい場合はライブラリ化する必要がある。
数値を保存すると、整数であっても例えば1.0といったように小数点が付く。けれど文字列として保存される。
但し取得時は文字列として扱われるので、計算時はNumber()などで型変換してあげないとしくじる
G Suite BasicやBusinessでは１日に読み書きできる上限は500,000回。
G Suite BasicやBusinessでは、保存出来る文字のサイズは、1つの値につき9KBまで
G Suite BasicやBusinessでは、そのプロジェクト内に保存できるプロパティの合計値上限は、500KBまで
JSONは型崩れせずに保存できますが、配列データなどは型崩れしたりします。1プロパティに複数値盛り込みたい場合には、JSONにして格納しましょう。
HTML側でも使いたい場合は、GAS側で呼び出した値をreturnするように関数を用意する必要がある。
Null値は保存できない。セットしても消えてしまう。
スクリプトから保存する場合、該当のキー名が存在しない場合自動的に作成されて保存される。
存在しない値を取得しようとすると、nullが返ってくる
変数ではないので、プログラムが終了しても消えない。なので、6分の壁を超えるテクニックなどで使える。
スクリプトトリガーなどに於いて、誰かがセットしたらフラグとしてスクリプトプロパティに保存し、二重設置防止などのテクニックにも利用できる。
レコード登録時の連番を取る時に、uidというプロパティを用意して、1ずつ足していくという使い方はよくあります。排他制御は必須です。
ファイルをコピーすると、コピーしたファイルには元のファイルにあったプロパティ値はありません。空の状態です。

となっています。VBAで言うところのiniファイル読み書きやレジストリ読み書き、.NETでいうところのappconfigのようなものだと思えばOKだと思います。ただし容量に制限があるので注意。特にユーザプロパティは利用してるG Suiteのユーザ数が多いと場合によっては上限値に到達しやすくなります。

プロパティサービスの種類

プロパティサービスは3種類のプロパティサービスで構成されています。スクリプトプロパティ、ドキュメントプロパティ、ユーザプロパティの３つです。

それぞれに特徴があり

スクリプトプロパティはそのプロジェクト内から自由にアクセスでき、権限があれば誰でも見ることが可能
ユーザプロパティはそのユーザだけが閲覧でき編集が可能である。またそのプロジェクト内でだけ有効。
ドキュメントプロパティはスクリプトエディタ上からは一切見えない。但し、全員がアクセス可能である。ドキュメント共通。但し、プロジェクトを跨いで参照は出来ない。

普通は1.および2.を利用する事になるかと思います。ドキュメントプロパティはアドオンなどの形式で使う場合に使うもので、アドオンですから、いろいろなドキュメントを見る時に、「そのドキュメントにバインドされてるプロパティは○○」といった具合に使うシーンで、通常使いません。

ですので、スクリプトプロパティが全ユーザ共通の設定項目、ユーザプロパティがユーザ単位の設定項目と覚えて使い分けをすると良いでしょう。

スクリプトプロパティを編集

一番よく利用するスクリプトプロパティだけは、全員がスクリプトエディタ上から編集する事が可能です（ユーザプロパティは各個人だけ）。ただ通常はあまりスクリプトエディタ上から編集作業は行いません。スクリプトから読み書きを行います。また、スクリプトプロパティはその性質上スクリプト編集が出来る人間であれば、その内容を編集できてしまうので、センシティブな情報を格納する場合（たとえ、クライアントIDやクライアントシークレット）、スプレッドシートの編集権限はオーナーのみとし、プログラム（HTML Serviceから使わせる）の実行権限もオーナーとすると隠蔽が可能です。

スクリプトエディタを開く
ファイル⇒プロジェクトのプロパティを開く
スクリプトプロパティタブを開く
行を追加をクリック
キー名と値を入れて保存するとストアされる。
削除をクリックすれば削除することが可能

図：こんな感じでtomatoというキーに0という値を保存してみた

使ってみる

スクリプトプロパティ

そのプロジェクト共通で利用する値を格納します。以下のような構文になります。全ユーザが共通して読み書きが可能です。

//スクリプトプロパティを保存する
function setscprop(){
  //プロンプト表示
  var ui = SpreadsheetApp.getUi();
  var prompt = ui.prompt("値を格納")
  var res = prompt.getResponseText();
  
  //スクリプトプロパティに保存する
  var prop = PropertiesService.getScriptProperties();
  prop.setProperty("value", res);
  
  //メッセージを返す
  ui.alert(res + "がスクリプトプロパティに保存されました。");
}

//スクリプトプロパティを呼び出す
function getscprop(){
  //UIを取得
  var ui = SpreadsheetApp.getUi();
  
  //スクリプトプロパティの値を取得
  var prop = PropertiesService.getScriptProperties();
  var res = prop.getProperty("value");
  
  //メッセージを返す
  ui.alert("スクリプトプロパティに保存されてる値は" + res + "です");
  
}

//スクリプトプロパティを保存する

function setscprop(){

//プロンプト表示

var ui = SpreadsheetApp.getUi();

var prompt = ui.prompt("値を格納")

var res = prompt.getResponseText();

//スクリプトプロパティに保存する

var prop = PropertiesService.getScriptProperties();

prop.setProperty("value", res);

//メッセージを返す

ui.alert(res + "がスクリプトプロパティに保存されました。");

}

//スクリプトプロパティを呼び出す

function getscprop(){

//UIを取得

var ui = SpreadsheetApp.getUi();

//スクリプトプロパティの値を取得

var prop = PropertiesService.getScriptProperties();

var res = prop.getProperty("value");

//メッセージを返す

ui.alert("スクリプトプロパティに保存されてる値は" + res + "です");

}

スクリプトプロパティなので、PropertiesService.getScriptPropertiesを利用します。
getProperty(キー名)で値を取得出来ます。
setProperty(キー名,値)で値をセットできます。
サンプルコードでは、valueというキーにui.promptの値を格納しています。

ユーザプロパティ

そのプロジェクト内に於ける各ユーザだけが読み書きできるプロパティです。ユーザ固有の他の誰にも閲覧不可なデータ類を格納する時に利用します。

//ユーザプロパティを保存する
function setusrprop(){
  //プロンプト表示
  var ui = SpreadsheetApp.getUi();
  var prompt = ui.prompt("値を格納")
  var res = prompt.getResponseText();
  
  //スクリプトプロパティに保存する
  var prop = PropertiesService.getUserProperties();
  prop.setProperty("value", res);
  
  //メッセージを返す
  ui.alert(res + "がユーザプロパティに保存されました。");
}

//ユーザプロパティを取得する
function getusrprop(){
  //UIを取得
  var ui = SpreadsheetApp.getUi();
  
  //スクリプトプロパティの値を取得
  var prop = PropertiesService.getUserProperties();
  var res = prop.getProperty("value");
  
  //メッセージを返す
  ui.alert("スクリプトプロパティに保存されてる値は" + res + "です");
}

//ユーザプロパティを保存する

function setusrprop(){

//プロンプト表示

var ui = SpreadsheetApp.getUi();

var prompt = ui.prompt("値を格納")

var res = prompt.getResponseText();

//スクリプトプロパティに保存する

var prop = PropertiesService.getUserProperties();

prop.setProperty("value", res);

//メッセージを返す

ui.alert(res + "がユーザプロパティに保存されました。");

}

//ユーザプロパティを取得する

function getusrprop(){

//UIを取得

var ui = SpreadsheetApp.getUi();

//スクリプトプロパティの値を取得

var prop = PropertiesService.getUserProperties();

var res = prop.getProperty("value");

//メッセージを返す

ui.alert("スクリプトプロパティに保存されてる値は" + res + "です");

}

ユーザプロパティなので、PropertiesService.getUserPropertiesを使用します。
getとsetはスクリプトプロパティと全く同じです。
以前のユーザプロパティは、各プロジェクト単位ではなく全プロジェクト共通でしたが、現在は他のプロジェクトから他のプロジェクトのユーザプロパティは直接参照できません。
ただし、GASでユーザプロパティをセットすると、プロジェクトのプロパティ上で手動でセットした時と違い、見えません・・・ここ嵌まるポイントです。ですが、確実にセットされているので、必要に応じて次項のプロパティを削除するを装備すべきでしょう。

プロパティを削除する

プロパティの削除は非常に単純です。以下のメソッドで削除するだけです。

//すべてのプロパティを削除
var prop = PropertiesService.getUserProperties();
prop.deleteAllProperties();

//特定のプロパティを削除する
var prop = PropertiesService.getUserProperties();
prop.deleteProperty('value');

//すべてのプロパティを削除

var prop = PropertiesService.getUserProperties();

prop.deleteAllProperties();

//特定のプロパティを削除する

var prop = PropertiesService.getUserProperties();

prop.deleteProperty('value');

トリッキーなテクニック

ライブラリ化して複数プロジェクト内で同一プロパティを共有

ワークフローな仕組みを作る時に、例えば読み書きするスプレッドシートのIDなどや送信先メアドなどをプロパティサービスを使って保存し、プログラムで利用することがあります。しかし、申請用フォームと承認用フォームで共用の２つで共用したくとも、スクリプトプロパティはそのプロジェクト内しか参照できません。

そこでライブラリ機能を使って1箇所で読み書き出来るようにして使ってたりします。この場合そのスプレッドシートには申請用、承認用に加えてスクリプトプロパティ用の3つ目のプロジェクトを追加しておく必要があります。この3つ目がライブラリとなります。

//プロパティを取得する
function getProp(value) {
  var Properties = PropertiesService.getScriptProperties();  
  var temp = Properties.getProperty(value);
  
  return temp;
}

//プロパティをセットする
function setProp(key,value){
  var Properties = PropertiesService.getScriptProperties();
  
  Properties.setProperty(key,value);
}

//プロパティを取得する

function getProp(value) {

var Properties = PropertiesService.getScriptProperties();

var temp = Properties.getProperty(value);

return temp;

}

//プロパティをセットする

function setProp(key,value){

var Properties = PropertiesService.getScriptProperties();

Properties.setProperty(key,value);

}

呼び出し側は、ライブラリ登録し識別子を加え（libraryと名付けたとする）、例えばlibrary.getPropでプロパティを取得します。library.setPropでプロパティを保存します。呼び出し側でPropertiesServiceをいちいち記述する必要もないので楽ちんです。共通設定用のプロジェクトとしてこれを版保存し呼び出し側のライブラリに追加してあげましょう。

この手法を使うと、1つのプロジェクトを用意してライブラリ化。ユーザプロパティの読み書きを実装しておけば、1箇所で全プロジェクト共通で使えるユーザ設定を実現できます。但し、この場合全プロジェクト共通なので、API_KEYなんて設定にしてしまうと、他のAPI_KEYとごっちゃになったり、区別がつかなくなったりするので、キー名の付け方に注意が必要です。

大きなサイズの設定値を格納したい

大規模な組織で1個のファイルにスクリプトプロパティでいろいろ格納したり、ユーザプロパティとは言え結構な量のデータを規定の情報としてキープしておきたい場合、1キーの上限9KBや合計値の500KBに到達して、プロパティサービスでは保存しきれないケースがあると思います。この場合、当然情報を保存できないので、以下の策を取る必要があります。

別途キー情報格納用のスプレッドシートを用意し保存する
Google DriveにJSONファイルとしてプロパティデータは保存し、ユーザプロパティにはそのファイルのIDだけを保存しておく。

前者の場合、そのファイルのアクセス権限は全員が見られる状態にしなければならないので、ユーザプロパティ保存をするには適していません。また、全ユーザの各個人の情報を格納するため、色々とロジックが必要になります。

一方後者の場合、そのユーザのドライブにそのユーザだけがアクセスできる権限で取っておけば良いだけなので、大容量のユーザプロパティ情報を保存するのに適しています。またJSONなので構造化されていてアクセスしやすいという利点もあります。後者のケースを研究してみました。

//JSONでユーザ情報を保存する
function setpropJson(){
  //JSONファイルが存在しているかどうかを確認
  var prop = PropertiesService.getUserProperties();
  var json = prop.getProperty("json");
  var ui = SpreadsheetApp.getUi();
  
  //jsonファイルのIDがある場合はそのIDを利用し、ない場合にはエラーを返す
  var jsonfile = "";
  var jsondata = "";
  var propdata = "";
  
  if(json == null){
    //ファイルがない場合
    //ファイル名はスクリプトID+メアドとする
    var filename = ScriptApp.getScriptId() + "_" + GetUser() + ".json";
    
    //JSONデータを生成
    var jsondata = {};
    jsondata.user = Utilities.formatDate(new Date(), "JST","yyyy/MM/dd HH:mm:ss");
    jsondata = JSON.stringify(jsondata);
    
    //ルートディレクトリ直下にファイルを生成
    var fileID = DriveApp.createFile(filename,jsondata,MimeType.PLAIN_TEXT).getId();
    
    //fileIDをスクリプトプロパティに格納する
    prop.setProperty("json", fileID);
    
    ui.alert("JSONファイルにユーザプロパティ情報を格納しました。")

  }else{
    jsonfile = json;
    
    //IDを元にJSONデータを取得する
    jsondata = DriveApp.getFileById(jsonfile).getBlob().getDataAsString('utf8');
    propdata = JSON.parse(jsondata);
    
    //user情報を書き換える（今回は日付情報を入力）
    propdata.user = Utilities.formatDate(new Date(), "JST","yyyy/MM/dd HH:mm:ss");
    
    //propdataを元のファイルに上書きする
    DriveApp.getFileById(jsonfile).setContent(JSON.stringify(propdata));
    
    //メッセージを返す
    ui.alert("ユーザ情報を書き込みました");
  }
}

//JSONのユーザ情報を取得する
function getpropJson(){
  //JSONファイルが存在しているかどうかを確認
  var prop = PropertiesService.getUserProperties();
  var json = prop.getProperty("json");
  var ui = SpreadsheetApp.getUi();
  
  //jsonファイルのIDがある場合はそのIDを利用し、ない場合にはエラーを返す
  var jsonfile = "";
  var jsondata = "";
  var propdata = "";

  if(json == null){
    ui.alert("プロパティ情報が見つかりませんでした")  
  }else{
    //IDを元にJSONデータを取得する
    jsonfile = json;
    jsondata = DriveApp.getFileById(jsonfile).getBlob().getDataAsString('utf8');
    propdata = JSON.parse(jsondata);
  
    //jsonのユーザ情報を表示する
    ui.alert(propdata.user);
  }
}

//ユーザプロパティ削除
function deleteuserprop(){
  var prop = PropertiesService.getUserProperties();
  prop.deleteProperty('json');
  SpreadsheetApp.getUi().alert("ユーザプロパティ情報を削除しました。");
}

//実行者のメールアドレスを取得する
function GetUser() {
  var objUser = Session.getActiveUser();
  return objUser.getEmail();
}

//JSONでユーザ情報を保存する

function setpropJson(){

//JSONファイルが存在しているかどうかを確認

var prop = PropertiesService.getUserProperties();

var json = prop.getProperty("json");

var ui = SpreadsheetApp.getUi();

//jsonファイルのIDがある場合はそのIDを利用し、ない場合にはエラーを返す

var jsonfile = "";

var jsondata = "";

var propdata = "";

if(json == null){

//ファイルがない場合

//ファイル名はスクリプトID+メアドとする

var filename = ScriptApp.getScriptId() + "_" + GetUser() + ".json";

//JSONデータを生成

var jsondata = {};

jsondata.user = Utilities.formatDate(new Date(), "JST","yyyy/MM/dd HH:mm:ss");

jsondata = JSON.stringify(jsondata);

//ルートディレクトリ直下にファイルを生成

var fileID = DriveApp.createFile(filename,jsondata,MimeType.PLAIN_TEXT).getId();

//fileIDをスクリプトプロパティに格納する

prop.setProperty("json", fileID);

ui.alert("JSONファイルにユーザプロパティ情報を格納しました。")

}else{

jsonfile = json;

//IDを元にJSONデータを取得する

jsondata = DriveApp.getFileById(jsonfile).getBlob().getDataAsString('utf8');

propdata = JSON.parse(jsondata);

//user情報を書き換える（今回は日付情報を入力）

propdata.user = Utilities.formatDate(new Date(), "JST","yyyy/MM/dd HH:mm:ss");

//propdataを元のファイルに上書きする

DriveApp.getFileById(jsonfile).setContent(JSON.stringify(propdata));

//メッセージを返す

ui.alert("ユーザ情報を書き込みました");

}

//JSONのユーザ情報を取得する

function getpropJson(){

//JSONファイルが存在しているかどうかを確認

var prop = PropertiesService.getUserProperties();

var json = prop.getProperty("json");

var ui = SpreadsheetApp.getUi();

//jsonファイルのIDがある場合はそのIDを利用し、ない場合にはエラーを返す

var jsonfile = "";

var jsondata = "";

var propdata = "";

if(json == null){

ui.alert("プロパティ情報が見つかりませんでした")

}else{

//IDを元にJSONデータを取得する

jsonfile = json;

jsondata = DriveApp.getFileById(jsonfile).getBlob().getDataAsString('utf8');

propdata = JSON.parse(jsondata);

//jsonのユーザ情報を表示する

ui.alert(propdata.user);

}

//ユーザプロパティ削除

function deleteuserprop(){

var prop = PropertiesService.getUserProperties();

prop.deleteProperty('json');

SpreadsheetApp.getUi().alert("ユーザプロパティ情報を削除しました。");

}

//実行者のメールアドレスを取得する

function GetUser() {

var objUser = Session.getActiveUser();

return objUser.getEmail();

}

今回は日付データを格納するようにしてみました。userというキーに日付データを入れてあります。setpropJsonを実行する度に日付データが書き換わります。
setpropJsonではファイルがない場合には、JSONファイルを生成し、ユーザプロパティにはそのFileのIDが格納されます。
getpropJsonは、キーがある場合にはJSONファイルをパースしてuserキーの日付情報を返します。無い場合はエラーを返します。
このファイルは実行ユーザだけが見られる権限にしておきましょう。他人と共用してはなりません。
新規作成されるファイルはただのテキストファイルで、ScriptIDとユーザメアドを組み合わせたファイル名になっています。
ファイルはGoogle Driveのルート直下に生成されます。
この手法の場合、ファイルサイズの上限はDriveAppの扱える上限である50MBまで対応可能になります。

図：JSONなので変なロジックも不要

ウェブアプリから利用する場合

直接スプレッドシート上からスクリプトプロパティの操作をする場合は、必ずユーザ権限で実行されます。よって、getUserPropertiesを使った場合ユーザ毎のプロパティに対して読み書きが行われます。

しかし、ウェブアプリケーションから実行する場合は、ウェブアプリケーションをデプロイした際に指定した「次のユーザとして実行」のユーザ権限にて動作することになるため、ウェブアプリケーションにアクセスしてるユーザの場合は期待通りに動作しますが、ウェブアプリ作成者の権限で動作する場合、作成者のプロパティからの読み書きとなるため、実質getScriptPropertiesと変わらない動作になってしまいます。

しかし、ウェブアプリは作成者権限で動かしたい、けれど挙動はユーザ毎に変えたい場合はプロパティを利用するのではなく、Webアプリ側でLocalStorageを使って実装する必要があるでしょう（この場合、PC毎にデータが保存されるため、Chromeを利用する場合はプロファイルと共にLocalStorage Syncなどの拡張機能を使うと他のPCからのLocalStorageが別のPCへ同期出来て流用可能です）。

以下は検証で使ったコードです。

GAS側コード

function doGet(){
  var html = HtmlService.createHtmlOutputFromFile('index');
  return html;
}

//プロパティを保存する
function setProp(value){
  value = JSON.parse(value)
  var prop = PropertiesService.getUserProperties();

  prop.setProperty("userinfo",value);

  return 0;
}


//プロパティをゲットする
function getProp(){
  var prop = PropertiesService.getUserProperties();
  console.log(prop.getProperty("userinfo"))

  return prop.getProperty("userinfo")
}

function doGet(){

var html = HtmlService.createHtmlOutputFromFile('index');

return html;

}

//プロパティを保存する

function setProp(value){

value = JSON.parse(value)

var prop = PropertiesService.getUserProperties();

prop.setProperty("userinfo",value);

return 0;

}

//プロパティをゲットする

function getProp(){

var prop = PropertiesService.getUserProperties();

console.log(prop.getProperty("userinfo"))

return prop.getProperty("userinfo")

}

HTML側コード

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">

    <script>
      function timeset(){
        var time = new Date();
        google.script.run.withSuccessHandler(onSuccess).setProp(JSON.stringify(time));
      }

      function onSuccess(data){
        alert("OK")
      }

      function gettime(){
        google.script.run.withSuccessHandler(onSuccess2).getProp();
      }

      function onSuccess2(data){
        document.getElementById("time").innerHTML = data;
      }

    </script>
  </head>
  <body onload="gettime()">  
    <button onClick="timeset()">クリック</button>
    <div id="time"></div>
  </body>
</html>

<!DOCTYPE html>

<html>

<head>

function timeset(){

var time = new Date();

google.script.run.withSuccessHandler(onSuccess).setProp(JSON.stringify(time));

}

function onSuccess(data){

alert("OK")

}

function gettime(){

google.script.run.withSuccessHandler(onSuccess2).getProp();

}

function onSuccess2(data){

document.getElementById("time").innerHTML = data;

}

</script>

</head>

</body>

</html>

Google Apps Scriptで使う情報はプロパティを利用しよう【GAS】

今回使用するスプレッドシート

プロパティサービスについて

概要

プロパティサービスの種類

スクリプトプロパティを編集

使ってみる

スクリプトプロパティ

ユーザプロパティ

プロパティを削除する

トリッキーなテクニック

ライブラリ化して複数プロジェクト内で同一プロパティを共有

大きなサイズの設定値を格納したい

ウェブアプリから利用する場合

GAS側コード

HTML側コード

関連リンク

コメントを残すコメントをキャンセル

Google Apps Scriptでカスタムプロパティを使う【GAS】

今回使用するスプレッドシート

プロパティサービスについて

概要

プロパティサービスの種類

スクリプトプロパティを編集

使ってみる

スクリプトプロパティ

ユーザプロパティ

プロパティを削除する

トリッキーなテクニック

ライブラリ化して複数プロジェクト内で同一プロパティを共有

大きなサイズの設定値を格納したい

ウェブアプリから利用する場合

GAS側コード

HTML側コード

関連リンク

コメントを残す コメントをキャンセル

Google App Makerを試してみた

Google Apps Scriptでカスタムプロパティを使う【GAS】

コメントを残すコメントをキャンセル