【コピペで使える】GASの処理をスプレッドシートに即時反映させてみる

GASの処理をスプレッドシートに即時反映させてみる

GASでスプレッドシートを操作しているとあるタイミングで処理が反映されるのにタイムラグがあることに気がつく人もいると思います。

例えば、GASを使ってスプレッドシートを操作し、操作が終わったあとのスプレッドシートをPDF化してGoogleドライブに保存する場合やGASで出力された値をスプレッドシートの関数で計算結果をGASの後続処理などに使いたいといった場合でにこのようなことに気がつくことになります。

今回の記事ではそのような現象が起こることのご紹介とそれに対する対処方法をサンプルコード等を用いてご紹介します。

本記事が役立つターゲット読者

記事で紹介するメソッド一覧

まずは現象の確認

まずは、挙動が確認できるようにデモアプリを用意しました。

今回の記事でご紹介したいメソッドはflushというメソッドになります。

デモアプリにはボタンが3つ並んでいます。

それぞれの挙動を確認していただければ、Flushの有無によってどのような違いが出てくるのかがわかると思います。

私が実行した結果も動画で撮影したので埋め込んでおきますね。

上記の動画でも確認ができますが、FlushをしないでスプレッドシートをPDF化すると、スプレッドシートに対する処理が反映される前の空欄のスプレッドシートがPDF化されます。

そのため、このような処理を行う場合は、PDF化する直前にSpreadsheetApp.Flushをしてあげる必要があります。

冒頭でも述べたように、GASの処理によって出力したものをスプレッドシートの関数で計算し、計算後の値をGASで再利用するというような処理を書いている場合にも同様にSpreadsheetApp.Flushをしてあげる必要があります。

覚えておかなければいけないことは

GASの処理は処理がすべて終了後にスプレッドシートに反映される

ということです。このロジックが頭に入っていないと、期待通りに処理が終わらない可能性があります。

flush()のサンプルコード

引数に入れる値

• 引数なし

サンプルコード

//flushなしでPDF化するコード
function sampleCodeForCreatingPdfWithoutFlush() {
  let ss = SpreadsheetApp.openByUrl(SHEET_URL); //テキトーなスプレッドシートURLに置き換える
  let sheet = ss.getSheets()[0];
  let isEmpty = sheet.getRange(1, 1).getValue();
  if (isEmpty) {
    sheet.clearContents();
    SpreadsheetApp.flush();
  }
  for (let i = 1; i <= 5; i++) {
    for (let j = 1; j <= 5; j++) {
      let range = sheet.getRange(i, j);
      range.setValue(j);
    }
  }
  let pdf = ss.getAs("application/pdf").copyBlob();
  let fileName = pdf.getName();
  let folder = DriveApp.getFolderById(FOLDER_ID); //テキトーなフォルダIDに置き換える
  let isFileExist = folder.searchFiles("title = '" + fileName + "'");
  if (isFileExist.hasNext()) {
    let file = isFileExist.next();
    file.setTrashed(true);
  }
  folder.createFile(pdf);
}

//flush後にPDF化するコード
function sampleCodeForCreatingPdfWithFlush() {
  let ss = SpreadsheetApp.openByUrl(SHEET_URL); //テキトーなスプレッドシートURLに置き換える
  let sheet = ss.getSheets()[0];
  let isEmpty = sheet.getRange(1, 1).getValue();
  if (isEmpty) {
    sheet.clearContents();
    SpreadsheetApp.flush();
  }
  for (let i = 1; i <= 5; i++) {
    for (let j = 1; j <= 5; j++) {
      let range = sheet.getRange(i, j);
      range.setValue(j);
    }
  }
  let pdf = ss.getAs("application/pdf").copyBlob();
  let fileName = pdf.getName();
  let folder = DriveApp.getFolderById(FOLDER_ID);
  let isFileExist = folder.searchFiles("title = '" + fileName + "'");
  if (isFileExist.hasNext()) {
    let file = isFileExist.next();
    file.setTrashed(true);
  }
  SpreadsheetApp.flush();
  folder.createFile(pdf);
}

変数の説明

