Add a script trigger to Google Sheet that will work in Android mobile app for merged cells

Question

I'm new here, and I have my first question regarding the following post here: Add a script trigger to Google Sheet that will work in Android mobile app

function onEdit(e) {
  if (e.range.getA1Notation() == 'B1') {
    if (/^\w+$/.test(e.value)) {        
      this[e.value]();
      e.range.clear();
    }
  }
}

function insertSomething() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2,3).setValue('inserted something');
}  

function convertSomething() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(3,3).setValue('converted something');
}  

This script worked for me for a non-merged cell range which is 'B1'. I need this script to work for a merged cell range like 'B1:D4'. I merged all range 'B1:D4' and then I modified the script to run for this range, but the result was always - the merged cells were unmerged and each single cell in the range inherited the data validation of the initial 'B1' cell that I set at the very beginning.

So my question is: how to change the above script so it will work for a range of multiple merged cells like 'B1:D4', not only for a single cell - 'B1'? (in other words: how to make 'B1:D4' to behave like a single cell 'B1' in the above mentioned script). Thank you for help!

P.S. I apologise if this question is breaking the rules of this site.

Answer 1

The example code you copied from Add a script trigger to Google Sheet that will work in Android mobile app¹ works for your use case but requires a couple of modifications.

Cells Unmerging
This is happening because the script explicitly removes the cell formatting each time it runs².

 e.range.clear();    // clears formatting

Use one of the following approaches in order to clear "only" the contents:

 e.range.clear({contentsOnly: true});
 
 //  or

 e.range.clearContent();

Location of setValue() Target
The locations the example functions write to are 'D2' and 'D3' which are within your merged range 'B1:D4'. Hard to test properly as you never see the result ;-)

I suggest moving the setValue() target to locations that are outside the merged range.

Instead of:

 sheet.getRange(2,3).setValue('inserted something');

 //  and

 sheet.getRange(2,3).setValue('converted something');

I would combine the two into one target, replacing both with a single target 'F2' located outside your merged range:

sheet.getRange(2,5).setValue('value');

To Recap
Based on the sample code you provided, the updated version would be:

function onEdit(e) {
  if (e.range.getA1Notation() == 'B1') {
    if (/^\w+$/.test(e.value)) {
      
            
      this[e.value]();
        e.range.clearContent();
  // or e.range.clear({contentsOnly: true});
    }
  }
}

function insertSomething() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2,5).setValue('inserted something');

}  

function convertSomething() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2,5).setValue('converted something');
}  

---

_{¹ Add a script trigger to Google Sheet that will work in Android mobile app
² https://developers.google.com/apps-script/reference/spreadsheet/range#clearoptions}