How do I use Google Script If Statements?-CodePudding

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)

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.

CodePudding user response：

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.

CodePudding user response：

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.