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

This is an extension of

CodePudding user response：

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.

Sample script:

Note:

References: