arrayformula that can "skip" rows

google-sheets

Solution

I realize this is an old problem but I was searching for this today and made a script that works for me.

This script puts a formula in an adjacent cell when a cell is edited in the second column. This way you can just overwrite the formula if you need to input something manually and you don't need to have the formulas go into all of the rows beforehand. I had people accidentally edit the formula and mess it up most of the time when they were pre-filled, so this works better for me.

function onEdit() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetList = ["Sheet1","Sheet2","Sheet3"]; // list of sheets to run script on

    for (i = 0; i < sheetList.length; i++) {

        var sheetName = ss.getSheetByName(sheetList[i]);

        // only runs if sheet from sheetList is found
        if (sheetName != null) {

            var aCell = sheetName.getActiveCell();
            var col = aCell.getColumn();
            var adjacentCell = aCell.offset(0, -1);
            var formula = 'INPUT FORMULA HERE'; // put the formula you want in the adjacentCell here. Don't use it in an arrayformula

            // only runs if active cell is in column 2, if the adjacentCell is empty, and if the active cell is not empty(otherwise it runs if you delete something in column 2)
            if(col==2 && adjacentCell.getValue()=="" && aCell.getValue()!="") {

                adjacentCell1.setValue(formula);
            }
        }
    }
}

Problem

I need to introduce functionality into a google spreadsheet that will allow the user to edit the result of an array formula. The reason for the requirement is that an ARRAYFORMULA sets a default value for a group of cells, but the user sometimes needs to overwite these defaults. I'd like to know if this is even remotely possible. example: ``` Row(#)|Array_1 |Array_2 ------------------------------------ 1 |a |=arrayformula(Array_1) 2 |b |"" 3 |c |"" 4 |d |"" ``` So all rows in Array_2 are populated by an array formula. However the user wants to go directly to the second cell in Array_2 and change its value. Of course, by design ARRAYFORMULA will break. Is there some way to modify ARRAYFORMULA, so that it will simply skip over the cell that the user has edited and continue on its way as if nothing has happeded?

Original source