Groups keyboard shortcuts have been updated
Dismiss
See shortcuts

Hyperlink script where it dynamically references cells

152 views
Skip to first unread message

Stuart Eade

unread,
Mar 25, 2019, 3:43:41 PM3/25/19
to Google Apps Script Community
Good evening,

I am using the following code:
   
    // global
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();

  if(activeRange.getColumn() == 4) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("https://docs.google.com/spreadsheets/d/spreadsheetID/'+$E2+'","'+$D2+'")');
    }
  }
}

function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("https://docs.google.com/spreadsheets/d/spreadsheetID/'+$E2+'","'+$D2+'")');  
}


The code generates a hyperlink on the value entered in column D.
Column D is selected from a drop-down list, with the worksheet already created, so there is no chance of any errors.
This also means that the gid is already known for each item in the drop-down list and a query displays it in column E.
So the code should look up the values from columns D & E and insert it into the code above.

Firstly, +$E2+ and +$D2+ cannot be the correct way of doing it. I am not sure how to reference the dynamic cell references.
Which leads me on to the second point - how can i make E2 and D2 dynamic, so for example the user may be on row 28, select a value from the drop-down in D28, in which case E2 and D2 would need to be E28 and D28.

Any help, greatly appreciated

Best regards
manc


Frerichs, Chad

unread,
Mar 25, 2019, 4:05:27 PM3/25/19
to google-apps-sc...@googlegroups.com
You would use & instead of + t o concatenate the strings to produce the URL as long as there are no special characters that would otherwise break it.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4d371a8e-2d63-4a5a-ba77-4aea8db92cab%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


--
Chad Frerichs
Director of Technology
ADM School District

Stuart Eade

unread,
Mar 25, 2019, 4:08:14 PM3/25/19
to google-apps-sc...@googlegroups.com
Thanks Chad, but any idea how to reference the different rows? It’s not always D2 and E2.

Best regards
manc

Frerichs, Chad

unread,
Mar 25, 2019, 4:18:46 PM3/25/19
to google-apps-sc...@googlegroups.com
Sorry, I was attempting to multitask and failed.

You would need an additional variable that grabs the current row like:

aRow=aCell.getRow;

And then concatenate that into your URL string.

I haven't put this in an editor but something like:

function onEdit(e) {
  var activeRange = e.source.getActiveRange();
  var aRow=aCell.getRow;
  if(activeRange.getColumn() == 4) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("https://docs.google.com/spreadsheets/d/spreadsheetID/"&$E'.aRow.'&"/edit#gid="&$D'.aRow.'")');
    }
  }
}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKCFMbr1ampPgbuk-L4pMMR7B4tWLh3oHnxvN0G_55PuG8-JuA%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

Frerichs, Chad

unread,
Mar 25, 2019, 4:43:49 PM3/25/19
to google-apps-sc...@googlegroups.com
Looking at this a little closer, it looks like you are trying to dump the hyperlink back into the cell you just changed? That will cause an error. The script below does what you want but dumps the hyperlink into column 6.

function onEdit(e) {
  var activeRange = e.source.getActiveRange();
  var aRow=activeRange.getRow();
  var dumpCell = e.source.getActiveSheet().getRange(aRow,6);
  if(activeRange.getColumn() == 3) { 
    if(e.value != "") { 
      dumpCell.setValue('=HYPERLINK("https://docs.google.com/spreadsheets/d/spreadsheetID/"&$E'+ aRow + '&"/edit#gid="&$D'+ aRow +')');
    }
  }
}

Stuart Eade

unread,
Mar 25, 2019, 4:51:03 PM3/25/19
to Google Apps Script Community
Thanks for your replies Chad, really is appreciated.

The original code i had is from here:

This code DID dump the code back into the cell i just changed, just the gid was invalid of course. FIne if you are using the code to jump to websites i guess, but not for google sheets. Thought i could adapt it a little by substituting the +e.value+ to a specific gid and friendly text.

I'm about to give your code a try. 

Many thanks
manc
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.


