How do I use Google Script If Statements?

Question

I am familiar with Excel VBA but am new to Google Script Editing

I am trying to use a simple IF statement to insert a new line above line 2 if cells A2 & B2 are NOT Blank.

  function AddaLine() {
if (B1 == false)
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('2:2').activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
  spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRange('A2').activate();
};

where cell B1 contains

=isblank(A2:B2)

or

function AddaLine() {
  if (A2, B2 != null)
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('2:2').activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
  spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRange('A2').activate();
};

Every combination I can think of returns errors. Any help would be appreciated.

Tanaike · Accepted Answer · 2021-10-31 23:06:38Z

3

I believe your goal is as follows.

From I am trying to use a simple IF statement to insert a new line above line 2 if cells A2 & B2 are NOT Blank., you want to insert a new row to the row 2 when the cells "A2" and "B2" are not empty.

In this case, how about the following modification?

Modified script:

function AddaLine() {
  var spreadsheet = SpreadsheetApp.getActive();
  var [a2, b2] = spreadsheet.getActiveSheet().getRange("A2:B2").getDisplayValues()[0];
  if (a2 != "" && b2 != "") {
    spreadsheet.getRange('2:2').activate();
    spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
    spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
    spreadsheet.getRange('A2').activate();
  }
}

Or, for example, as a simple script, how about the following modification?

function AddaLine() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var [a2, b2] = sheet.getRange("A2:B2").getDisplayValues()[0];
  if (a2 != "" && b2 != "") {
    sheet.insertRowBefore(2);
  }
}

In order to check whether the cells "A2" and "B2" are not empty, I compared the values retrieved from the cells.

Note:

As the additional information by advising from Rubén's comment, in your script, you use the following script.
```
  if (A2, B2 != null)
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('2:2').activate();
```
In this case, when the the condition is true, only the line of var spreadsheet = SpreadsheetApp.getActive(); is run. When you want to use the script below if (A2, B2 != null), please enclose them by {}. Ref

edited Oct 31, 2021 at 23:06

answered Oct 31, 2021 at 6:21

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wicket Over a year ago

As the OP isn't familiar with Google Apps Script / JavaScript, it might be important to highlight the use of {} on if-statements and the problems related to automatic line termination.

Yuri Khristich · Accepted Answer · 2021-10-31 08:46:36Z

Rather for educational purpose. If you want to check all elements of an array you can use every() method:

function AddaLine() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getRange("A2:B2").getDisplayValues().flat(); 
  if (data.every(x => x != '')) sheet.insertRowBefore(2);
}

JavaScript Array every()

Alternatively, in your case, you can use some() to check if some of the elements is empty:

function AddaLine() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getRange("A2:B2").getDisplayValues().flat();
  if (data.some(x => x == '')) return; // do nothing if there is an empty cell
  sheet.insertRowBefore(2);
}

JavaScript Array some()

Probably somme() will be a little bit more efficient since it doesn't need to check all elements of the array.

Collectives™ on Stack Overflow

How do I use Google Script If Statements?

2 Answers 2

Modified script:

Note:

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Modified script:

Note:

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related