How to merge cells with Google Sheets API?

Question

I am using the Python client for the Google Sheets API to build a spreadsheet. I am able to create a new sheet and update values in it, but I have been unable to merge cells for the header row that I want.

top_header_format = [
    {'mergeCells': {
        'mergeType': 'MERGE_COLUMNS',
        'range': {
            'endColumnIndex': 3,
            'endRowIndex': 1,
            'sheetId': '112501875',
            'startColumnIndex': 0,
            'startRowIndex': 0
        }
    }},
    {'mergeCells': {
        'mergeType': 'MERGE_COLUMNS',
         'range': {
             'endColumnIndex': 5,
             'endRowIndex': 1,
             'sheetId': '112501875',
             'startColumnIndex': 3,
             'startRowIndex': 0
         }
    }}
]

service.spreadsheets().batchUpdate(
    spreadsheetId=spreadsheet_id,
    body={'requests': top_header_format}
).execute()

This is the code I am running. There are no errors. The response's replies are empty and the spreadsheet doesn't have merged cells. The documentation is here.

score 9 · Accepted Answer · edited Jan 03 '19 at 15:37

9

Start indexes are inclusive and end indexes are exclusive, so by asking to merge row [0,1) you're saying "i want to merge row 0 into row 0", which is a no-op. You probably want [0,2), e.g:

top_header_format = [
    {'mergeCells': {
        'mergeType': 'MERGE_COLUMNS',
        'range': {
            'endColumnIndex': 4,
            'endRowIndex': 2,
            'sheetId': '112501875',
            'startColumnIndex': 0,
            'startRowIndex': 0
        }
    }},
    {'mergeCells': {
        'mergeType': 'MERGE_COLUMNS',
         'range': {
             'endColumnIndex': 6,
             'endRowIndex': 2,
             'sheetId': '112501875',
             'startColumnIndex': 3,
             'startRowIndex': 0
         }
    }}
]

edited Jan 03 '19 at 15:37

tehhowch

9,645
4
24
42

answered Apr 19 '17 at 17:03

Sam Berlin

3,603
12
23

1

(Although, now that I look back, you're saying MERGE_COLUMNS... so specifying a single row shouldn't be an issue since you already had multiple columns. So my answer might not be meaningful. Let me check and get back.) – Sam Berlin Apr 19 '17 at 17:05
2

OK, the above answer is correct. The enum names just always confuse me. MERGE_COLUMNS is documented as "Create a merge for each column in the range" -- so that means rows are merged together. Since you specified MERGE_COLUMNS with only a single row, there's no rows to merge together. You can specify MERGE_ALL or MERGE_ROWS to see things merging with your prior indexes. – Sam Berlin Apr 19 '17 at 17:14
Thanks. The issue was me being confused by the enums as well. I have it working now as expected. – ctp_9 Apr 19 '17 at 18:57

score 1 · Answer 2 · answered Jan 03 '19 at 10:26

If someone looking for doing merging with PHP, you can use this code below:

For merging cells first you will have to download the PHP library from composer as in their documentation ( https://developers.google.com/sheets/api/quickstart/php ) once installed follow their guide to set up your client to get authenticated.

    //$client will be your Google_Client authentication, for more info check the documentation link above.

Use below code for doing merging of rows and columns

    $service = new Google_Service_Sheets($client);

    $spreadsheetId = '1jfUz2VMUUEt2s4BP2Ye';    //this is test spreadsheet it, use your spreadsheet id here

    $rangeinst = new Google_Service_Sheets_GridRange();
    $rangeinst->setSheetId(0);  //your sheet id
    $rangeinst->setStartRowIndex(1);    // row index from where to start
    $rangeinst->setEndRowIndex(11); //ending row upto which you want merging
    $rangeinst->setStartColumnIndex(0); //start of column index, first column has 0 index like row
    $rangeinst->setEndColumnIndex(4);   //end of column upto which you want merging
    $merge = new Google_Service_Sheets_MergeCellsRequest();
    $merge->setMergeType('MERGE_COLUMNS');  //merge type request
    $merge->setRange($rangeinst);

    $requestBody = new Google_Service_Sheets_Request();
    $requestBody->setMergeCells($merge);
    $requestBatchBody = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
    $requestBatchBody->setRequests($requestBody);


    $response = $service->spreadsheets->batchUpdate($spreadsheetId, $requestBatchBody);
    echo "<pre>";
    print_r($response);

How to merge cells with Google Sheets API?

2 Answers2

Linked