Updated answer based on your comment:
If you want to transpose the full range of columns and pass the full column B "B1:B", you can use this instead:
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();
}
Essentially, this script will find the last cell that has a note and return until that cell instead of the full range:
You can also use getNotes("B1:B") which will work in the same way, but it will spread the values in a column instead of a row.
Explanation:
As Tanaike mentioned in his comment, your current code:
function getNotes(cell)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getRange(cell)
return range.getNotes();
}
requires to pass the desired range as string:
=getNotes("B1:B")
You don't need an arrayformula as this custom function returns an array itself.
You can then do use built-in google sheets formulas if you want, like transpose:
Alternative approach:
You can use the getRange(row, column, numRows, numColumns) method of the sheet object instead which allows you to pass numbers if you want:
function getNotes(rs,cs,re,ce)
{
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = ss.getRange(rs,cs,re,ce);
return range.getNotes();
}
and then call it like that =getNotes(1,2,3,1):
This is a useful link to see how this getRange method works.
