Secure cells in a way that I can only add +1 to them

Question

I have a bunch of cells with numbers in them. I want them to secure them in a way that I can only add +1 to them. Now I do this manually (4 becomes 5 etc.etc.).

Assuming Google Spreadsheets doesn't have nifty up/down arrow for a cell increasing/decreasing +1 / -1, I think I'm supposed to use a script.

Something like this

function increment() {  SpreadsheetApp.getActiveSheet().getRange("F5").setValue(SpreadsheetApp.getActiveSheet().getRange("F5").getValue() + 1);
}

But if I'm using this script (creating numerous drawings (grrr) ), I'm supposed to create x of the same functions? Seems a bit dumb.

This gives me errors

function increment(mycell) {  SpreadsheetApp.getActiveSheet().getRange(mycell).setValue(SpreadsheetApp.getActiveSheet().getRange(mycell).getValue() + 1);
}

Usage: increment(F5)
Error: function increment(F5) not found.

?

Answer 1

The problem about your approach is the recursion

Sheet formulas are by default recalculated, so should you type into a cell a formula that increments the value in another cell by 1 - once the value increases, this will trigger the formula recalculation and request the value to increase again and so on.

So you cannot implement your idea in this way

Workaround

• Create a function that increments the value of the current cell
• You bind this function e.g. to a button and trigger execution after you click into the desired cell and clicked on the button

Sample function:

function increment() {  
  SpreadsheetApp.getActiveSheet().getCurrentCell().setValue(SpreadsheetApp.getActiveSheet().getCurrentCell().getValue() + 1);
}

To create a button:

• Go from the Sheets UI on Insert->Drawing
• Draw a shape of your choice and click on Save and close
• Click on the three vertical dots in the right upper corner of the drawing
• Select Assign script
• Type in the name of the function (without ())
• Click into the cell the value of which you want to increment
• Click onto your drawing to run the script - done!