Excel or Google sheets question

[Robin]

<snip>

And I'd say that goes in Spades, doubled and redoubled, for checkboxes in Google Sheets now I've discovered that you can't assign a script to a them.

[newshound]

+1.

I find even if I am just evaluating algebraic expressions in repeating rows, it is often best to break them down into simple stages, and use a separate column for each stage. Once you have debugged it and got it working, just hide the intermediate columns.

[John Rumm]

You can setup an onChange or onEdit event handler and just test for the current cell being one of interest...

[nothanks]

Thanks John - exactly the level I needed.

FWIW I've changed the original design to avoid the mixing of input and output in the same cell. I can now do what I need using the functions =IF (OR(H83,H89)=true,CHAR(9745),) and =ArrayFormula(SUM(COUNTIF(F75:AF75,{"true", CHAR(9745)}))), rather than having to run macros or VBA

[John Rumm]

Yup, that looks like a much simpler solution for this particular problem :-)

The scripting is good for many things, but realtime interaction with cells in sheets will always be a bit clunky since they run server side...

FWIW if you do need real time interaction in a google sheet, it will allow you to augment the UI using either a sidebar or a popup dialogue.

Both of those you can craft in HTML / Javascript (plus any common web framework if you fancy like bootstrap), and then they run client side. (There are hooks to pass stuff back and fourth between client and server).

So you could for example have a HTML form, with javascript events tied to different bits, that fill in other fields automatically as you enter stuff. Once you have you html, and the GAS script for communicating with it, then displaying it in the UI is just a case of:

const dialogHTML = HtmlService.createHtmlOutputFromFile( "yourhtmlfile" ) SpreadsheetApp.getUi().showModalDialog( dialogHTML, "You dialog name" )

[Robin]

On 21/03/2022 11:16, [e-mail] wrote: <snip>

Looks good.

And IMO the equivalent of Chesterton's "The night we went to Birmingham by way of Beachy Head" is just one of those things with this stuff :)