Can you help me out with my data entry form in Google Sheet. I need a script for when after filling out a field and pressing enter it moves to the next empty field. Also, is there a way to grey out all other unused cells in my data entry form or lock them so they wouldn't be touched like we can't put anything on them for example.

CodePudding user response：

SUGGESTION

Note: This will only tackle the first question about moving active selection to the next empty field. As for the locking of cells, kindly post a separate question for it as every question in Stackoverflow should focus only on one specific concern/issue.

You may try a combination of

Note: This sample GIF was rendered in fast playback speed

Limitations:

Since this method calls the Spreadsheet App Service, there's a huge chance that it will run inconsistently/slowly if multiple triggers occur in a short period of time.
After pressing Enter, the selector jumps to the next row for a few seconds then it'll auto move to the next empty field.
Since we do not have a clear view of your actual sheet, this sample will only work if there is no other data on the sheet except for the one that is in your provided sample image. If this doesn't work as shown on the demonstration, kindly share your sample sheet so we can properly replicate your setup.

CodePudding user response：

Script:

Try

function onEdit(event){ 
  var sh = event.source.getActiveSheet();
  var rng = event.source.getActiveRange();
  if (sh.getName() == 'mySheetName'){                          // adapt
    var addresses = ["E7","H7","E10","H10","E13","H13","E16"]; // adapt
    var values = addresses.join().split(",");
    var item = values.indexOf(rng.getA1Notation());
    if (item < addresses.length - 1){ 
      sh.setActiveSelection(addresses[item   1]);
    }
  }
}

Note:

This way you can determine the order in which the cells are selected.

If you have a script that copies cells into a master data sheet, you can take advantage of the range list. (by the way, you can find