带有getNotes的ARRAYFORMULA [英] ARRAYFORMULA with getNotes

问题描述

是否可以使用Arrayformula填充公式"getNotes"上的整个列?

您还可以使用 getNotes("B1:B")，它的工作方式相同，但是会将值分散在列而不是行中.

说明:

正如Tanaike在评论中提到的那样，您当前的代码:

 功能getNotes(单元格){var ss = SpreadsheetApp.getActiveSpreadsheet();var range = ss.getRange(cell)返回range.getNotes();} 

要求将所需范围作为字符串传递:

= getNotes("B1:B")

您不需要 arrayformula ，因为此自定义函数将返回数组本身.

然后，您可以根据需要使用内置的Google表格公式，例如转置:

替代方法:

您可以使用

这是一个有用的链接查看此 getRange 方法的工作原理.

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.

解决方案

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.

这篇关于带有getNotes的ARRAYFORMULA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！