Google Sheetで選択したセルを再計算させるApps Script

#概要
Googleスプレッドシートで計算式が沢山あるシートを開くと、たまに計算が正しく行われていないことがある。
計算式を一回消して再度設定すると正しく計算し直してくれるので、それを自動的に行うApps Scriptを書いた。

以下のAppsScriptをインストール後、再計算対象のセルを選択し、メニューから「Re-calculate selected cells」を選ぶとAppsScriptが再計算してくれる。

#下記のコードでなにをしているのかの解説

onOpen()でGoogle SheetのAdd-onsメニューに「Re-calculate selected cells」というメニュー項目を追加している。そのメニュー項目が押されたらrecalculate()という関数が呼び出される。

recalculate()の中では、まずSpreadsheetApp.getActiveRange()を呼び出し、スプレッドシート上で選択されているセルのレンジをactiveRangeとして取得する。
activeRangeに対してgetFormulas()を呼び出すことで、選択したレンジ内に設定されている数式群を二次元配列として取得している。

その後、数式を削除しactiveRange.setValues(valuesToEraseFormula)、再度数式を設定activeRange.setValues(valuesToRestoreFormula)することで、Googleスプレッドシートに数式を強制的に再計算させている。

なお、元々数式が設定されていないセルはSpreadsheetApp.getActiveRange()では空文字列が返ってくる。なので、activeRange.getValues()を実行して、セルの値を消さないような処理も行っている。

#実際のコード
Gist: katz/RecalculateSellectedCells.gsで公開している。

/**
 * @OnlyCurrentDoc  Limits the script to only accessing the current spreadsheet.
 */


/**
 * Adds a custom menu with items to show the sidebar and dialog.
 *
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('Re-calculate selected cells', 'recalculate')
      .addToUi();
}


/**
 * Force Spreadsheet to re-calculate selected cells
 */
function recalculate(){
  var activeRange = SpreadsheetApp.getActiveRange();
  var originalFormulas = activeRange.getFormulas();
  var originalValues = activeRange.getValues();
  
  var valuesToEraseFormula = [];
  var valuesToRestoreFormula = [];
  
  originalFormulas.forEach(function(outerVal, outerIdx){
    valuesToEraseFormula[outerIdx] = [];
    valuesToRestoreFormula[outerIdx] = [];
    outerVal.forEach(function(innerVal, innerIdx){
      if('' === innerVal){
        //The cell doesn't have formula
        valuesToEraseFormula[outerIdx][innerIdx] = originalValues[outerIdx][innerIdx];
        valuesToRestoreFormula[outerIdx][innerIdx] = originalValues[outerIdx][innerIdx];
      }else{
        //The cell has a formula.
        valuesToEraseFormula[outerIdx][innerIdx] = '';
        valuesToRestoreFormula[outerIdx][innerIdx] = originalFormulas[outerIdx][innerIdx];
      }
    })
  })
  
  activeRange.setValues(valuesToEraseFormula);
  activeRange.setValues(valuesToRestoreFormula);
}


/**
 * Runs when the add-on is installed; calls onOpen() to ensure menu creation and
 * any other initializion work is done immediately.
 *
 * @param {Object} e The event parameter for a simple onInstall trigger.
 */
function onInstall(e) {
  onOpen(e);
}