Insert text to selected range with button

user_7502029 0
Viewed 120

I apologize in advance for having little to no knowledge on this matter. I am familiar enough to utilize VBA but have recently made the switch to Sheets and I am lost.

I was originally looking for a way to prompt a drop down menu when double clicking a cell in a range that will then add the selected information as a prefix to the text.

Example: cell contains "First Last", upon double clicking you get a drop down to select "Mr., Mrs." when selecting "Mr." the cell now says "Mr. First Last" but it doesn't appear that you can set a double click event.

So now I am trying to come up with another solution where you select desired cells and 'apply' the function.

Example: Select cells A1 B1 C3 D9, click a button that applies "Mr. " as a prefix. Selecting it again will remove it.

Please help me get this moving.

google-apps-script javascript
1 Answers

Try this:

// menu

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⚡ Scripts')
  .addItem(' Add Mr', 'add_Mr')
  .addItem('Remove Mr', 'remove_Mr')
  .addToUi();
}


// functions

function add_Mr() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var selection = sheet.getSelection();
  var ranges = selection.getActiveRangeList().getRanges();
  for (var i in ranges) {
    var data = ranges[i].getValues();
    for (var row in data) for (var col in data[row])
      if (data[row][col] != '') data[row][col] = 'Mr. ' + data[row][col];
    ranges[i].setValues(data);
  }
}

function remove_Mr() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var selection = sheet.getSelection();
  var ranges = selection.getActiveRangeList().getRanges();
  for (var i in ranges) {
    var data = ranges[i].getValues();
    for (var row in data) for (var col in data[row])
      if (data[row][col] != '') data[row][col] = data[row][col].replace(/^Mr\. /,'');
    ranges[i].setValues(data);
  }
}

It will add menu Scripts and two commands 'Add Mr' and 'Remove Mrs' for selected cells.

enter image description here

And as a homework you can try to add add_Mrs() and remove_Mrs() functions.

If you need to toggle 'Mr' here you go:

function toggle_Mr() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var selection = sheet.getSelection();
  var ranges = selection.getActiveRangeList().getRanges();
  for (var i in ranges) {
    var data = ranges[i].getValues();
    for (var row in data) for (var col in data[row]) {
        var cell = data[row][col];
        if (cell == '') continue;                      // if empty --> go to next cell
        if (cell.match(/^Mr\. /)) {                    // if contains 'Mr.'
          data[row][col] = cell.replace(/^Mr\. /,'');  // --> remove 'Mr.'
          continue;                                    // --> go to next cell
        } else {
          data[row][col] = 'Mr. ' + data[row][col];    // else --> add 'Mr.'
        }
    }
    ranges[i].setValues(data);
  }
}

Update

Here is the same function toggle_Mr() with example how you can get the 'coordinates' of all processed cells:

function toggle_Mr() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var selection = sheet.getSelection();
  var ranges = selection.getActiveRangeList().getRanges();
  for (var i in ranges) {

    // get 'coordinates' (col, row) of first (left-top) cell of selected range
    var start_col = ranges[i].getColumn(); 
    var start_row = ranges[i].getRow();
    
    var data = ranges[i].getValues();

    for (var row in data) for (var col in data[row]) {
      var cell = data[row][col];

      // calculate the real 'coordinates' of processed cells
      var real_col = +start_col + +col;
      var real_row = +start_row + +row
      console.log('Process the cell [' + real_col + '][' + real_row + ']');
      
      if (cell == '') continue;
      if (cell.match(/^Mr\. /)) {
        data[row][col] = cell.replace(/^Mr\. /,'');
        continue;
      } else {
        data[row][col] = 'Mr. ' + data[row][col];
      }
    }
    ranges[i].setValues(data);
  }
}

You can see the cell 'coordinates' in console if you run the function from the Script Editor.

user_142654690
Related