2

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

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

Nico
  • 23
  • 4
  • 1
    First, I apologize that my answer was not useful for your situation. And also, I have to apologize for my poor English skill. Unfortunately, I cannot understand what you want to do. In order to correctly understand your question, can you provide the sample input and output situations you expect? Or, can you provide the sample Spreadsheet including the sample input and output situations? First, I would like to correctly understand your question. – Tanaike Aug 23 '23 at 07:39
  • What do you mean by "enter image description here"? Please read how to ask on StackOverflow: https://stackoverflow.com/help/how-to-ask – Ingo Steinke Aug 23 '23 at 08:30
  • 1
    Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Aug 23 '23 at 08:30
  • 1
    @Tanaike Thank you for your response, I have edited my question, I hope it can make it clearer than before so you can understand what I mean – Nico Aug 23 '23 at 09:35
  • 1
    @IngoSteinke Thank you for your suggestion. I am new here, just registered yesterday. I will try to make the question more detailed and specific. I have edited my question. I hope you can understand it. – Nico Aug 23 '23 at 09:37
  • Thank you for replying. I would like to support you. But, I have to apologize for my poor English skills, again. Unfortunately, from your reply, I cannot still understand your question. Where can I see the sample input and output situations you expect in your question? And, in order to correctly understand your current issue, can you provide a sample Spreadsheet for correctly replicating your current issue? – Tanaike Aug 24 '23 at 01:20
  • @Tanaike Thank you for your response. I have put a spreadsheet link for my problem. I also have revised and given an additional explanation in my post. I hope you can understand my problem. – Nico Aug 24 '23 at 07:04
  • Thank you for replying. From your reply and provided sample Spreadsheet, I proposed a modified script as an answer. Please confirm it. – Tanaike Aug 24 '23 at 07:40
  • @Tanaike thank you for your answer. It is clear now. – Nico Aug 24 '23 at 08:12
  • Thank you for replying and testing it. I'm glad your issue was resolved. I could correctly understand your current issue with your cooperation. Thank you, too. – Tanaike Aug 24 '23 at 11:41

1 Answers1

1

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?

Modified script:

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

Note:

  • This modified script is for your provided Spreadsheet. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165