0

I have Sheet1 with existing filter view hyperlinks. I want to find any names that dont have filterviews, then create filterviews with hyperlinks to these names and add them in alphabetical order to the list that already has hyperlinks.

Example: In Sheet 1, Col C,E,G, new names: Tracy Jack,Maria Jose , Samuel Philips,Karan Tucker, Vincent Lee, Wes Lee do not have a filter view and hyperlink, I want to add filter view and hyperlink to these names and then add them alphabetically to the list of names with hyperlinks in the rows above

Before adding hyperlinks

After adding hyper link to

Tracy Jack (Col C),

Maria Jose , Samuel Philips (Col E),

Karan Tucker, Vincent Lee, Wes Lee(Col G),

I want to add insert these names with links in alphabetical order in the rows above. Please see pic below for final output

After adding and rearranging list

user17243359
  • 55
  • 6
  • So, do you need a script to perform a sort operation? If you mean to select every column and run the script you don't even need a script. You can just sort them with about the same number of clicks. If you want to selects the columns automatically, I see no way (from the sample and description) how the script could tell which of the columns need to sort. – Yuri Khristich Jan 09 '22 at 09:50
  • In your situation, the start row of the data is always row 5? In order to add the filter view, can I ask you about the relationship between the columns "C", "E" and "G" and each sheet in Google Spreadsheet? – Tanaike Jan 09 '22 at 12:45
  • @Tanaike: Sorry this question is badly written. I reposted the question here: https://stackoverflow.com/questions/70641669/create-a-list-of-names-and-counts-with-hyperlinks-to-filter-views-in-google-shee – user17243359 Jan 09 '22 at 13:21
  • @Tanaike: This post is a valid use case as well, so if you have time you can look at it. The names always start from row 5. Column C has hyperlinks to Sheet2, Column E has hyperlinks to Sheet3 and Column G has hyperlinks to Sheet 4. – user17243359 Jan 09 '22 at 13:48

1 Answers1

0

Here is the simply script that adds the custom menu 'Scripts' and add two commands: 'Sort column' and 'Sort columns C, E, G':

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Scripts')
  .addItem('Sort current column', 'sort_active_col')
  .addItem('Sort columns C, E, G', 'sort_cols_CEG')
  .addToUi();
}

function sort_active_col() {
  var letter = SpreadsheetApp.getActiveRange().getA1Notation().replace(/^([A-Z]+).+/,'$1');
  sort_col(letter);
}

function sort_cols_CEG() {
  ['C', 'E', 'G'].forEach(letter => sort_col(letter));
}

function sort_col(letter) {
  var range = SpreadsheetApp.getActiveSheet().getRange(`${letter}5:${letter}`);
  range.sort(range.getColumn());
}

They sort a current column of columns 'C', 'E', 'G' respectively (start from row 5).

Yuri Khristich
  • 13,448
  • 2
  • 8
  • 23
  • I want the hyperlink to be cleared, not the format. Right now the background color of the cell becomes white. How can I just clear hyperlink without changing cell color? – user17243359 Jan 09 '22 at 12:57
  • The code doesn't clear anything. It just sorted the ranges. Are you sure that your comment belongs this answer? – Yuri Khristich Jan 09 '22 at 13:12
  • To keep and restore background colors you can use `var bg = range.getBackgrounds()` and `range.setBackgrounds(bg)` respectively. – Yuri Khristich Jan 09 '22 at 13:14
  • 1
    Sorry youre right. I posted it incorrectly here. This post was worded poorly. I reposted the question here: https://stackoverflow.com/questions/70641669/create-a-list-of-names-and-counts-with-hyperlinks-to-filter-views-in-google-shee – user17243359 Jan 09 '22 at 13:23