0

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

1
  • Upon the user typing the 6th character in A1, App Script automatically 'presses Enter' 3 times. @JacobJanTuinstra himself makes the point that "Not while entering. Upon leaving it is possible."
    – Tedinoz
    Dec 2 '20 at 22:25
1

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)
        
      }
}
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.