Fill Down Formula while ignoring empty cells- Google Scripts

user_17438392 0
Viewed 89

I am trying to write a script that will replace the following formula:

=ARRAYFORMULA(IF((BM3:BM <> "") * (BN3:BN <> ""), BM3:BM + BN3:BN,))

This is where I am at so far:

function Formula_Columns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("BP3").setFormula("=BM3 + BN3");
  var lr = ss.getLastRow();
  var fillDownRange = ss.getRange(3,68,lr-2);
  ss.getRange("BP3").copyTo(fillDownRange);
}

What I would like to happen is when the date is entered into column BM the formula will combine the date in column BM with the time set in column BN and place it into column BP. I need the script to add the function into BP only when a cell in BM has a date in it. As the script is written now, it enters 12/30/1899 0:00:00 if there is no date and time entered in column BM & BN respectively. (See screenshot below)

I would appreciate some help with this. Thank you in advance.

enter image description here

google-apps-script google-sheets
1 Answers

There are three ways I see how to solve this. See the methods below:

Script:

// Copying just the displayed values and returning the combined values
function writeValuesOnly() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var dates = ss.getRange(3, 65, lr - 2, 2).getDisplayValues();
  dates = dates.map(row => { 
    // if BM and BN is present
    if(row[0] && row[1]) 
      return [row[0] + ' ' + row[1]]; 
    // else, return blank
    else
      return [''];
  });
  ss.getRange(3, 68, dates.length, dates[0].length).setValues(dates);
  // make sure to format the whole column to date time
  ss.getRange(3, 68, lr - 2, 1).setNumberFormat("MM/dd/yyyy HH:mm:ss")
}

// Writing the ARRAYFORMULA on BP3 
// Then set the formatting of the column to Date Time
function writeArrayFormula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  ss.getRange(3, 68).setFormula(`=ARRAYFORMULA(IF((BM3:BM <> "") * (BN3:BN <> ""), BM3:BM + BN3:BN,))`);
  // make sure to format the whole column to date time
  ss.getRange(3, 68, lr - 2, 1).setNumberFormat("MM/dd/yyyy HH:mm:ss")
}

// Copying formula to all cells in the column until last row of column BM
function writeFormulaPerCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var colValues = ss.getRange("BM3:BM" + lr).getValues();
  var colLastRow = lr - colValues.reverse().findIndex(cell => cell[0] != '');
  // if BM and BN is present, populate
  ss.getRange(3, 68, colLastRow - 2, 1).setFormulaR1C1(`=IF(AND(R[0]C[-3] <> "", R[0]C[-2] <> ""),  R[0]C[-3] + R[0]C[-2], "")`);
  // make sure to format the whole column to date time
  ss.getRange(3, 68, lr - 2, 1).setNumberFormat("MM/dd/yyyy HH:mm:ss")
}

Comparison:

output

Note:

  • Using writeValuesOnly will not automatically update the value when BM and BN is updated.
  • If you want to use writeFormulaPerCell, you need to use setFormulaR1C1 instead since when it is copied to other rows/columns, the formula will automatically adjust based on the range.
  • Not using setNumberFormat (or not formatting the range output) might yield unexpected results such as showing float data type instead.

References:

user_161186860
Related