Requesting a Spreadsheet range value return "You must select all cells in a merged range..."

Question

I'm trying to read in a single cell range in a Google Spreadsheet I've been maintaining for about a year now. Today, I've been getting the following error in my logs:

You must select all cells in a merged range to merge or unmerge them.

I don't have any merged cells in that sheet. I've noticed that the Range method also has a "isPartOfMerge()" method. Trying this on the returned Range object raises the same error as before.

var sht_settings = ss.getSheetByName('Settings');
// lots of code in between
var reg_rate = sht_settings.getRange("B9").getValue();

Normally, I would get an integer correlating to the value in the cell, like "20" or "35". Now just getting the reported error.

Update: Discovered the line that is causing the problem. Running the following sequence allows the correct value to be read.

var reg_rate = sht_settings.getRange(9, 2).getValue();
new_sht.getRange("F3:G3").merge().setValue('INVOICE').setFontSize(18).setFontWeight('bold').setHorizontalAlignment('right');

By changing the getRange() method to refer to a single cell and remove the merge() method allows it all to work.

Questions looking for help to debug code should include a minimal reproducible example. In the specific case of Google Sheets scripts, include a demo spreadsheet shared with anyone with the link. — Rubén
Thanks, I'll try to prepare an example of this as soon as I can. — wsidl
It looks that you already found the solution. It should be posted as an answer rather than a edit of the question. — Rubén

wsidl wsidl · Accepted Answer · 2019-07-17T19:24:08

FYI for anyone dealing with the same issue:

The issue was discovered to be editing another sheet that the code was updating. The other sheet had merged cells and the format was slightly changed. Now the code retrieved ranges with merged cells overlapped. To fix this was by removing all merges in the other sheet and re-running the code.

Instead of reporting these changes on those lines involving the merges/updates, the script was reporting the change somewhere else.

Check the following Spreadsheet I've created to test the issue: https://docs.google.com/spreadsheets/d/14Ym0GQl3fMhlHljGfWZeUbZRTJMQ53vrA3BYBjbNISU/edit?usp=sharing

The code in the script editor looks like

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sht1 = ss.getSheetByName('Sheet1');
var sht2 = ss.getSheetByName('Sheet2');

function myFunction() {
  var val1 = sht1.getRange(1, 2).getValue();
  sht2.getRange(2, 2, 1, 2).merge().setValue(val1);
  var val2 = sht1.getRange(2, 2).getValue();
  sht2.getRange(3, 2, 1, 2).merge().setValue(val2);
}

"Sheet1" contains values in B1 and B2, "Sheet2" is blank except for a Merged Cell with B2:C2.

In this example, running myFunction() will error on line 8 instead of line 7.

Requesting a Spreadsheet range value return "You must select all cells in a merged range..."

1 Answers