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

score 1 · Answer 1 · answered Dec 31 '18 at 06:34

1

I want the output to go to column T and the row should remain the same.

Change , 1, to , 20,.

answered Dec 31 '18 at 06:34

pnuts

58,317
11
87
139

brief but accurate and quick. tks – Pat Stewart Dec 31 '18 at 22:18

CalamitousCode · Accepted Answer · 2019-01-02T05:15:27.693

0

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

The first is to exactly match the sheets name to a string.
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);
  }
}

edited Jan 02 '19 at 05:15

answered Dec 31 '18 at 14:02

CalamitousCode

1,324
1
14
21

Thanks cOde okay, I see how that works now . This raises two new questions 1) Can I use the column number from the sheet and express the output column in terms of an offset to the right (by adding how many columns I want to move to the right) to the column number or to the left by subtracting from it? 2) the string needs to include a tab and a sheet name I think. what is the syntax for a tab within a sheet? I tried the url but that didn't work. – Pat Stewart Dec 31 '18 at 21:31
There is an offset method. The reference can be found here. https://developers.google.com/apps-script/reference/spreadsheet/range. For your second question, you may need to create another question and clarify a bit. Not sure I understood exactly what you are asking. – CalamitousCode Dec 31 '18 at 21:36
Also, yes you can add and subtract numbers from the references in the getRange() method. – CalamitousCode Dec 31 '18 at 21:38
hi cOde if (eventRange.getColumn() == 14 && sheetName == 'yourSheetNameHere') { The above line is giving me trouble with ‘yoursheetnamehere’ Mainly its the syntax of 'Yoursheetnamehere' that I think is the problem. If the file is named X, and there are three tabs in the file Sheet A, Sheet B, Sheet C, changing your ‘yoursheetnamehere’ to Sheet B surround by nothing, single quotes, double quotes, with underlines in the spaces or blanks, does not seem to stop it from running in Sheet A or Sheet C. – Pat Stewart Jan 01 '19 at 19:52
Would you share your new code in an edit on the question (at the end)? Also, the sheet name should always be surrounded by quotes. Single or double doesn't matter unless the sheet name itself contains either of those. – CalamitousCode Jan 01 '19 at 21:52
Nope that wasn't it it seems to like the string minus quotes etc. I am getting a missing ) after line 6 error on lin 6 but the script works, In my prior programming experience, you have to be absolutely right or things fails. so how come this runs? – Pat Stewart Jan 01 '19 at 22:24
If you want my help, I need to see your current code. As my previous comment, please add it to the end of your question. – CalamitousCode Jan 01 '19 at 22:27
HI cOde,I have pasted the exact script in to the original question. The problem is errors in the running the script in the script editor and I know that sooner or later it will be a problem if I have bad sybtax, which is what I assume to be the case. thanks – Pat Stewart Jan 02 '19 at 04:48
Have updated my answer. Also, it would never like the string without quotes. It just bugged out on a different line when you remove the quotes. – CalamitousCode Jan 02 '19 at 05:16
Thanks Code it is all working now. This is an excellent resource and your answers are wonderfully complete! – Pat Stewart Jan 02 '19 at 23:54

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

2 Answers2