I hope you have a nice day! I have read a post regarding hyperlinks of filtered views from this post (Hyperlinks of filtered views in google sheets using apps script). In this post, the questioner gives the sample in the Sheet1 data as follows:
Names
1 John Smith
2 Ryan Jones
3 Tony Welsh
The total row here is only 3 rows. The solution from that post is perfect for this condition. But when I try to add another data to the Sheet1 until around 100 rows of data, the script becomes an Error.
This is the sample spreadsheet for my problem: sample spreadsheet.
In this spreadsheet, I have 2 sheets. Sheet1 and Sheet2. I want to make hyperlinks for all data in Sheet1 Col A so when I click one of them it will take me to the filter view in Sheet2 based on the value that I clicked.
This is my data:
Names
1 FGH-10
2 LZX-27
3 JKL-78
....
....
....
98 BZN-38
99 ERF-27
100 QYT-45
Error message: GoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: Invalid requests[26].addFilterView: This filter view name already exists, please try another. create_filter_view @ Code.gs:11
this is the code that I got from the link above:
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();
for(let i=1;i<=sheet1.getLastColumn();i++){
var range1 = sheet1.getRange(2,i,sheet1.getLastRow()-1,1);
var values1 = range1.getValues();
var requests = values1.map(([a]) => ({ addFilterView: { filter: { title: a, range: { sheetId: sheetId2, startRowIndex: 0, startColumnIndex: 0 }, filterSpecs: [{ columnIndex: 0, 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);
}
}
I have found that this code only works if we only have less than 25 rows of data in Sheet1. If we have more than 25 rows of data, the error will occur as stated above. So please if someone can help me to solve this problem.
thank's
When I saw your provided Spreadsheet, the value of FDB-73 existed 2 times. By this, such an error occurs. In this case, it is required to remove the duplicate value. So, how about the following modification?
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());
range1 = range1.removeDuplicates([1]);
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);
}