--
Chad Frerichs
Director of Technology
ADM School District

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.


--
Chad Frerichs
Director of Technology
ADM School District

Stuart Eade

unread,
Mar 25, 2019, 5:28:12 PM3/25/19
to Google Apps Script Community
So i modified your code slightly Chad;

function onEdit(e) {
  var activeRange = e.source.getActiveRange();
  var aRow=activeRange.getRow();
  var dumpCell = e.source.getActiveSheet().getRange(aRow,6);
  if(activeRange.getColumn() == 4) { 
    if(e.value != "") { 
      dumpCell.setValue('=HYPERLINK("https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid="&$E'+ aRow + ')');
    }
  }
}

Whilst it works, it is not what i'm trying to achieve and can pretty much do the same without using the script editor.
It is specifically replacing the text just entered with a hyperlink is what i'm trying to achieve.

Thanks very much for your efforts though.

Best regards
manc

Stuart Eade

unread,
Mar 25, 2019, 6:21:07 PM3/25/19
to Google Apps Script Community
Guys, I am so close I can feel it.

The highlighted text is not working correctly - it won't take the gid from column E into the variable "aRow"

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/spreadsheetID/edit#gid=&E'+aRow+'","'+e.value+'")');
    }
  }
}


function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=&E'+aRow+'","'+value+'")');
  }

Please help!

Best regards
manc

On Monday, 25 March 2019 20:51:03 UTC, Stuart Eade wrote:

Clark Lind

unread,
Mar 26, 2019, 8:18:45 AM3/26/19
to Google Apps Script Community
You are very close. Firstly, I am assuming you have added "spreadsheetID" in the URL to mask the real spreadsheetID, otherwise you will obviously need that.

If you right-click on any cell, you can select: "Get link to this cell" and it will show you the correct format. The GID is the ID of the tab itself. Then just add: &range=A1_cell_notation

I normally hard-code the GID and ssID. So I would write it like this:
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssID = ss.getId();//get the actual spreadsheetID
var GID = 'YOUR_GID#';//insert the actual GID of the tab

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/' + ssID + '/edit#gid=' + GID + '&range=E'+aRow+'","'+e.value+'")');
}
}
}


function createURL() {
var aCell = ss.getActiveCell(), value = aCell.getValue();
aCell.setValue('=HYPERLINK("https://docs.google.com/spreadsheets/d/' + ssID + '/edit#gid=' + GID + '&range=E'+aRow+'","'+e.value+'")');
}

Stuart Eade

unread,
Mar 26, 2019, 8:31:49 AM3/26/19
to google-apps-sc...@googlegroups.com
Hi Clark,

Thanks very much for your suggestion. However, there are many GIDs relating to many tabs.
For example, the drop down in column 4 that the user selects from, may contain many names of food products, with each product in the drop-down having its own worksheet and GID.

Best regards
manc

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.


--
Chad Frerichs
Director of Technology
ADM School District

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.


--
Chad Frerichs
Director of Technology
ADM School District


--
Chad Frerichs
Director of Technology
ADM School District

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/eb77e9e6-ab5b-480a-8942-b311546d6bb2%40googlegroups.com.

Stuart Eade

unread,
Mar 26, 2019, 8:33:02 AM3/26/19
to Google Apps Script Community
Hi Clark,

Thanks very much for your suggestion. However, there are many GIDs relating to many tabs.
For example, the drop down in column4 that the user selects from, may contain many names of food products, with each product in the drop-down having its own worksheet and GID (which is displayed in column5)

Best regards
manc

Clark Lind

unread,
Mar 26, 2019, 8:53:57 AM3/26/19
to Google Apps Script Community
hrm.. then you'll probably need another function that generates the GID for you, based on the content/worksheet. I was looking for a way to get the GIDs, but haven't found it yet.
I'm sure there are a few creative ways to do it...   a function that cycles through all the worksheets, grabs the urls, parses them for the GID, & store them on a helper-function worksheet.
That could then be the basis for a lookup table.. based on e.value, return GID. Or something to that affect.
Or you could do a Switch(e.value) kinda thing.