ss = スプレッドシート
sheet = 対象のシート
isEmpty = A1セルの値
range = 値出力セル
pdf = PDF化したスプレッドシートファイル
fileName = PDFのファイル名
folder = 出力フォルダ
isFileExist = PDFファイルの有無
file = 既存PDFファイル

スプレッドシートのA1:E5までの範囲に1から5までの数字を出力してからPDF化する処理をサンプルコードとしています。

上記のサンプルコードを参考に、処理対象のスプレッドシートの処理が終わり、PDFする化する直前にflush()をすることを忘れないようにしてください。

flush()を使えば、それまでの処理をスプレッドシート側に反映させることができます。

一つ一つ厳密にスプレッドシートに反映させたいのであれば、setValue後に毎回flush()を行うことになりますが、そこまでやってしまうと処理が多くなって速度の低下に繋がりますので、必要最低限の回数を実施するようにします。

LockServiceとの併用について考える

GASにはLockServiceというクラスがあります。

あまり使ったことがない方も多いかもしれませんが、プログラムが同時に複数実行される場合などに、他の処理が動いているときはLockServiceを使うことで動いている処理が終わるまでその他の処理を待たせる事ができるという排他処理を行うことができます。

例えば、スプレッドシートの最終行の次の行に新しいデータを追加していくというような処理を考えてみます。

LockServiceを使った排他処理を行わずに実行すると、複数の処理が同時に最終行を取得してしまうと、すべての処理で最終行が同じものが取得されてしまいます。

このため、最終行の次の行が複数処理で被ってしまい、出力データを他の処理で上書きしてしまうという状況が発生してしまいます。

そこでLockServiceを利用します。流れとしては次のようになります。

このような形で、LockServiceを利用すると処理の順番待ちのようなことが行えるようになります。

とても便利な機能ですが、もちろん注意点があります。

これは、処理上限に達するリスクがある事を意味します

そのため、LockServiceはロックの必要性がある処理が終われば可能な限り早めにロック解除することが必要となります。

ここで思い出してほしいのですが、GASでスプレッドシートを処理する場合に、スプレッドシートに処理内容が反映されるのはGASの処理が終わったタイミングということです。

そのため、上記のSTEP通りにロックを解除すると思った通りの動きをしてくれない場合があります。

主に、排他処理終了後にGASの処理が続く場合などが条件になりますね。

それを回避するためには本記事でご紹介のflushをして上げる必要があるのです。

このように、GASが自分の思ったとおりの動きをするようにロジックを考える必要があります。

//LockServiceのサンプルコード
function sampleCodeForLockService() {
  let ss = SpreadsheetApp.openByUrl(SHEET_URL);
  let sheet = ss.getSheets()[0];
  let msg;
  let lock = LockService.getScriptLock();
  if (lock.tryLock(10000)) { //10秒間他の処理が終わるのを待ち
    Utilities.sleep(15000); //15秒処理をスリープ
    
    lock.releaseLock(); //ロックを解除
    msg = "実行完了";
  }
  else {
    msg = "他の処理が実行中です";
  }
  console.log(msg);
}

上記のサンプルコードでは、lock.tryLock(10000)の部分で、現在排他処理がされているかどうかを確認し、されていなければtrueを返して排他処理を開始し始めます。10000というのは排他処理が動いている場合に何秒間処理が終わるのを待ち続けるかという待機時間をミリ秒で指定しています。

サンプルでは最大10秒間待ち続け、その間に排他処理が終われば自分がロックをかけて排他処理を開始するという形です。10秒以内に他の排他処理が終わらなければ、falseを返してelse 内の処理を行うようにしてあります。

LockServiceの処理について詳しくは下記の記事で紹介します。

あわせて読みたい

【コピペで使える】GASで排他処理を実現してみる 【GASで排他処理を実現してみる】 本記事では、排他処理というものをご紹介しようと思います。 おそらく、この記事にたどり着いた方は、フォームからGASでスプレッドシ…

まとめ

今回の記事では、SpreadsheetAppでは非常に重要なflush()というメソッドのご紹介を行いました。

今までに思ったとおりの値が取得できない経験がある方にはぜひ覚えていただきたいメソッドです。