0

This is an extension of question:

I have sheet a sheet of names(sheet 1 in Columns A, B and C). I want Have sales information of people in Sheet 2 , sheet 3 and Sheet 4.

I want an apps script for filter view hyperlinks on sheet 1. So Column A on Sheet 1 should take you to a filtered view on Sheet 2. Sheet 1 column B names will have hyperlinks of filter views in Sheet 3. Sheet 1 column C names will have hyperlinks of filter views in Sheet 4.

The code I have so far only takes names from one column in sheet 1 and gets hyperlinks from sheet 2. How do I cycle through Columns A, B and C in Sheet 1 and Sheets 2,3,4. Heres what I have so far ?

If possible, please provide code to even delete filter views, using the same method(I mean based on column names you select, delete specific filter views). When I delete filter views, I want to clear the hyperlinks on the Sheet1 as well(since these links will not exist any more)

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

Pics of example sheets are below:

Sheet1 Has 3 columns: A, B, C. I want the hyperlinks on sheet1 Column A to come from sheet 2. hyperlinks on sheet1 Column B should come from sheet 3. hyperlinks on sheet1 Column C should come from Sheet 4. I attached an example pic of the filter view in the last pic. "Vincent Lee" from Column C on Sheet 1 should have hyperlink of all "Vincent Lee " records from Sheet4.

sheet1

Sheet2 Pic

Sheet3 Pic

Sheet4 Pic

Example Filter View for Vincent Lee

user17243359
  • 55
  • 6
  • Unfortunately, I cannot correctly understand your situation and your goal from your question. This is due to my poor English skill. I deeply apologize for this. In order to correctly understand your current situation and your goal, can you provide the sample input and output situations as the images? By this, I would like to try to correctly understand your question. – Tanaike Jan 08 '22 at 23:56
  • @Tanaike Please see updated pics and comments. thank you for your patience – user17243359 Jan 09 '22 at 00:55
  • I could understand 1st question. But, I cannot understand `Additionally if possible, please add a function to delete the filter views and hyperlinks of Columns A, B and C from Sheet1. I want the option to delete filter views and Hyperlinks of names in each column. Instead of deleting all filterviews I mean. Like input will be names from each column and output will be deleted hyperlinks and filterviews for those names(This way I will have the option for deleting just Column A and B's views and hyperlinks, instead of deleting ALL).`. Can I ask you about the detail of your additional questions? – Tanaike Jan 09 '22 at 03:53
  • @Tanaike function deleteAllFinterViews() .... This function deletes all the filter views in the spreadsheet. but it doesnt remove the hyperlinks from sheet 1. Also I want to delete filter views for some names only. Not all. So I want to be able to delete filter views and hyperlinks for Sheet 1 Column A and Column B names. I dont want to delete Column C filterviews and hyperlinks. Does this make sense? – user17243359 Jan 09 '22 at 04:01
  • @Tanaike The delete All function you wrote previously will delete ALL filterviews (I want it to be able to delete based on whatever column I give as input. If i give A, B, C columns as input , it will remove all filter views, but I want the option to just delete names from certain columns only.) – user17243359 Jan 09 '22 at 04:01
  • Thank you for replying. I cannot still imagine `(I want it to be able to delete based on whatever column I give as input. If i give A, B, C columns as input , it will remove all filter views, but I want the option to just delete names from certain columns only.)`. I apologize for my poor English skill. – Tanaike Jan 09 '22 at 04:08
  • @Tanaike I want Function deleteViews for a given range of input names. The deleteALL function will delete ALL views in the entire spreadsheet. Imagine if I have other hyperlinks in additional tabs, I need to still keep those. makes sense? – user17243359 Jan 09 '22 at 04:23
  • In your goal, do you want to run the function? I cannot understand this. So I cannot imagine your additional question. I apologize for this. – Tanaike Jan 09 '22 at 04:45
  • I want 2 functions. 1.)One to add filter views and hyperlinks. 2.)Another delete filter views and hyper links – user17243359 Jan 09 '22 at 04:52
  • If the delete section(additional section) is too hard, its ok. its not as important – user17243359 Jan 09 '22 at 04:53
  • @Tanaike: yes this will work too... Delete filterviews by sheet name. But If I want to delete views of only certain users in Column A, I cannot change the range right? It will delete ALL users from a sheet? Like I cant delete only a subset users filters. Ideal case is to be able to delete by user list, but by sheet name also works great too – user17243359 Jan 09 '22 at 05:09
  • @Tanaike if i have additional filters like of all sales in a region(not by person) in the same sheet, I will lose those filters too if I delete views by Sheetname – user17243359 Jan 09 '22 at 05:10
  • Thank you for replying. From your replying, I would like to propose you post your 2nd question as a new question. Because in this case, when the scripts of your 1st question and 2nd question are answered, the users might confuse your questions. And I think that your both questions will be also useful for other users. How about this? – Tanaike Jan 09 '22 at 05:14
  • @Tanaike. Done. i edited this post. I created a new post for the delete section: https://stackoverflow.com/questions/70638649/delete-filtered-views-and-hyperlinks-from-google-sheets – user17243359 Jan 09 '22 at 05:20
  • Thank you for your response. About this question, I answered. Could you please confirm it? – Tanaike Jan 09 '22 at 05:20
  • @Tanaike: So sorry for the confusion , but sheet 1 has additional rows. I have edited sheet 1 pic to show proper format. Can you please edit code to correctly put the values for the names. I tried to edit this line " var values = src.getRange(2, 1, src.getLastRow(), src.getLastColumn()).getValues();" but i am getting wrong results. I changed the code to " var values = src.getRange(5, 1, src.getLastRow(), src.getLastColumn()).getValues();" but all the rows are getting shifted and the hyperlinks are on the wrong names – user17243359 Jan 09 '22 at 05:56
  • Thank you for replying. When I saw your updated question, I noticed that you had changed your sample image from https://i.stack.imgur.com/MyYOC.png to https://i.stack.imgur.com/QgVwk.png . I think that your current issue is due to this change. Because my proposed answer is for your sample image of https://i.stack.imgur.com/MyYOC.png . So please change your sample to the 1st one, and test it again. I had believed your sample images. So I prepared the sample script for using your provided sample images. I deeply apologize for this. – Tanaike Jan 09 '22 at 05:59
  • I think that at your changed question, it is required to largely change my proposed script. Because I prepared the sample script by using the sample images you firstly provided. So, first, please test my proposed script using your initial question. And, if you want to change your question, please post it as a new question. The reason that my proposed script cannot be for various Spreadsheets is due to my poor skill. About this, I deeply apologize for this. I would be grateful if you can forgive my poor skill. – Tanaike Jan 09 '22 at 06:18
  • @Tanaike: So sorry tanaike, i did not realize that adding a few extra lines will need the script to change, I thought I would be able to modify it easily. Again I am very sorry. I will post the same question again with the updated sheet1 – user17243359 Jan 09 '22 at 06:32
  • Thank you for replying. About `I will post the same question again with the updated sheet1`, before you post a new question, please test this question. Because when my proposed answer was not useful for your initial question, I have to apologize. – Tanaike Jan 09 '22 at 06:33
  • @Tanaike: I just finished testing and it works perfectly for my files i input. Please see new question(updated sheet1): https://stackoverflow.com/questions/70639055/hyperlinks-of-multiple-filtered-views-in-google-sheets-using-apps-script-part2 – user17243359 Jan 09 '22 at 06:50
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/240882/discussion-between-user17243359-and-tanaike). – user17243359 Jan 09 '22 at 06:56
  • @Tanaike: please see this question as well: https://stackoverflow.com/questions/70639705/add-filter-view-hyperlinks-and-rearrange-the-column-values – user17243359 Jan 09 '22 at 08:47

