INSERT_COLUMN 的 Google Sheets 应用程序脚本 onChange(event)：如何获取插入的列？ [复制]

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);
  }
}

解释：

正如用户在评论中已经提到的，这取决于列的插入方式。如果这是通过电子表格 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);
  }
}