2020年10月10日 / 最終更新日時 : 2020年10月10日 officeの杜最新トピック

Google Apps Scriptでスクリプトプロパティの動きがオカシイ件

2週間ほど前から、ツイッター上でGoogle Apps Scriptに関する以下のような報告がパラパラと出始めました。そしてこの問題の一部は未だ継続中です。そして、相変わらず、ログに表示されるのが遅い。

スクリプトプロパティの値がきちんと格納されないで、Data storage errorが表示される（データ容量エラー）
スクリプトプロパティに格納した値を取り出すと、古い値が帰ってくる（過去のデータが返るバグ）
そもそもスクリプトプロパティに値が格納されない（データ格納不能バグ）

図：Data Storage Error

図：過去の値が帰ってくる

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

1 テスト用スプレッドシート
2 厄介な点
3 代替案
4 関連リンク

テスト用スプレッドシート

Propertie Testシート

厄介な点

厄介なのはこれらの問題、すべて一律に発生してるわけではなく、一部のファイルでだけ発生しており、コピーしてもNG、コピペしてもNGになるケースがあるとか。

スクリプトプロパティは、プログラムの一時的な値や、固定的に使う変数などを格納する為に利用する非常に重要な機能であるがゆえに、影響範囲が非常に大きいです。

前項の3.に至っては、手動でUI上からスクリプトプロパティを作ってるにも関わらず、値がセットされない現象です。また、Issue TrackerではGoogle側担当者が再現出来ないからと、問題の調査をそこで終了し放置するケースがままある上に、前回の別のトラブルに至っては実に１ヶ月以上もの間、修正も代替案も出さず仕舞い（この件は完全にGoogleがGoogle Apps Script側との連携を考慮せずにChrome85をリリースして、トラブルになってる）。

※過去にもPicker関係でライブラリを修正後にGoogle Apps Scriptから使えなくなるケースなどの対応も、まぁ、酷いものでした。他のユーザが代替案出してくれてたので、フォロー出来ていた状態。

代替案

概要

さてこのプロパティという機能、アプリの設定値などを格納したり、OAuth2.0認証用のClient IDなどの格納で使ったりと、表からは見えないけれど非常に重要な機能です。

代替案は

スプレッドシート上にプロパティの役割をする値を配置して管理する
JSONなどのファイル化を行い、ドライブに保存。それに対して読み書きを行う

の２つになります。但し、１．はあまりオススメ出来ません。１．の方法は誰でも簡単にスプレッドシート上から書き換えが出来てしまう点やシート保護をしても閲覧を防ぐことは出来ない（ユーザプロパティのようなユーザ毎に設定を取る場合には全く使えない）

となると、後者になります。後者の場合、Google Drive上にテキストファイルでJSONファイルを作成し、そこにKeyと値を格納する方法で、Google Apps Scriptから読み書きが可能。アクセス権限はGoogle Driveの権限をそのまま利用するので、ファイルのIDがわかっても、ファイルを閲覧出来るのはスクリプト実行者本人だけになります（もちろん、ファイルを公開すれば誰でもアクセス可能にもなる）

閲覧と編集の権限もわけられるので、利用者は閲覧権限で組織内で公開。オーナーだけ編集にして設定値を保存できるといった柔軟性もあります。

事前準備

スクリプトプロパティとして使うようなケースでは、予めJSONファイルを作っておきそのファイルIDをスクリプト内で変数で直書きで良いでしょう。複数名で使いユーザごとにセットするユーザプロパティとして使うようなケースでは、スプレッドシート上で

ユーザのメアドとファイルのIDの２列を用意
実行時にユーザのメアドを取得。シートを参照しメアドとファイルIDがある場合はそれを利用する
２．で存在しない場合は新規にファイルを作成し、１．のシートにメアドとIDを追記する

という仕組みを用意しておきます。この場合スプレッドシートは組織内の人全員が編集権限を持つ必要があります（ウェブアプリケーションの場合は、オーナー権限で代行ということも可能）

JSONファイルの読み書き

//JSONでユーザ情報を保存する
function setpropJson(fileid){
  var ui = SpreadsheetApp.getUi();
  
  //jsonファイルのIDがある場合はそのIDを利用し、ない場合にはエラーを返す
  var jsonfile = "";
  var jsondata = "";
  var propdata = "";
  
  if(fileid == 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);
    
    //ルートディレクトリ直下にファイルを生成しIDを取得
    var fileID = DriveApp.createFile(filename,jsondata,MimeType.PLAIN_TEXT).getId();
    
    //メアドとfileIDをスクリプトプロパティに格納する
    ・・・・ここにシート側に記述するコードを書く・・・・
    
    ui.alert("JSONファイルにユーザプロパティ情報を格納しました。")

  }else{
    //IDを元にJSONデータを取得する
    jsondata = DriveApp.getFileById(fileid).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(fileid){
  var ui = SpreadsheetApp.getUi();
  
  //jsonファイルのIDがある場合はそのIDを利用し、ない場合にはエラーを返す
  var jsonfile = "";
  var jsondata = "";
  var propdata = "";

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

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

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

function setpropJson(fileid){

var ui = SpreadsheetApp.getUi();

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

var jsonfile = "";

var jsondata = "";

var propdata = "";

if(fileid == 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);

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

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

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

・・・・ここにシート側に記述するコードを書く・・・・

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

}else{

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

jsondata = DriveApp.getFileById(fileid).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(fileid){

var ui = SpreadsheetApp.getUi();

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

var jsonfile = "";

var jsondata = "";

var propdata = "";

if(fileid == null){

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

}else{

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

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

propdata = JSON.parse(jsondata);

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

ui.alert(propdata.user);

}

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

function GetUser() {

var objUser = Session.getActiveUser();

return objUser.getEmail();

}

別途スプレッドシートにユーザメアドとファイルIDを格納するシートが必要
また、それを読み書きするためのロジックも必要です。（新規追加はappendRowで良いでしょう）
今回は日付データを格納するようにしてみました。userというキーに日付データを入れてあります。setpropJsonを実行する度に日付データが書き換わります。
setpropJsonではファイルがない場合には、JSONファイルを生成し、そのファイルIDを取得しておきます
このファイルはユーザプロパティ使用ならば誰とも共有してはなりません。
このファイルはスクリプトプロパティ使用ならば組織内で全員が読み書きできる必要があります。但し、ユーザがプロパティに書き込みを行わないケースでは読み取り専用でOK
ファイルはGoogle Driveのルート直下に生成されます。
この手法の場合、ファイルサイズの上限はDriveAppの扱える上限である50MBまで対応可能になります（スクリプトプロパティはトータル500KBまで）
ユーザプロパティとして使うケースは、例えばOAuth2.0認証のAccess Token、Refresh Tokenの保存などが考えられます。暗号化保存しておけば尚安心でしょう。

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

Google Apps Scriptでスクリプトプロパティの動きがオカシイ件

テスト用スプレッドシート

厄介な点

代替案

概要

事前準備

JSONファイルの読み書き

関連リンク

GoogleスプレッドシートのonSelectionChangeトリガーについて

GASのウェブアプリが開けなくなる障害発生中