Hyperlinks of multiple filtered views in google sheets using apps script part2

Question

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.

Answer 1

I believe your goal is as follows.

• There are one source sheet and 3 destination sheets in a Google Spreadsheet.
  • At "Sheet1", the values are put in the columns "C", "E" and "G" in your showing image.
• 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 the source sheet is corresponding to each destination sheet.

In this case, how about the following sample script?

Sample script:

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

function create_filter_view2() {
  // 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: [{ name: "Sheet2", range: "C4:C" }, { name: "Sheet3", range: "E4:E" }, { name: "Sheet4", range: "G4:G" }] };

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var src = ss.getSheetByName(obj.src);
  var values = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: obj.dst.map(({ range }) => `'${obj.src}'!${range}`) });
  obj.dst.forEach(({ name, range }, i) => {
    var dst = ss.getSheetByName(name);
    var dstId = dst.getSheetId();
    var requests = values.valueRanges[i].values.flatMap(([r]) => {
      if (r && r.toString() != "") {
        return { addFilterView: { filter: { title: r, range: { sheetId: dstId, startRowIndex: 0, startColumnIndex: 0 }, filterSpecs: [{ columnIndex: 1, filterCriteria: { condition: { type: "TEXT_EQ", values: [{ userEnteredValue: r }] } } }] } } };
      }
      return [];
    });
    var response = Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
    var filter_view_ids = response.replies.map(({ addFilterView: { filter: { filterViewId } } }) => filterViewId);
    var c = 0;
    var richTextValues = values.valueRanges[i].values.map(e => {
      if (e.toString() != "") {
        var temp = [SpreadsheetApp.newRichTextValue().setText(e[0]).setLinkUrl(`#gid=${dstId}&fvid=${filter_view_ids[c]}`).build()];
        c++;
        return temp;
      }
      return [SpreadsheetApp.newRichTextValue().setText("").build()];
    });
    src.getRange(range).offset(0, 0, richTextValues.length).setRichTextValues(richTextValues);
  });
}

• In this sample script, in order to retrieve the values from the scattered ranges, Sheets API is used.

Note:

• This sample script is for the sheets of your showing images. So when your actual situation is different from them, the script might not be able to be used. So please be careful about this.

References:

• Method: spreadsheets.values.batchGet
• Method: spreadsheets.batchUpdate
• AddFilterViewRequest
• FilterView
• setRichTextValues(values)