使用getNotes动态自动填充列 [英] Dynamically Autopopulate Column with getNotes

问题描述

这是来自

排序(A)后:

排序后(反向B):

This is a followup question from the previous post. Basically I want to autopopulate the entire column with getNotes on the first row similar to that of ArrayFormula. The previous poster have kindly provided this function:

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

Which then uses

getNotes("B1:B")

to populate the column cells with the respective notes from the B column. The problem though is that doing any sort of sorting on the columns does not dynamically change the locations of these Notes; the function still remembers the previously sorted locations. This function would also need to dynamically add notes to the cells as new rows get added automatically, and based on how it doesn't autopopulate properly, it does not do that either. Basically, the function runs just once to populate then I have to manually run the function again to get it to repopulate to correct positions. Help on this would be much appreciated.

As a side note, I'd also like to label this cell with the formula. I have tried

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

to see if it'll work similar to ArrayFormula and get first row as a label, and use the function for all the rows beneath in a column, but it doesn't seem to work.

解决方案

If you want to have the notes adjust to when the data moves, you need to use an onEdit trigger.

This function below will run whenever the range specified (which is B1:B in our case) is sorted.

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

A little change here is that, you modify the variable out to point where you want the notes be written, in this case, column C.

Before sorting:

After sorting (A):

After sorting (reverse B):

这篇关于使用getNotes动态自动填充列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！