Dynamically Autopopulate Column with getNotes

Question

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.

To help us visualize, can you provide a sample sheet that shows your data, and expected output? — NightEye, Dec 31 '20 at 18:05
[Here](https://i.imgur.com/pFZRPiw.png) is what it should look like as a sample. However if I sort this by ID for example...[here](https://i.imgur.com/9bbPWWr.png) you can see that the notes in the "note" column are no longer respective to the cells left of it (4B has no notes to the right of it despite it containing notes). [This](https://i.imgur.com/gEBldZD.png) is the expected output. — user3245228, Dec 31 '20 at 18:14
I added a snippet sheet [here](https://docs.google.com/spreadsheets/d/1sSVhXgFwomB8a58Y4OIEiszrGn2ix5p4_k77yJfuqis/edit?usp=sharing), if you want to play around with it to get a feel for my question. Much appreciated for your support. — user3245228, Dec 31 '20 at 18:21

score 2 · Accepted Answer · answered Dec 31 '20 at 18:35

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):

Dynamically Autopopulate Column with getNotes

1 Answers1

Linked