google-apps-script - 使用 getNotes 动态自动填充列

Question

这是上一篇文章的后续问题。基本上我想在第一行使用类似于ArrayFormula 的 getNotes 自动填充整个列。之前的海报已经提供了这个功能：

function getNotes(rng){
  const ss = SpreadsheetApp.getActive().getActiveSheet();
  const index = ss.getMaxRows()-ss.getRange(rng).getNotes().flat().reverse().findIndex(v=>v!='');
  const range = ss.getRange(rng+index);
  return range.getNotes();
}

然后使用

getNotes("B1:B")

用 B 列中的相应注释填充列单元格。但问题是对列进行任何排序都不会动态更改这些注释的位置；该函数仍会记住先前排序的位置。此函数还需要在自动添加新行时动态地向单元格添加注释，并且基于它无法正确自动填充的方式，它也不会这样做。基本上，该函数只运行一次来​​填充，然后我必须再次手动运行该函数以使其重新填充到正确的位置。对此的帮助将不胜感激。

作为旁注，我还想用公式标记这个单元格。我努力了

IF(ROW(A:A)=1,"Label",getNotes("B1:B"))

看看它是否会类似于 ArrayFormula 并获取第一行作为标签，并对列中的所有行使用该函数，但它似乎不起作用。

标签： google-apps-scriptgoogle-sheets

Answer 1

如果您想让笔记适应数据移动的时间，您需要使用onEdit触发器。

只要指定的范围（B1:B在我们的例子中）被排序，下面的这个函数就会运行。

function onEdit(e){
  // Exit if edited range is not on column 2
  if (e.range.getColumn() != 2) return;

  const rng = "B1:B";
  const out = "C";
  const ss = SpreadsheetApp.getActive().getActiveSheet();
  const index = ss.getMaxRows()-ss.getRange(rng).getNotes().flat().reverse().findIndex(v=>v!='');
  const range = ss.getRange(rng+index);

  range.getNotes().forEach(function(note, i){
    ss.getRange(out+(i+1)).setValue(note[0]);
  });
}

这里的一个小变化是，您修改变量out以指向您希望写入注释的位置，在本例中为 column C。

排序前：

排序后（A）：

排序后（反向B）：