How do I control output cell location in a google sheet using google script?

Question

I am working on a script for a Google sheet and learning scripts from scratch.

I've used the following script and changed the input column with success, but the output remains in column A. I want the output to go to column T and the row should remain the same.

The trigger type is good but it works in all the sheets so I need to limit the sheets it works in. How do I make the script work in one particular sheet and tab combination?

As long as I am asking about changing the output to the one column, can you explain the general method for determining the output column and row?

Thanks in advance.

Code, here is the exact script I am using. Once I Run the script in the editor it gives me an error on line 3 which is "var eventRange=event.range;"

that stops if i remove the quotataions from around the sheet name 'ticket to deliver' in line 6. but i get a new error when I run the script on line 6 "which says "missing ) after condition, line 6". Here is the code I am working with which was written by another poster here.

function onEdit(event) {

  var eventRange = event.range;
  var sheetName = eventRange.getSheet().getName();

  if (eventRange.getColumn() == 14 && sheetName == 'tickets to deliver') {

//if (eventRange.getColumn() == 14 && sheetName.match(/sheetName1|sheetName2/)) {

    // getRange(row, column, numRows, numColumns)
    var columnXRange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 21, eventRange.getNumRows(), 1);
    //second parameter changed from '1' to '21'. 21 represents column 'U'

    var values = columnXRange.getValues();

    for (var i = 0; i < values.length; i++) {
      if (!values[i][0]) {
        values[i][0] = new Date();
      }
    }
    columnXRange.setValues(values);
  }
}

Answer 1

You can manage the sheets the script affects by checking the sheet name. There are two options below.

1. The first is to exactly match the sheets name to a string.
2. The second (commented out) is to match the sheet's name against a regular expression.

Column adjustment from A to T commented on below.

function onEdit(event) {

  var eventRange = event.range;
  var sheetName = eventRange.getSheet().getName();

  if (eventRange.getColumn() == 14 && sheetName == 'yourSheetNameHere') {

//if (eventRange.getColumn() == 14 && sheetName.match(/sheetName1|sheetName2/)) {

    // getRange(row, column, numRows, numColumns)
    var columnXRange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 20, eventRange.getNumRows(), 1);
    //second parameter changed from '1' to '20'. 20 represents column 'T'

    var values = columnXRange.getValues();

    for (var i = 0; i < values.length; i++) {
      if (!values[i][0]) {
        values[i][0] = new Date();
      }
    }
    columnXRange.setValues(values);
  }
}

EDIT

After our conversation, I jumped on the computer to test the script.

Here's my working script. (Just removed the comments) there should be no change to the actual script.

Also, please note, you cannot just use the debug function as-is when testing an onEdit or onChange function that requires an event object. If you're going to use the debug, in this function, you'll need to change line 3 to var eventRange = SpreadsheetApp.getActive.getActiveRange();.

Please also note, the sheet name test string needs to be exact and case-sensitive.

function onEdit(event) {

  var eventRange = event.range;
  var sheetName = eventRange.getSheet().getName();

  if (eventRange.getColumn() == 14 && sheetName == 'tickets to deliver') {

    var columnXRange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 21, eventRange.getNumRows(), 1);

    var values = columnXRange.getValues();

    for (var i = 0; i < values.length; i++) {
      if (!values[i][0]) {
        values[i][0] = new Date();
      }
    }
    columnXRange.setValues(values);
  }
}