填充公式而忽略空单元格- Google脚本

Question

我正在尝试编写一个脚本，它将替换以下公式：

=ARRAYFORMULA(IF(BM 3:BM <>“)*(BN 3:BN <>”)，BM 3:BM+BN 3:BN，)

这就是我目前所处的位置：

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);
}

我想要发生的是，当日期被输入到BM栏时，公式将把BM列中的日期与BN列中所设定的时间结合起来，并将其放入列BP。我需要脚本添加到BP的功能，只有当一个细胞在BM中有日期。由于脚本是现在编写，它进入12/30/1899年0:00:00，如果没有日期和时间分别输入BM和BN栏。(见下面的截图)

我希望能在这方面提供一些帮助。提前谢谢你。

​

​

Answer 1

我认为有三种方法可以解决这个问题。见以下方法：

剧本：

// 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")
}

比较：

​

​

注意：

• 使用writeValuesOnly不会在更新BM和BN时自动更新值。
• 如果要使用writeFormulaPerCell，则需要使用setFormulaR1C1，因为当它被复制到其他行/列时，公式将根据范围自动调整。
• 不使用setNumberFormat (或不格式化范围输出)可能会产生意外的结果，例如显示浮点数据类型。

参考文献：

• setNumberFormaat
• setFormulaR1C1