0

New to writing Google scripts. Have found similar questions but none with answers that I could make work. I want the value of the cell in column "H" of a given row on sheet "main" to be set to "Y" when the cell in column "G" of the same row is edited to be "y". I can not use a formula for this, as I need the values in "H" to remain after those in "G" are deleted. Current code I am working with is below. Returns no errors but also does not change anything in the sheet. It is not written as onEdit because I am using a separate onEdit function to call multiple functions (including this one when it is correct).

function myFunction3() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("main");
var range = sheet.getActiveCell();
var columnNumberToWatch = 7; // column A = 1, B = 2, etc.
var valueToWatch = "y";
var right = range.offset(0,1);

if (range.getColumn() == columnNumberToWatch && range.getValue() == 
valueToWatch) { 
sheet.getRange(right).setValue('Y');
}
}
jasond
  • 17
  • 1
  • 6
  • Do this `var right = range.offset(0,1);` inside `if` block before you write to sheet – ra89fi Apr 15 '19 at 15:36
  • @ra89fi Tried some variations in attempt to figure out how to correctly add it. Can you please advise? This is what I currently have, returning a syntax error. if (range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch && var right = range.offset(0,1);) – jasond Apr 15 '19 at 16:11
  • See my code below – ra89fi Apr 15 '19 at 16:24

2 Answers2

1

Try this -

function myFunction3() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('main');
  var range = sheet.getActiveCell();
  var columnNumberToWatch = 7; // column A = 1, B = 2, etc.
  var valueToWatch = 'y';

  if (range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
    range.offset(0, 1).setValue('Y');
  }
}
ra89fi
  • 1,217
  • 1
  • 8
  • 9
  • That did it! Thanks so much! – jasond Apr 15 '19 at 17:00
  • at the expense of being "that guy" I have to ask... any chance that you might make sense of my other question here: https://stackoverflow.com/questions/55599028/script-to-move-range-showing-no-error-but-not-executing – jasond Apr 15 '19 at 17:29
0

In your description you seem to be saying that if the value in column G is "y" then you want to make the value in column "H" equal to "Y". The range = activeCell(), column number is 7, and valueToWatch = "y".

So what you need to do is get the value in range (e.g. the active cell) and change the value in the cell next to it to "Y".

So your conditional statement needs to start with the thing you are testing (e.g. range.getValue() and if that value is in the columnToWatch then set the value in the column next to it to "Y".

First thing you need to so is to get the column number of the active cell.

try range.getColumn() and assign it to a new variable (i.e. activeColumnNumber)

then start your if statement with range.getValue().

if (range.getValue == 'y' && activeColumnNumber == 7) etc., etc. it should work.

RobertLCrocker
  • 42
  • 2