CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
jasond
jasond

Reputation: 17

Set Value Based on Another Cell's Value in the Same Row

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

Upvotes: 0

Views: 4961

Answers (2)

ra89fi
ra89fi

Reputation: 1245

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

Upvotes: 2

RobertLCrocker
RobertLCrocker

Reputation: 40

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.

Upvotes: 0

Related Questions