ARRAYFORMULA with getNotes

1
votes

Is it possible to use an Arrayformula to populate the entire column on the formula "getNotes"? https://developers.google.com/apps-script/reference/spreadsheet/range#getNotes()

So far I've added the small function on Apps Script as follows:

function getNotes(cell)
{
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var range = ss.getRange(cell)
   return range.getNotes();   
}

Then populated the column's first cell as follows:

=ARRAYFORMULA(IFERROR(getNotes(Address(row(B1:B), column(B1:B)))))

which does not seem to work. For the record,

=getNotes(Address(row(B2:B), column(B2:B)))

does work fine.

1
javascriptgoogle-apps-scriptgoogle-sheets
In your script, for example, when =getNotes("B2:B") is put to a cell, will the result be the same with your goal? - Tanaike
My goal is for the formula to get the cell's notes to populate the respective cell in its column. For example, the notes in the cell B2 should populate the P2 cell, B10 to P10, and so on. I just thought that since ArrayFormula took the range from B1:B, that would be the way to go. If that's not the case, I would be grateful to see the correct approach. - user3245228
Added a part 2 to the question here as per advise of the answerer for future posterity: stackoverflow.com/questions/65524033/… - user3245228

1 Answers

2
votes

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:

updated answer

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

enter image description here

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:

example

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

example2

This is a useful link to see how this getRange method works.