CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
andywicks
andywicks

Reputation: 23

Google Sheets Merging Cells

I am creating a spreadsheet for the family tree. There is one row for each person and successive people can have a date of marriage. I would like to merge the cells in column F (the date of the marriage column) for the thousands of potential pairings. The code I have is:-

function mergeMarriageCells() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = ss.getDataRange().getValues();
  
  for (var i = 1; i < data.length; i += 2) {
    ss.getRange(i, 5, 2, 1).mergeVertically();
  }
}

This gives me an error in line 6, the one which starts with ss.getRange. The error it gives is:

"Exception: The parameters (String,number,number,number) don't match the method signature for SpreadsheetApp.Spreadsheet.getRange. (line 6, file "Code")".

However, i has been initialised to 1 and should therefore be understood as an integer and not a string.

By the way, the first row contains the headings, hence the loop starts at 1.

Upvotes: 1

Views: 222

Answers (1)

Marios
Marios

Reputation: 27400

Issue:

A Spreadsheet object does not have a method getRange. The latter is a method of a Sheet object.

Solution:

  1. Modify your code to consider the Sheet instead of the Spreadsheet.

You have the following ways to do that:

  var sh = ss.getSheetByName('Sheet1');
  var sh = ss.getSheets()[0] // 0 means first sheet, 1 means second sheet
  var sh = ss.getActiveSheet(); // the sheet that is currently selected in the file

Choose one of these three depending on what your goal is. See References section on what each of them does to find which suits you the best.

  1. Finally, if you want to merge the cells in column F, then you need to choose 6 instead of 5 (column E) in the second argument of getRange.

Result:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Sheet1');
  // var sh = ss.getSheets()[0] // 0 means first sheet
  // var sh = ss.getActiveSheet(); // the sheet that is currently selected
  var data = sh.getDataRange().getValues();

  for (var i = 1; i < data.length; i += 2) {
    sh.getRange(i, 6, 2, 1).mergeVertically();
  }
  
}

Please modify Sheet1 to your specific scenario.

References:

Upvotes: 1

Related Questions