Automatically drag formula in Google spreadsheet columns

Question

I've a Google spreadsheet to which data is added from a fusion table using the script given in this github link. I added two more columns at the right end of the spreadsheet in which one is filled using formula

    =COUNTIF(A$2:A$24,A2)

and other column by another formula. Currently, when every time the spreadsheet is updated with new rows, I am manually dragging the formula columns to update the data in them. Is it possible to update formula columns dynamically using script?. Ie, when the rows added the formula column also updated dynamically.

EDIT:

// evaluate project type and set identifier
function addCountIfFormulaToCell(){

    // add the id of your spreadsheet here
    var sss = SpreadsheetApp.openById('0AozvCNI02VmpdG5tb0pkUGdDR3djMm5NV0pYeThFbGc');

    // add the name of the sheet here
    var ss = sss.getSheetByName('Sheet1');

    // column you want to evaluate for the formula
    var columnToEvaluateAgainst = "A";

    // column you want to add the formula to
    var columnToAddFormulaTo = "H";

    // identifies the last row
    var lastRow = ss.getLastRow();

    // is the cell to evaluate in the last row
    var evaluateThisCell = columnToEvaluateAgainst + lastRow;

    // is the cell that gets the forumla in the last row
    var addFormulaToThisCell = columnToAddFormulaTo + lastRow;

    // this is my formula
    var projectFormula = "COUNTIF(A$2:$A,A2)";

    // grabs the cell that gets the forumla in the last row
    var ssCellToGetFormula = ss.getRange(addFormulaToThisCell);

    // sets the formula to the cell in the last row
    ssCellToGetFormula.setFormula(projectFormula);

};

Chris Keller Chris Keller · Accepted Answer · 2013-04-23T16:30:14

Pradeep… You could use an apps script that is triggered to run when a new row is added to your spreadsheet.

The script below is modifed from one I used to insert an IF formula into a cell in the last row that evaluate a value in another column.

Quickly:

Add the ID of your spreadsheet.
Add the name of your sheet.
I made a variable for the column I will be evaluating and the column where I will be adding the formula. You may or may not need this.
Use .getLastRow() to get the last row of the spreadsheet.
Create a range, which will pinpoint the cell I want to evaluate and the cell where I will add the formula
Create a variable for the formula.
Get the cell I will be adding the formula to.
Use .setFormula to add the formula to that cell.

This could probably be more efficient, and you might not be able to use this straight out the box, but it will give you an idea of some of the mechanisims available.

Chris K.

    // evaluate project type and set identifier
    function addCountIfFormulaToCell(){

        // add the id of your spreadsheet here
        var sss = SpreadsheetApp.openById('0ApI9xmBd0k....');

        // add the name of the sheet here
        var ss = sss.getSheetByName('Sheet1');

        // column you want to evaluate for the formula
        var columnToEvaluateAgainst = "B";

        // column you want to add the formula to
        var columnToAddFormulaTo = "C";

        // identifies the last row
        var lastRow = ss.getLastRow();

        // is the cell to evaluate in the last row
        var evaluateThisCell = columnToEvaluateAgainst + lastRow;

        // is the cell that gets the forumla in the last row
        var addFormulaToThisCell = columnToAddFormulaTo + lastRow;

        // this is my formula
        var projectFormula = "THIS IS MY FORMULA";

        // grabs the cell that gets the forumla in the last row
        var ssCellToGetFormula = ss.getRange(addFormulaToThisCell);

        // sets the formula to the cell in the last row
        ssCellToGetFormula.setFormula(projectFormula);

    };

Automatically drag formula in Google spreadsheet columns

2 Answers