Google スプレッドシートの数式エラーを Apps Script で検知する
🔸背景
Google のスプレッドシートは強力な、配列数式(ARRAYFORMULA や、 MAP/LAMBDA)や、外部シートを取り込む IMPORTRANGE関数などを備えており、大量のデータを扱う時にとても便利に利用させてもらっています。
しかし、スプレッドシートの利用者が、配列数式が展開するはずの領域に値をいれてしまったり、IMPORTRANGE の参照するスプレッドシートの権限を変更したりするとエラーが発生し、予期しない動作をすることがあります。
🔸ソリューション
スプレッドシート上の数式エラーを検知してアラートメールを出す関数を Apps Script で作成しました。
Apps Script の CreateTextFinder を使って 数式エラー(#N/A,#ERROR!,#REF!,#VALUE,#DUV/0!) を列挙し、メールアラートを出してくれます。この関数を Daily Trigger に設定しておくと、１日遅れですが数式エラーを検知できるようになります。
function FormulaErrorFinder() {
let ss=SpreadsheetApp.getActiveSpreadsheet()
let tfa=ss.createTextFinder('^[#](DIV[/]0[!]|N[/]A|ERROR!|REF[!]|VALUE[!])$').useRegularExpression(true)
let results=tfa.findAll()
let errortxt=''
for(let i=0;i<results.length;i++){
let cell=results[i]
errortxt=errortxt+`${cell.getSheet().getName()}!${cell.getA1Notation()}:${cell.getValue()}\n`
}
if(errortxt!=''){
let body=`下記スプレッドシートに数式エラーがあります。\n${SpreadsheetApp.getActiveSpreadsheet().getUrl()}\n\n${errortxt}`
GmailApp.sendEmail('sampleuser@example.com','スプレッドシートに数式エラーがあります',body)
}
}
出力結果：
下記スプレッドシートに数式エラーがあります。
https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxx/edit
シート1!D2:#DIV/0!
シート2!B2:#ERROR!
シート2!B3:#VALUE!
シート2!B4:#N/A
シート2!B5:#REF!
🔸アップデート版(1時間に一度実行対応)
１日に一度のチェックでは少なく感じ、ちょっと修正しました。
CacheService を利用して 1時間に一度、エラーチェックし、エラー内容に変更があったらメールでアラートします。CacheService は getDocumentCache で2時間前回の結果を保持しているので、1時間に一度のトリガーを使ってこの関数を呼び出してください。
function FormulaErrorFinderPerHour() {
let ss=SpreadsheetApp.getActiveSpreadsheet()
let tfa=ss.createTextFinder('^[#](DIV[/]0[!]|N[/]A|ERROR!|REF[!]|VALUE[!])$').useRegularExpression(true)
let results=tfa.findAll()
let errortxt_lasttime=CacheService.getDocumentCache().get('errortxt')
let errortxt=''
for(let i=0;i<results.length;i++){
let cell=results[i]
errortxt=errortxt+`${cell.getSheet().getName()}!${cell.getA1Notation()}:${cell.getValue()}\n`
}
CacheService.getDocumentCache().put('errortxt',errortxt,7200) // 120分 Cache を保持するので、1時間に一度、トリガー実行をすること。7200=2時間x60分x60秒
if(errortxt!='' && errortxt!=errortxt_lasttime){
let body=`下記スプレッドシートに数式エラーがあります。\n${SpreadsheetApp.getActiveSpreadsheet().getUrl()}\n\n${errortxt}`
GmailApp.sendEmail('sampleuser@example.com','スプレッドシートに数式エラーがあります',body)
}
}