谷歌表应用程序脚本onChange(事件)为INSERT_COLUMN:如何获得哪一列被插入?

Question

function onInsertColumn(activeRng) {  
/* I coded this function, and it works, so I'll omit 
the implementation here because it's irrelevant. 
But I haven't figured out how to get the active range
from the onChange event (to pass to this function).*/
}
function onChange(event) {
// https://stackoverflow.com/a/66686524/470749
// https://stackoverflow.com/a/64454240/470749
// https://developers.google.com/apps-script/guides/triggers/installable#google_apps_triggers
// https://developers.google.com/apps-script/guides/triggers/events

if(event.changeType == 'INSERT_COLUMN'){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet()
const activeRng = sheet.getSelection().getActiveRange(); // WHAT SHOULD GO HERE?
onInsertColumn(activeRng);
}
}

Answer 1

说明:

正如用户在评论中已经提到的，这取决于如何插入列。如果通过电子表格UI手动完成，那么您可以获得被选中的活动列的编号:

const colNumber = sheet.getSelection().getActiveRange().getColumn();

，然后创建一个表示完整列范围的range对象:

const activeRng = sheet.getRange(1,colNumber,sheet.getMaxRows(),1);

则可以将此范围提供给任何函数。

当然，除了getMaxRows()，您还可以使用getLastRow()来获取直到最后一行的内容，而不是整个列范围。

手动插入列时的解决方案:

function onInsertColumn(activeRng) {  
// example
activeRng.setValue("Selected");
}

function onChange(event) {
// https://stackoverflow.com/a/66686524/470749
// https://stackoverflow.com/a/64454240/470749
// https://developers.google.com/apps-script/guides/triggers/installable#google_apps_triggers
// https://developers.google.com/apps-script/guides/triggers/events

if(event.changeType == 'INSERT_COLUMN'){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
const colNumber = sheet.getSelection().getActiveRange().getColumn();
const activeRng = sheet.getRange(1,colNumber,sheet.getMaxRows(),1);
onInsertColumn(activeRng);
}
}