CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Matt
Matt

Reputation: 165

adapt script to set IF formula

I'm having some difficulty adapting a script I'm using to set a formula, and could use some help please.

The current script I'm using, which sets a simple formula starting in P1, and then every 26th row in Col P is fine, but I want to change the simple =AVL! formula, to an IF formula. The formulas set currently are very simple. For example the first three formulas set are below, and the script sets them in P1, P27, and P53. And so on every 26 rows down to P5175.

=AVL!BA3
=AVL!BA4
=AVL!BA5
           Etc down to...
=AVL!BA5175

Here's the script for that which is okay...

function setFormulas() {

  var sheet, startRow, i; 
  sheet = SpreadsheetApp.getActiveSheet();
  startRow = 1;
  i = 3;
  while(startRow < 5181) {
    sheet.getRange(startRow, 16).setFormula('=AVL!BA' + i);
    i++;
    startRow += 26;
  }
}

I want to change the formula set to set the IF formula below...

=IF(AVL!BA3<>"",AVL!BA3,"NO STAFF AVAILABLE!")
=IF(AVL!BA4<>"",AVL!BA4,"NO STAFF AVAILABLE!")
=IF(AVL!BA5<>"",AVL!BA5,"NO STAFF AVAILABLE!")

I've tried many different ways now, with no success, but I'm sure it's not hard, just my brain has stalled.

Can anyone help please?

Thanks in advance.

Matt

Upvotes: 1

Views: 64

Answers (1)

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

You just need to change the setFormula part to:

sheet.getRange(startRow, 16).setFormula('=IF(AVL!BA' + i + ' <> "", AVL!BA' + i + ', "NO STAFF AVAILABLE!")');

Upvotes: 3

Related Questions