javascriptgoogle-apps-scriptgoogle-sheetsgoogle-apps-script-api

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

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.

Sheet1pic

Sheet2 Pic

Sheet3 Pic

Sheet4 Pic

Example Filter View for Vincent Lee

Solution

  • I believe your goal is as follows.

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

    Note:

    References: