3

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.

0

1 Answer 1

1

The example code you copied from Add a script trigger to Google Sheet that will work in Android mobile app1 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 runs2.

 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');
}  

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.