最近、Googleスプレッドシートが簡易的な管理画面にならないかな？と妄想している@zaruです。こんにちは。今回は、GoogleスプレッドシートとGoogle Apps Scriptを使って、処理時間が長すぎてタイムアウトしてしまうときの対処法を紹介します。
前提
Google Apps Scriptは、処理時間が6分を超えるとタイムアウトして強制終了します。Google Workspaceで有料プランを使っている場合は30分が最大です。この時間を超えて処理を継続したい場合は今回紹介する工夫が必要です。
また、今回のやり方は 分割可能 なタスクであることが大前提です。
やり方
下準備
ざっくりやり方を紹介すると、処理をタイムアウトしない範囲に分割することです。
分割したタスクをスプレッドシートの行に記載する
このように予め分割したタスクをスプレッドシートに記載しておきます。処理内容によって、行に書くべきデータが異なりますので定義調整をしてください。
スクリプトの排他制御
次は、スクリプトです。まず、処理時間が長いのでスクリプトが同時に実行されないように排他制御をします。Google Apps Scriptでは、LockService.getScriptLock() でスクリプト自身の実行ロックが取れます。
スクリプト完了時もしくはエラー終了時にロックを開放するのを忘れないでください。
function sample() {
// ロックを取得
const lock = LockService.getScriptLock();
// ロックが取得できなければ終了
if (!lock.tryLock(1000)) {
const ui = SpreadsheetApp.getUi();
ui.alert("他のスクリプトが実行中です。後で再試行してください。");
return;
}
try {
// ここに処理を記述
Utilities.sleep(5000);
} finally {
// ロックを解放
lock.releaseLock();
}
}
スクリプトをボタンから実行
きちんとロックされているかを確認するために、スプレッドシートにボタンを配置します。ボタンはメニューの「挿入」→「図形描画」でそれっぽいものを作ります。
このボタンを適当に配置
ボタンにスクリプトの実行を割り当てる
実行したい関数名を入力
あとは配置した「実行」ボタンを押下すると指定関数が起動します。連続でクリックしたり、別ユーザが同時にクリックをしてもきちんと排他制御されているのを確認することができます。
行ごとに処理を行う
続いて、行ごとにループ処理を行います。
getLastRow で入力されている最終行を取得し、AとB列すべての範囲のデータを取得します。その後、ループ処理をして、A列に記入はあるが、B列が空欄のものを未処理とみなす条件分岐を入れます。
処理を完了した行にはB列に現在の時刻を記録することで、完了フラグとします。
+const START_ROW = 2; // データ取得開始行
+
function sample() {
// ロックを取得
const lock = LockService.getScriptLock();
@@ -9,7 +11,25 @@ function sample() {
return;
}
+ const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
+ const sheet = spreadsheet.getActiveSheet();
+
try {
+ const lastRow = sheet.getLastRow();
+ const startIndex = START_ROW;
+ const dataRange = sheet.getRange(`A${startIndex}:B${lastRow}`);
+ const data = dataRange.getValues();
+ for (let i = 0; i < data.length; i++) {
+ // A列が空でなく、B列が空の場合だけ処理を行う
+ if (data[i][0] !== "" && data[i][1] === "") {
+ const doneDateTime = Utilities.formatDate(
+ new Date(),
+ Session.getScriptTimeZone(),
+ "yyyy-MM-dd HH:mm:ss",
+ );
+ sheet.getRange(i + startIndex, 2).setValue(doneDateTime);
+ }
+ }
Utilities.sleep(50);
} finally {
// ロックを解放
これでタイムアウトを考慮しない処理の流れは完成しました。
タイムアウトする前に次回予約
6分あるいは30分のタイムアウトを回避するためには、タイムアウトする前に処理を次回予約します。Google Apps Scriptにはトリガーという機能があり、指定した時刻に処理を予約することができます。
ScriptApp.newTrigger("sample")
.timeBased()
.after(1000 * 60) // 1分後
.create();
最短で1分後（といってもGoogle Apps Scriptの時間トリガーはかなり幅があるので、1-2分後くらいの感覚）に、指定関数を実行するように予約することで、タイム・アウトしてしまう前に処理を中断して、トリガーで再開させることで結果的にタイムアウトを防ぐことができます。
処理時間を図って予約処理をする
最後に、タイムアウトする前にトリガーを予約したいので、処理時間を計測するように修正します。
今回はデモとして、タイムアウト判定の時刻を10秒にしています。実際には6分よりも余裕を持った時刻を設定してください。また、わざと処理を遅らせるためにループするたびに2秒間待機するようにしています。
const START_ROW = 2; // データ取得開始行
+const TIME_LIMIT = 10; // スクリプト実行時間制限(秒)
function sample() {
+ // 処理開始時刻
+ const startTime = new Date().getTime();
+
// ロックを取得
const lock = LockService.getScriptLock();
@@ -20,6 +24,14 @@ function sample() {
const dataRange = sheet.getRange(`A${startIndex}:B${lastRow}`);
const data = dataRange.getValues();
for (let i = 0; i < data.length; i++) {
+ // 今の時刻を取得
+ const currentTime = new Date().getTime();
+ const seconds = (currentTime - startTime) / 1000;
+ // もしタイムアウトを過ぎそうだったら次回のトリガーを設定して終了
+ if (seconds > TIME_LIMIT) {
+ ScriptApp.newTrigger("sample").timeBased().after(1).create();
+ return;
+ }
// A列が空でなく、B列が空の場合だけ処理を行う
if (data[i][0] !== "" && data[i][1] === "") {
const doneDateTime = Utilities.formatDate(
@@ -28,9 +40,9 @@ function sample() {
"yyyy-MM-dd HH:mm:ss",
);
sheet.getRange(i + startIndex, 2).setValue(doneDateTime);
+ Utilities.sleep(2000); // デモ用に2秒わざと待機
}
}
- Utilities.sleep(50);
} finally {
// ロックを解放
lock.releaseLock();
予約実行したトリガーはGoogle Apps Scriptの管理画面から確認することができます。
トリガー履歴をクリア
指定時刻に実行するトリガーなので、一度実行されると無効扱いになります。スクリプトを実行するたびにトリガー履歴が残り続けるのが嫌な場合は、トリガーをクリアにするように改善することもできます。
const seconds = (currentTime - startTime) / 1000;
// もしタイムアウトを過ぎそうだったら次回のトリガーを設定して終了
if (seconds > TIME_LIMIT) {
+ const triggers = ScriptApp.getUserTriggers(
+ SpreadsheetApp.getActiveSpreadsheet(),
+ );
+ for (const trigger of triggers) {
+ ScriptApp.deleteTrigger(trigger);
+ }
ScriptApp.newTrigger("sample").timeBased().after(1).create();
return;
}
以上が、処理時間が長くてタイムアウトしてしまうGoogle Apps Scriptを分割しながら最後まで実行する方法でした。
おまけ：効率化したい場合
これだけでも処理は問題なく進むのですが、もしスプレッドシートの行が数万以上ある場合には、データの取得方法を効率的にしたほうが良いです。Google Apps Scriptにはスクリプト プロパティという、スクリプト自体にデータをもたせる機能があります。データと知っても単に文字列でKey-Valueというだけですが、ここに 最後に処理した行番号 を記録することで、トリガーで処理を再開するときに、どこから再開すればいいかがわかります。
つまり、すべての行を取得せずに済むので、メモリ効率が良くなります。
こんな感じでスクリプト プロパティを設定できます。
PropertiesService
.getScriptProperties()
.setProperty("key-name", "value");
組み込むとこんなコードになります。
try {
const lastRow = sheet.getLastRow();
- const startIndex = START_ROW;
+ const startIndex =
+ Number(PropertiesService.getScriptProperties().getProperty("nextIndex")) +
+ START_ROW;
const dataRange = sheet.getRange(`A${startIndex}:B${lastRow}`);
const data = dataRange.getValues();
for (let i = 0; i < data.length; i++) {
@@ -35,6 +37,10 @@ function sample() {
for (const trigger of triggers) {
ScriptApp.deleteTrigger(trigger);
}
+ PropertiesService.getScriptProperties().setProperty(
+ "nextIndex",
+ i.toString(),
+ );
ScriptApp.newTrigger("sample").timeBased().after(1).create();
return;
}
@@ -49,6 +55,8 @@ function sample() {
Utilities.sleep(2000);
}
}
+
+ PropertiesService.getScriptProperties().deleteProperty("nextIndex");
} finally {
// ロックを解放
lock.releaseLock();
スクリプトを実行してトリガー予約が完了したあとに、Google Apps Scriptの管理画面から「プロジェクトの設定」画面の一番下にあるスクリプト プロジェクトを確認すると記録した行番号が確認できます。
次回からはこの番号を開始行として処理をすることになります。
完成したすべてのスクリプトはこちら
const START_ROW = 2; // データ取得開始行
const TIME_LIMIT = 10; // スクリプト実行時間制限(秒)
function sample() {
// 処理開始時刻
const startTime = new Date().getTime();
// ロックを取得
const lock = LockService.getScriptLock();
// ロックが取得できなければ終了
if (!lock.tryLock(1000)) {
const ui = SpreadsheetApp.getUi();
ui.alert("他のスクリプトが実行中です。後で再試行してください。");
return;
}
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getActiveSheet();
try {
const lastRow = sheet.getLastRow();
const startIndex =
Number(PropertiesService.getScriptProperties().getProperty("nextIndex")) +
START_ROW;
const dataRange = sheet.getRange(`A${startIndex}:B${lastRow}`);
const data = dataRange.getValues();
for (let i = 0; i < data.length; i++) {
// 今の時刻を取得
const currentTime = new Date().getTime();
const seconds = (currentTime - startTime) / 1000;
// もしタイムアウトを過ぎそうだったら次回のトリガーを設定して終了
if (seconds > TIME_LIMIT) {
const triggers = ScriptApp.getUserTriggers(
SpreadsheetApp.getActiveSpreadsheet(),
);
for (const trigger of triggers) {
ScriptApp.deleteTrigger(trigger);
}
PropertiesService.getScriptProperties().setProperty(
"nextIndex",
i.toString(),
);
ScriptApp.newTrigger("sample").timeBased().after(1).create();
return;
}
// A列が空でなく、B列が空の場合だけ処理を行う
if (data[i][0] !== "" && data[i][1] === "") {
const doneDateTime = Utilities.formatDate(
new Date(),
Session.getScriptTimeZone(),
"yyyy-MM-dd HH:mm:ss",
);
sheet.getRange(i + startIndex, 2).setValue(doneDateTime);
Utilities.sleep(2000);
}
}
PropertiesService.getScriptProperties().deleteProperty("nextIndex");
} finally {
// ロックを解放
lock.releaseLock();
}
}
Discussion