Google-sheets – How to restrict the number of entries for a particular column in shared spreadsheet using Google Drive

Simple triggers like onOpen and onEdit have restrictions.

In contrary of onOpen and onEdit that are reserved words onChange it's not, but its commonly used to name an installable trigger to be called by the spreadsheet change event. In the same way it's possible to set functions to be called ty the the spreadsheet open and change events which not have the restrictions of simple triggers.

We could create simple triggers manually or programmatically.

For further details please read Triggers and Events Guide.

I've shared a sheet for you to play with here. There is a fourth sheet to help write the second solution

The simple solution (with only numeric ids) is quite straightforward:

=MAX(Sheet1!A2:A,Sheet2!A2:A, Sheet3!A2:A)

(This assumes each column has a title)

The situation with a prefix works like this:

="R" & MAX(
  ARRAYFORMULA(IF(ISBLANK(Sheet1!B2:B), 0, 
                VALUE(RIGHT(Sheet1!B2:B, LEN(Sheet1!B2:B)-1)))), 
  ARRAYFORMULA(IF(ISBLANK(Sheet2!B2:B), 0, 
                VALUE(RIGHT(Sheet2!B2:B, LEN(Sheet2!B2:B)-1)))),
  ARRAYFORMULA(IF(ISBLANK(Sheet3!B2:B), 0, 
                VALUE(RIGHT(Sheet3!B2:B, LEN(Sheet3!B2:B)-1))))
)

What I'm doing here is:

1. Use an arrayformula to repeat a function across each of the cells in a range.
2. Check that cell is not blank as B2:B means every cell from B2 down in the B column
3. If it is blank, assign it 0, which will mean the returned range is guaranteed to have values
4. Otherwise, remove the first character from "R...", then use VALUE to convert it to a number
5. Repeat for each sheet
6. Get the MAX value from the resulting ranges
7. Add R to the front

This assumes the prefix is constant and only one character.