1 Answers1

3

I believe your goal is as follows.

  • There are one source sheet and 3 destination sheets in a Google Spreadsheet.
  • You want to retrieve the values from the source sheet and create the filter views to the destination sheets using the values retrieved from the source sheet.
  • Each column of source sheet is corresponding to each destination sheet.

In this case, how about the following sample script? In this script, I used the sample script in your previous question.

Sample script:

Before you use this script, please enable Sheets API at Advanced Google services.

function create_filter_view() {
  // Please set the object for putting the filter views to the destination sheet using the values from the source sheet.
  var obj = { src: "Sheet1", dst: [{ col: 1, name: "Sheet2" }, { col: 2, name: "Sheet3" }, { col: 3, name: "Sheet4" }] };

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var src = ss.getSheetByName(obj.src);
  var values = src.getRange(2, 1, src.getLastRow(), src.getLastColumn()).getValues();
  obj.dst.forEach(({ col, name }) => {
    var dst = ss.getSheetByName(name);
    var dstId = dst.getSheetId();
    var requests = values.flatMap(r => {
      var temp = r[col - 1];
      if (temp.toString() != "") {
        return { addFilterView: { filter: { title: temp, range: { sheetId: dstId, startRowIndex: 0, startColumnIndex: 0 }, filterSpecs: [{ columnIndex: 1, filterCriteria: { condition: { type: "TEXT_EQ", values: [{ userEnteredValue: temp }] } } }] } } };
      }
      return [];
    });
    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(values[i][col - 1]).setLinkUrl(`#gid=${dstId}&fvid=${e}`).build()]);
    src.getRange(2, col, richTextValues.length).setRichTextValues(richTextValues);
  });
}

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165