0

The following code, from its original state, would let me enter a text value in column4, and the code would convert the entry into a hyperlink from what was entered.

I tried to take it one step further, by adding the GID reference in column5, that would populate based on the value selected from a drop-down in column4.

The bit i'm having trouble with is [gid=&E&'+aRow+'",].

As an example, I am on row20. I have selected value in cell E20 from the drop-down, and the correct gid based on my choice is displayed in F20.

How can I reference F20 in the code, so that when clicking on the created hyperlink in cell E20, it uses the gid in F20 and takes you to the correct sheet?

I feel I am so close and have tried lots of different approaches. The nearest I have gotten is for it to display the hyperlink friendly name from cell E20 (which is great), and for it to display the gid as F20 (and not the 'actual' gid).

Remember that E20 and F20 are dynamic based on what row is being used.

 var ss = SpreadsheetApp.getActiveSpreadsheet();

    function onOpen() {
      var menu = [{name: "create URL", functionName: "createURL"}];
      ss.addMenu("URL", menu);
    }

    function onEdit(e) {
      var activeRange = e.source.getActiveRange();
      var aRow=activeRange.getRow();

        if(activeRange.getColumn() == 4) { 
        if(e.value != "") { 
          activeRange.setValue('=HYPERLINK("https://docs.google.com/spreadsheets/d/1jQnqJHqvC7QSxpfe2IOxHtmYOIiWy0yWM9MuTCuNqcw/edit#gid=&E&'+aRow+'","'+e.value+'")');
        }
      }
    }


    function createURL() {
      var aCell = ss.getActiveCell(), value = aCell.getValue();
      aCell.setValue('=HYPERLINK("https://docs.google.com/spreadsheets/d/1jQnqJHqvC7QSxpfe2IOxHtmYOIiWy0yWM9MuTCuNqcw/edit#gid=&E&'+aRow+'","'+value+'")');
      }
  • To make it clear. You enter stuff in col D, the code creates a hyperlink, then you select a value from a dropdown in col E. And you want, depending on different choices in col E, having clicked on the hyperlink in col D, to land to different targets, while not changing col D value? – ZygD Mar 27 '19 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.