使用应用程序脚本在Google Sheet中过滤视图的超链接

2022-04-12 00:00:00 google-sheets-api google-sheets google-apps-script javascript

我想创建超链接(在第一个选项卡/工作表上有一个名称列表。每个名称在单击时应转到筛选器视图，记录在另一个工作表中)。示例：单击表1中的&John Smith&Quot；应该会将我带到筛选后的视图，其中包含表2中包含John Smith的所有行。

示例表工作表%1表有名称

Names
John Smith
Ryan Jones
Tony Welsh

表2包含销售信息

Sales_id    Name
1245    John Smith
1234    John Smith
1256    Tony Welsh
5674    Ryan Jones
2345    Tony Welsh
1557    John Smith
9830    Ryan Jones

我希望每个名称都有一个筛选的视图。因此，John Smith应该对表2(Sales：ID：1245、1234和1557)中的3条记录进行筛选。这些过滤后的视图将有URL，我会在工作表1中为每个人的姓名提供超链接

到目前为止，我的代码只为&John Smith&创建了一个新的筛选器视图我会

我想提供一个名称列表(不仅限于John Smith)来创建多个筛选器视图。
对于每个筛选器视图，我想要一个创建的链接(Fvid)，这样我就可以将其用作第一个选项卡上每个名称的超链接

请帮助，我不熟悉应用程序脚本：(

function filter_view (){
  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet();
 
  const sheetId = spreadsheetId.getActiveSheet().getSheetId();
  
  const addFilterViewRequest = [
    {
       'addFilterView' : {
         filter : {
           title : 'John Smith', // Title Of filter view & make sure no space in title
           range : {
             sheetId : sheetId, // The filter view sheetId
             'startRowIndex': 0,
             'startColumnIndex':0,
             
           }, // Using comma to seprate different conditional values
            'criteria': {
              1:{  // This number the column you are indexing in the spreadsheet 
               'condition': {
                 'type': "TEXT_EQ",
                 'values':[
                   {
                     "userEnteredValue" :'John Smith'                      
                     
                   },

                 ],
               }
               
             }
           
         }

       }
    }
    }
  ]
    


   Sheets.Spreadsheets.batchUpdate({ requests: addFilterViewRequest },spreadsheetId.getId());
   // update the spreadsheet using the addFilterViewRequest 
    
}

更新1：了解了如何传递数组以获得不同的筛选器视图。我可以在控制台中打印fvid。但我需要在表1中获取fvid(或完整的url或筛选器视图)。



function create_filter_view (){
  var list_names = ["John Smith","Ryan Jones","Tony Welsh"];//Add names you want views for
  for(var i = 0; i < list_names.length; i++){
          
      const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet();
    
      const sheetId = spreadsheetId.getActiveSheet().getSheetId();
      
      const addFilterViewRequest = [
        {
          'addFilterView' : {
            filter : {
              title : list_names[i], // Title Of filter view & make sure no space in title
              range : {
                sheetId : sheetId, // The filter view sheetId
                'startRowIndex': 0,
                'startColumnIndex':0,
                
              }, // Using comma to seprate different conditional values
                'criteria': {
                  1:{  // This number the column you are indexing in the spreadsheet 
                  'condition': {
                    'type': "TEXT_EQ",
                    'values':[
                      {
                        "userEnteredValue" :list_names[i]                      
                        
                      },

                    ],
                  }
                  
                }
              
            }

          }
        }
        }
      ]
        


      response = Sheets.Spreadsheets.batchUpdate({ requests: addFilterViewRequest },spreadsheetId.getId());
      // update the spreadsheet using the addFilterViewRequest 
      filter_view_id = response['replies'][0]['addFilterView']['filter']['filterViewId']
      console.log(filter_view_id);
      

      
}

    
}

解决方案

我相信您的目标如下。

您有两张第1页和第2页第2页。您的问题中显示了"；Sheet1"；和"；Sheet2"；的单元格。
您要使用"；Sheet1"；的&Quot；A"；列的值创建"；Sheet2"；列的筛选器视图。
列&A&Quot；的单元格具有筛选器视图的超链接。单击单元格时，您希望跳到与该单元格值对应的筛选器视图。

在这种情况下，下面的示例脚本如何？

修改点：

criteria的属性似乎已弃用。因此，在这种情况下，请使用filterSpecs[]。Ref
为了设置筛选器视图的超链接，使用了RichTextValue。

示例脚本：

在使用此脚本之前，please enable Sheets API at Advanced Google services。

function create_filter_view() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var sheet1 = ss.getSheetByName("Sheet1");
  var sheet2 = ss.getSheetByName("Sheet2");
  var sheetId2 = sheet2.getSheetId();
  var range1 = sheet1.getRange("A2:A" + sheet1.getLastRow());
  var values1 = range1.getValues();
  var requests = values1.map(([a]) => ({ addFilterView: { filter: { title: a, range: { sheetId: sheetId2, startRowIndex: 0, startColumnIndex: 0 }, filterSpecs: [{ columnIndex: 1, filterCriteria: { condition: { type: "TEXT_EQ", values: [{ userEnteredValue: a }] } } }] } } }));
  var response = Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
  var filter_view_ids = response.replies.map(({ addFilterView: { filter: { filterViewId } } }) => filterViewId);
  var richTextValues = filter_view_ids.map((e, i) => [SpreadsheetApp.newRichTextValue().setText(values1[i][0]).setLinkUrl(`#gid=${sheetId2}&fvid=${e}`).build()]);
  range1.setRichTextValues(richTextValues);
}

运行此脚本时，将从"；Sheet1"；的"；A"；列中检索值，并使用检索到的值为"；Sheet2"；创建筛选器视图。在本例中，将筛选第&b&q；列。然后，将筛选器视图的链接设置为第1页的第1列。

注意：

当上述脚本运行2次时，将创建相同的筛选器视图。因此，当您想要重置筛选器视图(删除所有筛选器视图)时，您还可以使用以下脚本。

  function deleteAllFinterViews() {
    var ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
    var ids = Sheets.Spreadsheets.get(ssId).sheets.flatMap(s => s.filterViews ? s.filterViews.map(e => ({ deleteFilterView: { filterId: e.filterViewId } })) : []);
    if (ids.length > 0) Sheets.Spreadsheets.batchUpdate({ requests: ids }, ssId);
  }

此示例脚本用于脚本中显示的示例电子表格。因此，当实际电子表格的结构与示例电子表格的结构不同时，可能无法使用该脚本。请注意此点。

引用：

Method: spreadsheets.batchUpdate
AddFilterViewRequest
FilterView
setRichTextValues(values)

相关文章