Google Sheet AppScript - Return Formula if Cell If Blank-CodePudding

I'm trying to come up with a script that would add this formula ("=iferror(filter(Directors!$A$3:$A$55,Directors!D$4:D$56=$A5),)") to an empty cell (let's consider that the cell is question is "D6")

I've tried the below script but it will add the formula even if the cell isn't empty. If I have something already written in that cell, it will override it and add the formula, but I'm looking to only add it if the cell is blank.

This is the script I tried using.

function Week1IFBlankFormulas() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange("D6").activate(); spreadsheet.getCurrentCell().setFormula('=iferror(filter(Directors!$A$3:$A$55,Directors!D$4:D$56=$A5),)'); spreadsheet.getActiveRange().autoFill(spreadsheet.getRange("D6"), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES); if('D6' > 0,""); };

Don't know if you need it or not but the sheet is called "Week 1 Populated"

Any help is greatly appreciated. Thank you everyone!

CodePudding user response：

This script should work for you:

function Week1IFBlankFormulas() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange("D6").activate();
  if(spreadsheet.getCurrentCell().isBlank()) return;
  spreadsheet.getCurrentCell().setFormula('=iferror(filter(Directors!$A$3:$A$55,Directors!D$4:D$56=$A5),)');
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange("D6"),SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
};

I just tested it myself and it works for me. You had your if statement at the end of your script, and with the wrong syntax, so it wasn't executing properly.

Please let me know if you have any issues with this

CodePudding user response：

Based on the script in the previous answer but after testing it the cell was filled even with the text in it.

I have modified the if statement to only allow the formula to be in when the cell is empty:

function Week1IFBlankFormulas() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange("D6").activate();
  if (spreadsheet.getCurrentCell().isBlank()) {
    spreadsheet.getCurrentCell().setFormula('=iferror(filter(Directors!$A$3:$A$55,Directors!D$4:D$56=$A5),)');
    spreadsheet.getActiveRange().autoFill(spreadsheet.getRange("D6"), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  } else {
    return;
  }
};