Google-sheets – How to skip 2 rows every time the cell’s content gets > 5 character

google sheetsgoogle-apps-script

I'm trying to make google sheet skip 2 rows every time any active cell from column A gets > 5 characters in length.

Here's the process I thought of:

  1. The user types up to 5 characters in cell A1.
  2. Upon the user typing the 6th character in A1, App Script automatically 'presses Enter' 3 times.
  3. The user can go on typing on cell A4.

So the idea is for app script to keep track of the number of characters the user inputs in any cell. And upon the of '6th character entered in any cell' have App Script automatically press Enter 3 times.
Such that the user doesn't have to manually keep track of the character number in cells and doesn't have to manually press Enter 3 times to skip 2 rows every time the number of characters in cells get > 5.

I've tested the following script (customised from this earlier answer by Jacob Jan Tuinstra):

How to limit the length of data in a cell in Google Sheets? ):

function Skip2rows() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = activeSheet.getActiveCell();
  
  if(cell !== ""){
    var activeSheet = SpreadsheetApp.getActive();
    activeSheet.getCurrentCell().offset(2, 0).activate();
  }
};




function onEdit(e) {

      var activeSheet = e.source.getActiveSheet();
      var cell = e.range;
      var inputValue = e.value;
      var limit = 5;
      
      if(e.value.length > limit) {
        e.range.setValue(e.value.substring(0, limit) + "...");
        e.range.getCurrentCell("Remaining text was: \n....." + e.value.substring(limit)).offset(2, 0).activate();
      }
    }

How can I get it to work?

Thanks in advance.

Here the demo sheet:
Skip 2 rows every time the cell length > 5 characters

Best Answer

The OFFSET method is the one to use, just build it into the onedit script

  • Define a new cell 2 rows down from the existing cell (var newcell = cell.offset(2, 0);)
  • SETVALUE into that cell
function onEdit(e) {

      var activeSheet = e.source.getActiveSheet();
      var cell = e.range;
      var inputValue = e.value;
      var limit = 5;
      
      if(e.value.length > limit) {
      // use OFFSET create the new destination out - down 2 cells
      var newcell = cell.offset(2, 0);
      // Logger.log("DEBUG: the range = "+cell.getA1Notation()+", and the offset range = "+newcell.getA1Notation())
      
      // clear the exist cell
      cell.clear();
      
      // populate the new cell
      newcell.setValue(inputValue)
        
      }
}
Related Topic