How to Add a Link to Google Search in a Range in Google Sheets

google sheets

I have a google spreadsheet, where I want to link each cell in a range to the google search page, where the search term is the text in this particular cell. So if I have a cell with content 'Bikes' I want to turn it to a link that will search for 'Bikes' in Google.

How do I achieve that short of manually going over each cell in the range and turning it into a link?

This is what I have:

before

Note that the list is much longer than a few cells.

This is where I want to arrive:

after

These will hyperlink to

google.com/search?q=Alpha
google.com/search?q=Beta
google.com/search?q=Gamma
google.com/search?q=Delta
etc…

Since there are a hundred of cells I would like a more practical method than inserting a hyperlink manually for each cell.

Best Answer

You could use INSERT > Link... to add a link to the active cell but it will require you to write the URL.

If you want that the URL be added automatically, you will need a Google Apps Script.

Instead, you could use a formula with the HYPERLINK function in a temporary column and use some built-in functions to do bulk operations.

I.E. if the search term is in A1 then use the following formula:

=hyperlink("www.google.com/search?q="&A1,A1)

Fill down as necessary.
Copy and paste as values over the column with the search terms.
Delete the temporary column.

Related keyboard shortcuts

Insert a link: CTRL+k
Copy: Ctrl+c
Paste as values: Ctrl+Shift+v

References

Related Solutions

Google Sheets – Generate Hyperlinks from Cell Contents

Assuming your values are in column A, you can do this in column B:

=HYPERLINK(CONCATENATE("http://mywebsite.com?id=",A1);"link text")

Then you can auto-fill down the rest of the column.

If you want the text of the link to be the same as the id you're linking, that's as simple as

=HYPERLINK(CONCATENATE("http://mywebsite.com?id=",A1);A1)

Google Sheets – Make All Cells in Column Links from Values

I wasn't able to reproduce your results. As a matter of fact, it worked perfectly.

enter image description here

What you tried to do is most probably the following:

In A1 you typed in =HYPERLINK(CONCATENATE("http://www.example.com/id/",A1);A1) and this yields an error of coarse.

Update

If you really want to get the result in A1, then you need to use a script.

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() == 1) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("http://www.example.com/id/'+e.value+'","'+e.value+'")');
    }
  }
}

function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("http://www.example.com/id/'+value+'","'+value+'")');  
}

Explained:

The e.value will retrieve the cells value (only applicable a cell). The setValue() will add the concatenated string into the getActiveRange(). All is only executed when e.value contains something and the active range is in column A.

I've created an extra menu option as well, to be able access the script this way.

Example:

I've created an example file for you: onEdit URL builder
Add this script via Tools>Script editor, into the script editor. Press the "bug" button and you can use the script.