Google-sheets – Add email hyperlink to spreadsheet

google sheetsgoogle-apps-script

I have a spreadsheet where I track incoming email questions and outgoing responses. As questions come in, I have a script that appends a row (using .appendRow()) with information in the first few cells. To make sending the responses easier, I would like to include the email address of the questioner as a hyperlink in one cell of the new row. I've tried using =HYPERLINK(...) and get a syntax error.

Best Answer

Here's one way to make this work. It would be nice not to have the "mailto" show up, but this way does provide the correct functionality.

var string1 = new String ('mailto:');  
var string2 = joeblow@gmail.com;  
var address = string1.concat(string2);  
    sheet.appendRow(["", xxxx, yyyy, address]);

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.

Google Apps Script – Fix Maximum Execution Time Exceeded

Key in working with Google Apps Script is to handle things in batches. API calls are time consuming and need to be kept at a bare minimum.

Try applying the following:

You're trying to retrieve appr. 3000 values by using getValue(). Better is to use the getValues() and add that to an array (all data that is !!). This minimizes API calls by 2999 times; var allData = SpreadsheetApp.getActiveSpreadsheet().getDataRange().getValues().
The same is applicable for the setValue(). Here, you can also use setValues() to batch that process and thus minimizing the API calls.

Furthermore, most of the script is basically the same. Why not squeeze that in a standardized piece of code?

References:

Best Answer

Related Solutions

Google Sheets – Make All Cells in Column Links from Values

Update

Code

Explained:

Example:

Google Apps Script – Fix Maximum Execution Time Exceeded

Related Topic