Home > Back-end >  Hyperlinks of multiple filtered views in google sheets using apps script
Hyperlinks of multiple filtered views in google sheets using apps script

Time:01-09

This is an extension of sheet1

Sheet2 Pic

Sheet3 Pic

Sheet4 Pic

Example Filter View for Vincent Lee

CodePudding user response:

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:

  •  Tags:  
  • Related