Making all cells in a column links from the values within the cell?

Question

I have a column of cells in a Google Spreadsheet with values such as:

512
2123
2342
3532

What I would like to do is convert all these into a hyperlink and keep the value as the link text:

http://www.example.com/id/{value}

...where {value} is the value of the cell. I know the format of a hyperlink in a Google spreadsheet but I don't want to do this manually every time I put in a number. I want a simple process that when I add a new row the contents of this column is turned into a link with the value I input.

I tried this:

=HYPERLINK(CONCATENATE("http://www.example.com/id/",A1);A1)

But I get:

error: Circular dependency detected

Answer 1

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

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.

Answer 2

I modified Jacob's answer to be a little more flexible. Just copy & paste this script. A menu will get created with a macro "Convert Selected Cells To URLs". Says it all. Now select which cells, in the same column, you want converted to urls and click macro.

Script

// global
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {

  var menu = [{name: "Convert Selected Cells To URLs", functionName: "createURL"}];
  ss.addMenu("My Macros", menu);

}

function createURL() {

  var range = SpreadsheetApp.getActiveSheet().getActiveRange();
  if(range.getNumColumns() == 1) { 

    var numRows = range.getNumRows();

    for (var i = 1; i <= numRows; i++) {
      var value = range.getCell(i,1).getValue();
      range.getCell(i,1).setValue('=HYPERLINK("'+value+'","'+value+'")');
    }

  }

}

Answer 3

I found a way around it and no script needed. You just need to use another sheet.

See the updated sheet mentioned above for the working example.

=HYPERLINK(CONCATENATE("http://www.example.com/check-out.html?value1=",roundup('sheet2'!A1),"&value2=",$B30,"&value3",$C30),roundup('sheet2'!A1))

Hope this helps.