I would like to create hyperlinks(have a list of names on first tab/sheet. Each name when clicked should go to a filter view with records in another sheet). Example: Clicking on "John Smith" in sheet 1 should take me to a filtered view with all the rows that have John Smith in sheet 2.
Example tables Sheet 1 table has names
Names
John Smith
Ryan Jones
Tony Welsh
Sheet 2 has sales info
Sales_id Name
1245 John Smith
1234 John Smith
1256 Tony Welsh
5674 Ryan Jones
2345 Tony Welsh
1557 John Smith
9830 Ryan Jones
I want a filtered view for each name. So John Smith should have a filtered view of 3 records from sheet 2 ( sales:ids: 1245,1234 and 1557). These filtered views will have URLS that I would hyperlink in sheet 1 for each persons name
The code I have so far only creates a new Filterview for "John Smith" I would
- like to provide a list of names(not just limit to John Smith) to create multiple filter views.
- And for each filter view I want a the created link(fvid) so that I can use it as a hyperlink for each name on the first tab
Please help, I'm new to apps script :(
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
}
Update 1: Figured out how to pass an array to get different filter views. I am able to print the fvid in the console. But i need to get the fvid(or the full url or filter view) in sheet 1. Here's the updated code
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);
}
}
