LoginSignup
26

More than 5 years have passed since last update.

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

Last updated at Posted at 2015-08-31

概要

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

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

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

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);
}


Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
26