更新Google表格“过滤器视图"带新范围 [英] Update Google Sheet "Filter View" w/ New Range

问题描述

我有一个电子表格，其中已设置了各种过滤器视图.通常，这很好用，但偶尔会添加新行，而我必须在每个过滤器视图中手动更新范围.我尝试过在线搜索解决方案，并想出了可能会更新范围的以下代码:

I have a spreadsheet with various filter views set in place. Normally this works great but occasionally new rows are added and I have to manually update the range in each filter view. I've tried searching for solutions online and came up with the following code that might update the range:

function UpdateFilterView() {
    var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
    //for each (var dataSheet in sheets){ 
      var lastRow = dataSheet.getLastRow();
      var lastColumn = dataSheet.getLastColumn();
      var sheetId = dataSheet.getSheetId();
      var filterSettings = {
        "filterViewId": "319575141",
        "range":{
        "sheetId": sheetId,
        "startRowIndex": 1,
        "endRowIndex": lastRow,
        "startColumnIndex": 1,
        "endColumnIndex": lastColumn
        }
       //}
      };


var requests = [{
  "fields": "*",
  "updateFilterView":{
  "filter": filterSettings
  }
 }];
 
  
 Sheets.Spreadsheets.batchUpdate({"requests":requests},sheetId);

 }

我从发现的帖子中获取了大部分代码(

I took most of the code from a post I found (here) but for some reason it won't update the specified filter. Am I missing something? Also, I get the following error when I run the code "ReferenceError: Sheets is not defined (line 32, file "Code")" of which is referencing the line "Sheets.Spreadsheets.....".

有什么想法甚至可能吗?

Any thoughts on if this is even possible?

推荐答案

从您的答复中，我可以确认，在您当前的情况下，高级Google服务已启用Sheets API.这样，我可以确认您的错误消息已更改.为了解决新问题，如何进行此修改?

From your replying, I could confirm that in your current situation, Sheets API was enabled at Advanced Google services. By this, I could confirm your error message was changed. In order to resolve the new issue, how about this modification?

• 请修改 Sheets.Spreadsheets.batchUpdate({'requests':requests}，sheetId); 的 Sheets.Spreadsheets.batchUpdate()的 sheetId .
• 请在 updateFilterView 的对象中包含字段":"*" .

• Please modify sheetId of Sheets.Spreadsheets.batchUpdate({"requests":requests},sheetId); to the spreadsheet ID.
• Please include "fields": "*" in the object of updateFilterView.

修改脚本后，请进行以下修改.

When your script is modified, please modify as follows.

function UpdateFilterView() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  // Added
  var dataSheet = ss.getSheetByName('Data');  // Modified
  var lastRow = dataSheet.getLastRow();
  var lastColumn = dataSheet.getLastColumn();
  var sheetId = dataSheet.getSheetId();
  var filterSettings = {
    "filterViewId": "319575141",
    "range":{
      "sheetId": sheetId,
      "startRowIndex": 1,
      "endRowIndex": lastRow,
      "startColumnIndex": 1,
      "endColumnIndex": lastColumn
    }
  };
  var requests = [{
    "updateFilterView":{
      "filter": filterSettings,
      "fields": "*",  // Modified
    }
  }];
  Sheets.Spreadsheets.batchUpdate({"requests":requests}, ss.getId());  // Modified
}

注意:

• 如果 319575141 的 filterViewId 不正确，则会出现类似'requests [0] .update_filter_view.filter.filter_view_id 处的无效值之类的错误..请注意这一点.

Note:

• When filterViewId of 319575141 is not correct, an error like Invalid value at 'requests[0].update_filter_view.filter.filter_view_id occurs. Please be careful this.
• 方法:sheetssheets.batchUpdate
• UpdateFilterViewRequest

这篇关于更新Google表格“过滤器视图"带新范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！