Adding a button to formula recalculate all records in a sheet

To recalculate all formulas or the formula assigned in a field for all records without navigating to Design mode, you can add an action button to execute formula recalculation for all records by adding a simple script.

Please follow this guide to add your script:

Step 1: Open the Ragic workflow editor.

Right-click on the sheet name under the tab and select Javascript Workflow, which will take you to the Workflow Module.

Step 2: Switch the page being edited to the "Installed sheet scope".

Step 3: Modify and add the following code to your installed sheet scope and save.

Remember to change the '/sales/3' in the example code below to your own sheet path.

Example If your sheet url is 'www.ragic.com/sample/sales/3', the code would be written as following:



function recalc(){
    var pageSize = 1000; //read 1000 entries at a time
    var qMain = db.getAPIQuery('/sales/3');
    qMain.setUpdateMode();

    var mainAr = null, mainOffset = 0;
    while(mainAr==null || mainAr.hasMore()){
        qMain.resetData();
        qMain.setLimitSize(pageSize);
        qMain.setLimitFrom(mainOffset);
        mainAr = qMain.getAPIResults();

        var iterator = mainAr.iterator();
        while(iterator.hasNext()){
            var entry = iterator.next(); 
            entry.recalculateAllFormulas();
            entry.save();   
        }

        mainOffset += mainAr.getData().size();
    }
}

If you would like to only do recalculate for a single field, let's say with field id 1000001. You can use this instead:


function recalc(){
    var pageSize = 1000; //read 1000 entries at a time
    var qMain = db.getAPIQuery('/sales/3');
    qMain.setUpdateMode();

    var mainAr = null, mainOffset = 0;
    while(mainAr==null || mainAr.hasMore()){
        qMain.resetData();
        qMain.setLimitSize(pageSize);
        qMain.setLimitFrom(mainOffset);
        mainAr = qMain.getAPIResults();

        var iterator = mainAr.iterator();
        while(iterator.hasNext()){
            var entry = iterator.next(); 
            entry.recalculateFormula(1000001);//recalculate formula for only field id 1000001
            entry.save();   
        }

        mainOffset += mainAr.getData().size();
    }
}