CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Stef Timmers
Stef Timmers

Reputation: 21

Returning formula in google script

In Google Spreadsheet in want to subtract one cell with another based on a value in yet another cell.

example:

If cell A1 = "Yes" 
  subtract B1 from C1 

If cell A1 = "No"
  subtract B1 from D1

If cell A1 = ""
  subtract B1 from C1

I think a have to build a little script for this one (not sure) and have come up with this:

function sub() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();

  var nRange = ss.getRangeByName("sub");
  var group = nRange.getValues();;

  var range;

    if ( group == "Yes" ) {
     range = '=Q15-N15' }
    else if ( group == "No" ) {
     range = "=Q15-M15"}
    else if ( group == "" ) {
     range = "=Q15-M15"}

    return range
    }

Now it returns the proper range, but the formula is not going in effect. The script pastes the range literally in the cell.

Upvotes: 2

Views: 3306

Answers (1)

Mogsdad
Mogsdad

Reputation: 45710

If you use .getValues() to read the content of a range, you will receive a two-dimensional array of values - even if there is only one cell in the range. If you really want just one value, use .getValue() instead.

Because you're getting an array, all three of your if tests evaluate to false, and none set a value for range. Try giving range a default value to start:

var range = 'Range not set';

Note: Custom functions return Values, they cannot set a cell's formula. Your function could return the result of a calculation, or a line of text to display in a cell. If you must set a formula, you'll have to do so using a function that's invoked some other way, and use Range.setFormula().

Upvotes: 1

Related Questions