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

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

问题描述

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

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

if (condition!=0 && hiddenValues.length>0){filterSettings['criteria'][COLUMNS.ASSIGNEE.pos] = {隐藏值":隐藏值，'条件':条件};}否则如果(hiddenValues.length> 0){filterSettings['criteria'][COLUMNS.ASSIGNEE.pos] = {隐藏值":隐藏值，};}否则如果(条件！= 0){filterSettings['criteria'][COLUMNS.ASSIGNEE.pos] = {'条件':条件};}//使用 BASIC FILTER 而不是 FILTER VIEW无功请求 = {设置基本过滤器":{过滤器":过滤器设置}}Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());

看过这个文档:-

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#FilterViewhttps://developers.google.com/sheets/api/reference/rest/v4/FilterCriteriahttps://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ConditionTypehttps://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ConditionValue

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

如果我查看请求 API，我可以看到有以下请求类型:-

  • addFilterView
  • 删除过滤器视图
  • updateFilterView
  • 重复过滤器视图

没有 SetFilterView.

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

解决方案

问题:

  • FilterView 无法从应用程序脚本设置

解决方法:

在此解决方法中，主要缺点是，您需要通过 url 打开 Google 表格，并且该 url 选项卡/窗口将在后台运行.您还可以使用简单的 =HYPERLINK() 公式轻松完成此操作，使用相同的逻辑，无需任何应用程序脚本.

  • FilterViews 将以下 url 哈希附加到工作表编辑 url:
    gid=SHEET_ID&fvid=FILTERVIEW_ID
  • 发布一个网络应用(HookService)，明确用于打开 Google 表格网址.
  • 使用网络应用程序，可以更改 Google 表格网址.
  • 可以使用过滤视图按钮在 Google 表格中打开边栏
  • Sidebar 然后将连接到网络应用程序(通过 window.postMessage)并命令它更改 Google 表格 url.这是必需的，因为侧边栏不能直接更改 Google 表格网址，但网络应用可以.

示例脚本:

WebApp:hookService.html

<html lang="en"><头><meta charset="UTF-8";/><title></title><身体><h1><span>开始</span>Google 工作表上的 HookService....</h1><div id="main"><p>这是设置过滤视图所必需的</p><p>请允许弹出窗口继续......</p><标签>您的 Google 表格 ID:<输入id=工作表"类型=文本"值=<?=id?>"自动完成/></label><br/><label>侧边栏原点:<input id="origin";类型=网址"；自动完成/></label><br/><button>开始挂机</button>

<脚本>var win1, win1url;让 [div，输入，原点，按钮] = [...document.querySelectorAll('div,input,button'),];origin.value = window.location.origin;函数 openSheets() {win1 = window.open((win1url ='https://docs.google.com/spreadsheets/d/' + input.value + '/edit'),'_空白的');如果(赢1){document.querySelector('span').textContent = '运行';window.addEventListener('message', receiveMessage, false);div.innerHTML = '关闭此窗口以停止 Hook 服务';} 别的 {alert('必须在此站点上禁用弹出窗口阻止程序才能运行钩子');}}openSheets();button.addEventListener('click', openSheets);函数接收消息(事件){//[安全] 检查事件来源if (event.origin !== origin.value) {console.error('不允许来源');alert('来源不允许');返回;}win1.location.replace(win1url + event.data);}

SideBar:hookSubService.html

<html lang="en"><头><meta charset="UTF-8";/><title></title><风格>div {显示:弹性；弹性方向:列；}</风格><身体><div><button id="1">过滤视图 1</button><button id="2">过滤视图 2</button>

<脚本>const win0 = window.top.opener;如果(！win0){alert('未找到挂钩服务！退出中...');google.script.host.close();}/************TODO: 设置工作表 ID 和过滤视图 ID************/const gids = ['Sheetid for filter view1', 'Sheetid for filterview2'];const fvids = ['filterviewid1', 'filterviewid2'];让 div = document.querySelector('div');div.addEventListener('点击', e => {让 id = Number(e.target.id) - 1;让 [gid, fvid] = [gids[id], fvids[id]];让消息 = '#gid=' + gid + '&fvid=' + fvid;//TODO:[SECURTY] 提供 webapp origin urlwin0.postMessage(message, window.location.origin);});

服务器端代码:code.gs

function doGet(e){var page=e.parameter.pagevar out = HtmlService.createTemplateFromFile(page || 'hookService');out.id = SpreadsheetApp.getActive().getId();return out.evaluate().addMetaTag('viewport', 'width=device-width, initial-scale=1');}函数 onOpen(e) {电子表格App.getUi().createMenu('Sidebar').addItem('Show FilterView', 'showSidebar').addToUi();}功能显示侧边栏(){SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile('hookSubService.html'))}

  • 在 HookSubService.html 中设置上面的 sheet Id 和 filterview id
  • 从脚本编辑器发布网络应用
  • 从发布的链接打开网络应用
  • Web 应用程序必须绑定到电子表格的脚本编辑器

参考:

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.

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

Have looked at this doc:-

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.

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

  • addFilterView
  • deleteFilterView
  • updateFilterView
  • duplicateFilterView

There is no SetFilterView.

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

解决方案

Issue:

  • FilterView could not be set from apps script

Workaround:

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 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.

Sample script:

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>

Server side code: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')
  )
}

  • 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

References:

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

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