从一个单元格获取笔记并插入另一个单元格 [英] Get notes from a cell and insert into another cell
问题描述
我在Google表格中有一些单元格，其中有些带有注释.
I have a range of cells in Google Sheets, some of them have notes attached.
如果在单元格上附加了便笺，则需要将该便笺放在一个单独的单元格中，并将该便笺的位置放在另一个单元格中.
If there's a note attached to a cell, I need to put the note in a separate cell, and put the location of that note in another cell.
我在其他地方找到了该脚本:
I found this script elsewhere:
function getNote(cell)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getRange(cell)
return range.getNote();
}
但是当我尝试使用它时，出现错误异常:找不到范围(第12行)."
but when I try to use it I get an error "Exception: Range not found (line 12)."
但是这个脚本只能让我半途而废，因为它只能得到笔记并将其放在单元格中.我还需要知道笔记来自哪个单元格.
But this script only gets me halfway there as it only gets the note and puts it in a cell. I also need to know what cell the note came from.
非常感谢您的帮助.
推荐答案
在函数 getNote()上方的脚本中，期望参数 cell
如果您只运行脚本而未从另一个函数/为其获取分配的 cell 值的环境中调用 getNote()，则脚本将因错误而失败您获得了.
In the script above the function getNote() expects the paramter cell
If you just run the script without calling getNote() from another function / an environment where it gets a values for cell assigned, the script will fail wiht the error you obtained.
的确，此脚本确实不能满足您的需求.您可能想要的是对所有带有注释的单元格进行筛选.
Indeed, this script doe snot not meet your needs. What you probably want is to screen all your cells for the one that have notes.
您需要确定要将注释和单元格标记放入哪些单元格中.
What you need to decide is into which cells you want to put the note and the cell notation.
以下是您需要适应您的需求的示例:
Below is a sample that you need to adapt for your needs:
function getNotes() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
//if you have only one sheet in the spreadsheet, otherwise use ss.getSheetByName(name);
var sheet = ss.getActiveSheet();
var range = sheet.getDataRange();
var results = range.getNotes();
for (var i = 0; i < results.length; i++) {
for (var j = 0; j < results[0].length; j++) {
//if a not empty note was found:
if(results[i][j]){
var note = results[i][j];
var cell = range.getCell(i+1, j+1);
var notation = cell.getA1Notation();
//adjust the offset as function of the column / row where you want to output the results
cell.offset(0, 1).setValue(note);
cell.offset(0, 2).setValue(notation);
}
}
}
}
重要参考文献:
这篇关于从一个单元格获取笔记并插入另一个单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
