在GoogleSheetsAPI中，如何删除所有现有的FilterViews？

Question

我正在使用GoogleSheetsAPI，我想每月更新一次表单，其中包括删除所有过滤器视图，然后重新创建它们。我知道如何创建它们，但谷歌删除它们的示例代码要求您具有要删除的特定ID

我发现了另一个堆栈溢出问题（Mass Delete Filter Views in Google Sheets），但它不是Python，所以我很难理解如何将其转换为Python：

function delFilterViews() {
  var ssId = SpreadsheetApp.getActive().getId();
  Sheets.Spreadsheets.batchUpdate(
    {
      requests: Sheets.Spreadsheets.get(ssId, {
        ranges: 'Sheet1', //Sheet in which filterviews are present
        fields: 'sheets/filterViews/filterViewId',
      }).sheets[0].filterViews.map(function(e) {
        return { deleteFilterView: { filterId: e['filterViewId'] } }; //create a new delete filter view request for each filter view present in sheet1
      }),
    },
    ssId
  );
}

如有任何建议，将不胜感激

Answer 1

我相信你的目标如下

• 您希望将以下Google Apps脚本转换为python

    function delFilterViews() {
      var ssId = SpreadsheetApp.getActive().getId();
      Sheets.Spreadsheets.batchUpdate(
        {
          requests: Sheets.Spreadsheets.get(ssId, {
            ranges: 'Sheet1', //Sheet in which filterviews are present
            fields: 'sheets/filterViews/filterViewId',
          }).sheets[0].filterViews.map(function(e) {
            return { deleteFilterView: { filterId: e['filterViewId'] } }; //create a new delete filter view request for each filter view present in sheet1
          }),
        },
        ssId
      );
    }
  

• 您正在使用googleapis for python，并且已经完成了使用Sheets API的授权过程

在本例中，下面的示例脚本如何

示例脚本：

spreadsheetId = '###' # Please set the Spreadsheet ID.

service = build('sheets', 'v4', credentials=creds) # Please your "creds" for using Sheets API here.
res1 = service.spreadsheets().get(spreadsheetId=spreadsheetId, fields='sheets(filterViews)').execute()
deleteFilterViews = sum([[{'deleteFilterView': {'filterId': f['filterViewId']}} for f in e['filterViews']] for e in res1['sheets'] if 'filterViews' in e], [])
if deleteFilterViews != []:
    service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body={'requests': deleteFilterViews}).execute()

注：

• 运行上述脚本时，将删除所有过滤器视图。所以请小心这个。我建议使用示例电子表格和示例fiter视图来测试脚本

参考文献：

• Method: spreadsheets.batchUpdate
• DeleteFilterViewRequest