7
votes

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?

2
yes the point was to preserve arrayformula. but i decided to just close the thread since its a waste of time. i don't think there is anyone besides google devs who can introduce this type of functionality. - user27636
true. i asked the question before i coded a few custom functions in google sheets, and discovered that they don't recalculate by design. this means that for each time you want to use your "function" you have to hardcode a custom onEdit trigger. Seems like too much trouble, for what you get. You're welcome to share your solution and I'll accept the answer, but it seems impossible to create a reusable custom function that can be used all over the spreadsheet. - user27636
Yes that's unfortunately the only way to do it. Of course, if you use it extensively, performance will suffer. The beavior of custom functions in Google Sheets are TRWTF here. (Just one of several critical design flaws in the app) - user27636

2 Answers

1
votes

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

Will changing the value not throw out the output of the remaining formulas?

If not, you could set up 2 new tabs: one which will receive the user over-ride values, and another "reflection" tab which you populate with

IF(tabOverride!Rx:Cy, tabOverride!Rx:Cy, tabArray!Rx:Cy)

basically the new tabs are cloned layouts of your array tab, creating an override input layer, plus a presentation layer that uses the IF('override value exists', 'then show override', 'else show array out put') logic to return the desired values.

hope that makes sense!