It isn't easy to make Sheets act like a database, but it can be done. I've connected many sheets in an attempt to simulate a DB, but you may want to consider using FireBase or something if you can.

Best,
Clark

Clark Lind

unread,
Mar 26, 2019, 9:02:10 AM3/26/19
to Google Apps Script Community
Nevermind, re: the GID if you already have all that figured out. :)  you should have what you need now I think .

Stuart Eade

unread,
Mar 26, 2019, 9:08:05 AM3/26/19
to Google Apps Script Community
Hi Clark,

I'm glad you think i have everything I need, because i have no clue if I do or don't! :)
The GIDs are sorted - they are displayed in column5.
It's how do I reference column5 (or the GID) dynamically in the script that is the issue.
e.value works greats to dynamically store the value from column4.
I need another value like this to temporarily store the GID.
I'm sure i tried this, but instead of displaying the actual GID, it displayed the actual cell reference instead, i.e. E20 as the GID, where E20 contains the GID. 


Best regards
manc

Stuart Eade

unread,
Mar 26, 2019, 10:49:19 AM3/26/19
to Google Apps Script Community
This code kind of works:

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/SSID/edit#gid=&range=E'+aRow+'","'+e.value+'")');
    }
  }
}


function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("https://docs.google.com/spreadsheets/d/SSID/edit#gid=&range=E'+aRow+'","'+value+'")');
  }


Except for this bit:
edit#gid=&range=E'+aRow+'"

It displays "....edit#gid=&range=E9" where instead of the highlighted text, if it was to display the actual value in cell E9 (which is a GID), then the code would work!

Come on, any takers.... Any takers for instant glory? lol

Best regards
manc

Reno Blair

unread,
Mar 26, 2019, 11:10:40 AM3/26/19
to google-apps-sc...@googlegroups.com
Try updating just that part of your link to
...edit#gid="&E'+aRow...

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.


--
Chad Frerichs
Director of Technology
ADM School District

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.


--
Chad Frerichs
Director of Technology
ADM School District


--
Chad Frerichs
Director of Technology
ADM School District

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a4955758-30d7-47a8-bef6-e119735e4235%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.


--
Reno Blair
​
Educational Technology Services​

Stuart Eade

unread,
Mar 26, 2019, 11:16:01 AM3/26/19
to Google Apps Script Community
No, i've tried that already Reno thanks. 
My conclusion to that was by adding the " where you say, i am prematurely ending the hyperlink.

Best regards
manc
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.


--
Chad Frerichs
Director of Technology
ADM School District

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.


--
Chad Frerichs
Director of Technology
ADM School District


--
Chad Frerichs
Director of Technology
ADM School District

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Stuart Eade

unread,
Mar 26, 2019, 11:20:35 AM3/26/19
to Google Apps Script Community
I can even change the code to read:

and it displays the link correctly except that it shows the cell ID (i.e. E20 if i'm on row 20) instead of the contents of E20 (GID reference).

ARRRRRGGGGGHHHHHHHH
To view this discussion on the web visit <a href="https://groups.google.com/d/msgid/google-apps-script-community/a4955758-30d

Reno Blair

unread,
Mar 26, 2019, 12:43:35 PM3/26/19
to google-apps-sc...@googlegroups.com
Let's take a step back and ensure that the functionality you want works:
  1. Go to sheet.new
  2. In cell A1 paste the following
    =HYPERLINK("https://www.google.com/search?q="&B1, "Search")
  3. In cell B1 type a search term.
  4. Inspect the resulting hyperlink in A1 to ensure it includes your search term in B1 instead of the string "B1".
Now we know that this method does not end the hyperlink prematurely. However, after further review, I found my suggestion did not go far enough - the double quote after aRow started another string extending the URL parameter of the hyperlink. The entire string passed to setValue should look like this:
'=HYPERLINK("https://docs.google.com/spreadsheets/d/SSID/edit#gid="&E'+aRow+',"'+e.value+'")'