将行添加到过滤范围

问题描述

我在Google表格中有一个待办事项列表应用程序.我具有按注释类型"和完成状态"过滤的功能，这些功能可以在用户给定的任何时刻使用.

I have a to-do list app in google sheets. I have functions for filtering by "note type" and "done status" that can be in use at any given moment by the user.

我还具有轻松添加任何给定类型的新音符的功能.但是，当运行该函数添加新笔记时，并且工作表已被过滤时，出现以下错误:

I also have functions to easily add a new note of any given type. However, when running the function to add a new note, and the sheet is already filtered, I'm getting the following error:

具有过滤出行的范围不支持此操作."

"This operation is not supported on a range with a filtered out row."

关于如何将行添加到过滤范围的任何建议?

Any advice on how I can add a row to a filtered range?

这是我用来添加特定类型新音符的代码:

Here is the code that I am using to add a new note of a particular type:

function addNewCueNote() {
  if( sheet.getSheetName() == sheetName ) {
    var noteType = "CUE"

   //ADDS ROW AND COPIES FORMULA DOWN
  //SETS VARIABLES FOR LAST ROW AND LAST COLUMN
  var lRow = sheet.getLastRow();
  var lCol = sheet.getLastColumn();
  //INSERT LAST ROW
  sheet.insertRowsAfter(lRow, 1);
  //COPY FORMULAS DOWN FOR SPECIFIED COLUMNS
  sheet.getRange(lRow,firstCopyCol,1,numColCopy).copyTo(sheet.getRange(lRow+1,firstCopyCol,1,numColCopy));

    //SETS NOTE TYPE
    sheet.getRange(sheet.getLastRow(),noteTypeCol).setValue(noteType);
}

推荐答案

@Nick您的代码逻辑有问题.无论如何，这都是有效的代码

@Nick there is something wrong with your code logic. In any ways this is a working code

// *** I have to add this for tests ***
var firstCopyCol = 3;
var numColCopy = 2;
var noteTypeCol = 2;
var sheet = SpreadsheetApp.getActiveSheet();
var sheetName = 'MatchImport';

// ************************************

function addNewCueNote() {
  if (sheet.getSheetName() === sheetName) {
    var filter = sheet.getFilter();
    if (filter) {
      var dataRange = sheet.getDataRange();
      var oldNumColumns = filter.getRange().getNumColumns();
      var newNumColumns = dataRange.getNumColumns();
      var criterias = {};
      for (var c = 1; c <= oldNumColumns && c <= newNumColumns; c++) {
        var criteria = filter.getColumnFilterCriteria(c);
        if (criteria) criterias['_' + c] = criteria;
      }
      filter.remove();
    }

    // START OF YOUR INSERT LOGIC
    var noteType = 'CUE';

    // ADDS ROW AND COPIES FORMULA DOWN
    // SETS VARIABLES FOR LAST ROW AND LAST COLUMN
    var lRow = sheet.getLastRow();
    var lCol = sheet.getLastColumn(); // This is never used
    // INSERT LAST ROW
    sheet.insertRowsAfter(lRow, 1);

    // COPY FORMULAS DOWN FOR SPECIFIED COLUMNS

    sheet
      .getRange(lRow, firstCopyCol, 1, numColCopy)
      .copyTo(sheet.getRange(lRow + 1, firstCopyCol, 1, numColCopy));

    // SETS NOTE TYPE
    sheet.getRange(sheet.getLastRow(), noteTypeCol).setValue(noteType);

    //* * END OF YOUR INSERT LOGIC

    if (!filter) return;
    dataRange = sheet.getDataRange();
    var newFilter = dataRange.createFilter();
    newNumColumns = dataRange.getNumColumns();
    for (c = 1; c <= oldNumColumns && c <= newNumColumns; c++) {
      if (criterias['_' + c])
        newFilter.setColumnFilterCriteria(c, criterias['_' + c]);
    }
  }
}

这篇关于将行添加到过滤范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！