如何通过Google Apps脚本应用GoogleSheet Filterview? [英] How to apply a GoogleSheet Filterview via Google Apps Script?

查看：107
本文介绍了如何通过Google Apps脚本应用GoogleSheet Filterview?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我已经创建了一些Google Apps脚本代码，该代码创建了GSheets FilterViews.我看不到如何在当前工作表上应用特定的FilterViews(不是Filters).

I have created some Google Apps Script code which creates GSheets FilterViews. I cannot see how to apply a specific FilterViews (not Filters) to the current sheet.

我已经设置了FilterSettings的代码，然后将其应用于基本过滤器，但是我在文档中看不到如何对filterview应用类似的方法.

The code i have sets FilterSettings and then applies it to a basic filter but i cannot see in the documentation how to apply a similar approach to filterview.

if (condition!=0 && hiddenValues.length>0)
  {
    filterSettings['criteria'][COLUMNS.ASSIGNEE.pos] = {
      'hiddenValues': hiddenValues,
      'condition': condition
    };    
  }
  else if(hiddenValues.length>0)
  {
    filterSettings['criteria'][COLUMNS.ASSIGNEE.pos] = {
      'hiddenValues': hiddenValues,
    };    
  }
  else if(condition!=0)
  {
    filterSettings['criteria'][COLUMNS.ASSIGNEE.pos] = {
      'condition': condition
    };    
  }

  // Using BASIC FILTER not FILTER VIEW   
  var request = {
    "setBasicFilter": {
      "filter": filterSettings
    }
  }

Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());

已查看此文档:-

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#FilterView https://developers.google.com/sheets/api/reference/rest/v4/FilterCriteria https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ConditionType https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ConditionValue

我可以看到它在历史上不受支持，但是我想知道是否仍然如此.

I can see that historically its not been supported but I wonder if that is still the case.

我可以看看是否查看请求API的请求类型:-

I can see if i look at the requests API there are request types for:-

  • addFilterView
  • deleteFilterView
  • updateFilterView
  • duplicateFilterView

没有SetFilterView.

There is no SetFilterView.

通过GAS激活过滤器视图的正确方法是什么.

What is the right way to activate a filter view via GAS.

谢谢

推荐答案

问题:

    无法通过应用脚本设置
  • FilterView

    • Issue:

    • FilterView could not be set from apps script

      • 在此替代方法中，主要缺点是，您需要通过网址打开Goog​​le表格，而网址标签/窗口将在后台运行.您也可以使用相同的逻辑，使用简单的=HYPERLINK()公式轻松完成此操作，而无需任何应用程序脚本.

      In this workaround, The main con is that, You'd need to open the Google sheets through a url and that url tab/window will run in the background. You can also easily do this with plain =HYPERLINK() formulas using the same logic without any apps script.

      • FilterViews在工作表编辑url后面附加了以下url哈希:
        gid=SHEET_ID&fvid=FILTERVIEW_ID
      • 发布Web应用程序(HookService)的明确目的是打开Goog​​le表格的网址.
      • 使用网络应用程序，可以更改Google表格的网址.
      • 可以使用filterview按钮在Google表格中打开侧边栏
        • 然后，
      • 侧边栏将连接到Web应用程序(通过window.postMessage)并命令它更改Google表格的网址.这是必需的，因为侧边栏不能直接更改Google表格的网址，而Web应用程序可以.
      • FilterViews has the following url hash appended to sheets edit url:
        gid=SHEET_ID&fvid=FILTERVIEW_ID
      • Publish a web app(HookService) for the explicit purpose of opening Google sheets url.
      • Using the web-app, It is possible to change Google sheets url.
      • Sidebar can be opened in Google sheets with filterview buttons
      • Sidebar will then connect to the web-app(through window.postMessage) and command it to change Google sheets url. This is needed because sidebar cannot directly change the Google sheets url, but a web-app can.

      WebApp : hookService.html 

      <!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <title></title>
  </head>
  <body>
    <h1><span>Starting</span> HookService on Google sheets....</h1>
    <div id="main">
      <p>This is needed to set filter views</p>
      <p>Kindly allow pop ups to continue....</p>
      <label
        >Your Google sheets ID:<input
          id="sheets"
          type="text"
          value="<?=id?>"
          autocomplete/></label
      ><br />
      <label>Sidebar Origin:<input id="origin" type="url" autocomplete/></label
      ><br />
      <button>START HOOK</button>
    </div>
    <script>
      var win1, win1url;
      let [div, input, origin, button] = [
        ...document.querySelectorAll('div,input,button'),
      ];
      origin.value = window.location.origin;
      function openSheets() {
        win1 = window.open(
          (win1url =
            'https://docs.google.com/spreadsheets/d/' + input.value + '/edit'),
          '_blank'
        );
        if (win1) {
          document.querySelector('span').textContent = 'Running';
          window.addEventListener('message', receiveMessage, false);
          div.innerHTML = 'Close this window to stop Hook Service';
        } else {
          alert('Pop up blockers must be disabled on this site to run hook');
        }
      }
      openSheets();
      button.addEventListener('click', openSheets);
      function receiveMessage(event) {
        //[SECURITY] Check event origin
        if (event.origin !== origin.value) {
          console.error('origin disallowed');
          alert('Origin not allowed');
          return;
        }
        win1.location.replace(win1url + event.data);
      }
    </script>
  </body>
</html>

      SideBar : hookSubService.html 

      <!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <title></title>
    <style>
      div {
        display: flex;
        flex-direction: column;
      }
    </style>
  </head>
  <body>
    <div>
      <button id="1">FILTER VIEW 1</button>
      <button id="2">FILTER VIEW 2</button>
    </div>
    <script>
      const win0 = window.top.opener;
      if (!win0) {
        alert('Hook service not found! Exiting...');
        google.script.host.close();
      }
      /************TODO: Set sheet ids and filterview ids************/
      const gids = ['Sheetid for filter view1', 'Sheetid for filterview2'];
      const fvids = ['filterviewid1', 'filterviewid2'];

      let div = document.querySelector('div');
      div.addEventListener('click', e => {
        let id = Number(e.target.id) - 1;
        let [gid, fvid] = [gids[id], fvids[id]];
        let message = '#gid=' + gid + '&fvid=' + fvid;

        //TODO:[SECURTY] Provide webapp origin url instead
        win0.postMessage(message, window.location.origin);
      });
    </script>
  </body>
</html>

      服务器端代码: code.gs 

      function doGet(e){
var page=e.parameter.page
var out = HtmlService.createTemplateFromFile(page || 'hookService');
  out.id = SpreadsheetApp.getActive().getId();
  return out.evaluate().addMetaTag('viewport', 'width=device-width, initial-scale=1');
}
function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Sidebar').addItem('Show FilterView', 'showSidebar').addToUi();
}

function showSidebar() {
  SpreadsheetApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile('hookSubService.html')
  )
}

      • 在上方的HookSubService.html中设置工作表ID和filterview ID
      • 通过脚本编辑器发布网络应用
      • 通过已发布的链接打开网络应用程序
      • Web应用程序必须绑定到电子表格的脚本编辑器

        • Set sheet Ids and filterview ids above in HookSubService.html
        • Publish the web-app from script editor
        • Open the web app from the published link
        • Web app must be bound to the Spreadsheet's script editor

          • Window#postMessage
          • Same origin policy

          这篇关于如何通过Google Apps脚本应用GoogleSheet Filterview?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