Moving cursor to cell below in google sheets automatic when datainput amount = 1-CodePudding

Im making a registration kind of google sheets project, where the amount of input is quite high, however the input in each cell only needs to be 1 number. It would therefore be usefull to have a script, which made the cursor jump to the cell below after input of 1 number.

efunction onEdit(e) {
var sheet = e.source.getActiveSheet();
var activeCell = sheet.getActiveCell();
var col = activeCell.getColumn();
var row = activeCell.getRow();
var value = activeCell.getValue();
if (value.length == 1) {
 sheet.getRange(row   1, col).activate();
  }
}

However this only makes the cursor jump to the cell below the one which the input has been made, when another action is made, like clicking on 3rd cell. therefore not making the registrations easier.

Hope you can help.

CodePudding user response：

I am afraid that it is not possible to recognize what the users are typing until the edit is completely made. What you can do instead is to recognize if the value they have introduced is longer than 1 character and edit the cell by taking only the first character of what they introduce.

You can try the following script:

function onEdit(e) {
  var ss = e.source;
  var val = e.value;
  var r = e.range;

  if(val.length>1)
  {
    var x  = val.substring(0,1);
  }
  r.setValue(x);
}

References:

CodePudding user response：

The Google Sheets onEdit(e) trigger only runs when the user completes their data entry in a cell, usually by pressing Enter or Tab. You cannot watch individual characters as they are being entered.

What you can do, however, is let the user enter longer strings of digits in the cell, and when Enter is finally pressed, put each one of those digits in a cell of its own. You can then move the selection after the last digit that was thus filled down.

To do that, use String.split(), like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* Watches column Sheet1!A2:A as it is edited and splits digit strings such
* as 321654 or 321 654 into single digits 3, 2, 1, 6, 5, 4, and puts each
* digit in a cell of its own, stacking them vertically, starting at the
* cell where the digit string was entered.
* Moves the selection to the cell after the cell where last digit was put.
* Will happily overwrite values in cells under the cell that was edited.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  // version 1.0, written by --Hyde, 2 January 2023
  //  - see https://stackoverflow.com/a/74986570/13045193
  if (!e) throw new Error('Please do not run the onEdit(e) function in the script editor window.');
  if (!e.value
    || e.value.match(/[^\d\s]/i)
    || e.range.columnStart !== 1 // column A
    || e.range.rowStart < 2
    || !e.range.getSheet().getName().match(/^(Sheet1)$/i)) {
    return;
  }
  const numbers = e.value
    .replace(/\s/g, '')
    .split('')
    .map(n => [n]);
  e.range
    .offset(0, 0, numbers.length, 1)
    .setValues(numbers)
    .offset(numbers.length, 0, 1, 1)
    .activate();
}