/**
 * Add UTM-parameters to any URL to generate trackable links for Google Analytics
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function utm_link(url, id, campaign, source, medium, term, content) {

/* first, remove spaces */
var id = id;
var campaign = campaign;
var source = source;
var medium = medium;
var term = term;
var content = content;

/* build utm partials */
if(id) { var utm_id= "&utm_id=" + id; }
if(campaign) { var utm_campaign = "?utm_campaign=" + campaign; } else {utm_campaign = ""};
if(source) { var utm_source = "&utm_source=" + source; } else {utm_source = ""};
if(medium) { var utm_medium = "&utm_medium=" + medium; } else {utm_medium = ""};
if(term) { var utm_term = "&utm_term=" + term; } else {utm_term = ""};
if(content) { var utm_content = "&utm_content=" + content; } else {utm_content = ""};

/* check for mandatory fields and build final url */
if (url === '' || source === '' || medium === '') {
  return "Bitte Pflichtfelder befüllen";
}
else {
  var utm_link = url + utm_campaign + utm_source + utm_medium + utm_id + utm_term + utm_content;
  return utm_link;
}

}

/**
 * Converts all texts in the spreadsheet to lowercase.
 *
 * @param {spreadsheet} current spreadsheet to modify texts in.
 * @return No return needed.
 * @customfunction
 */
function onEdit(spreadsheet) {
  var spreadsheet = SpreadsheetApp.getActive();
  var range = spreadsheet.getRange("A2:G");
  var data  = range.getValues();

  for (var row = 0; row < data.length; row++) {
    for (var col = 0; col < data[row].length; col++) {
      // replace umlauts
      data[row][col] = (data[row][col]).toString().replace(/á|à|ã|ä|A/gi, 'a');
      data[row][col] = (data[row][col]).toString().replace(/ó|ò|ö|Ö|O/gi, 'o');
      data[row][col] = (data[row][col]).toString().replace(/ú|ù|ü|Ü|U/gi, 'u');
      // remove spaces
      data[row][col] = (data[row][col]).toString().replace(/\s/gi, '');
      // convert to lowercase
      data[row][col] = (data[row][col]).toString().toLowerCase();
   }
  }
  range.setValues(data);
}