Skip to content

Instantly share code, notes, and snippets.

@katz

katz/RecalculateSellectedCells.gs

Last active April 15, 2021 14:58
Show Gist options
  • Save katz/ab751588580469b35e08 to your computer and use it in GitHub Desktop.
Save katz/ab751588580469b35e08 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. katz revised this gist Aug 31, 2015. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions RecalculateSellectedCells.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -30,8 +30,8 @@ function recalculate(){
    originalFormulas.forEach(function(outerVal, outerIdx){
    valuesToEraseFormula[outerIdx] = [];
    valuesToRestoreFormula[outerIdx] = [];
    outerVal.forEach(function(innverVal, innerIdx){
    if('' === innverVal){
    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];
  2. katz renamed this gist Aug 31, 2015. 1 changed file with 0 additions and 0 deletions.
    0 Code.gs → RecalculateSellectedCells.gs
    View file
    File renamed without changes.
  3. katz created this gist Aug 31, 2015.
    59 changes: 59 additions & 0 deletions Code.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,59 @@
    /**
    * @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(innverVal, innerIdx){
    if('' === innverVal){
    //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);
    }