CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
bjw
bjw

Reputation: 25

How can I get a different cell to activate depending on the contents of an edit?

In Google Apps Script for Google Sheets, I want to use a barcode scanner to input new data into a cell. If this new data matches what I am expecting, I want another cell to then be activated so I can continue to input data. This is being used to create an inventory, and the initial data being input (and what is being checked by the code) will be the name of the person using the scanner at that time. Here's what I have so far:

var ss = SpreadsheetApp.getActive()
var sheet1 = SpreadsheetApp.getActive().getSheetByName("Sheet1")

//Set active cell to A2 (cell below Name header) when file is opened.
function onOpen() {
  sheet1.getRange('A2').activate();
}   

function onEdit(e)  {
      if (e.range.getA1Notation() === 'A2') {
        var nameval = sheet1.getActiveCell.getValue();
        if (nameval == 'Alpha' || nameval == 'Beta')  {
          sheet1.getRange('D7').activate();
    }
        else  {
          sheet1.getRange('F1').activate();
    }}}

Unfortunately this does not seem to do anything - The value in the cell is accepted but the new cell did not activate when either of the activation cases were input.

Upvotes: 1

Views: 59

Answers (1)

Tanaike
Tanaike

Reputation: 201398

Modification points:

  • In your script, sheet1 is not declaread.
  • getActiveCell of sheet1.getActiveCell.getValue() is not run as the method.
  • I thought that nameval == 'Alpha' || nameval == 'Beta' might be written by includes.

When these points are reflected in your script, it becomes as follows.

Modified script:

function onEdit(e) {
  var range = e.range;
  var sheet1 = range.getSheet();
  if (range.getA1Notation() === 'A2') {
    var active = ['Alpha', 'Beta'].includes(range.getValue()) ? "D7" : "F1";
    sheet1.getRange(active).activate();
  }
}
  • In this modified script, when a value of 'Alpha' or 'Beta' is put to the cell "A2", the cell "D7" is activated. When a value except for 'Alpha' and 'Beta' is put to the cell "A2", the cell "F1" is activated.

Note:

  • This script is automatically run by the OnEdit simple trigger. So, when you directly run this script with the script editor, an error occurs. Please be careful about this.

References:

Upvotes: 1

Related Questions