How can I update multiple merged cells with Google Sheets API in Python?

Question

I need to update (With blank info) with batch multiple merged cells with Python using Google Sheets API v4 (No gspread). I have this code:

blank = setValue("") <----- I set a variable with no data to delete all the info that the cell can contains
dataBlank = [
        ...
        {
            'range': 'AB83',
            'values': blank
        }
        {
            'range': 'BH75:BH82', # <------- I think here is the problem
            'values': blank
        } ...
    ]

body = {
        'valueInputOption': value_input_option,
        'data': dataBlank
    }
result = service.spreadsheets().values().batchUpdate(spreadsheetId=registro_id, body=body).execute()
print('{0} celdas borradas - Registro'.format(result.get('totalUpdatedCells')))

def setValue(value):
    rvalue = [value]
    avalue = [rvalue]
    return avalue

It runs, but in the sheet it only updates the first cell of the range, and the whole range does'n update. I try with 'range' : 'BH75:BH82', 'range' : 'Sheet1!BH75:BH82' and I can't update the whole range. I need to use the batch update because I need to check the performance and don't pass the quote limit from Google Cloud.

I het this:

Only the first cell is updated. If I update individual cells o merged cells without range it works fine, but if I use a range to update doesn't work.

Tanaike Tanaike · Accepted Answer · 2020-04-30T23:28:10

I believe your goal as follows.

You want to put zero to the cells BH75:BH82 in the 1st tab of Google Spreadsheet using the method of Method: spreadsheets.values.batchUpdate in Sheets API.
You want to achieve this using googleapis with python.

For this, how about this answer?

Modification points:

In the case of the method of Method: spreadsheets.values.batchUpdate in Sheets API, when you want to put the value to the cells BH75:BH82, it is required to be "values":[["sample"],["sample"],["sample"],["sample"],["sample"],["sample"],["sample"],["sample"]].
In your script, "values":[["sample"]] is used. By this, the value is put to only the 1st cell. I think that this is the reason of your issue.

Modified script:

When your script is modified, please modify as follows.

'range': 'BH75:BH82',
'values': zero

'range': 'BH75:BH82',
'values': [[value],[value],[value],[value],[value],[value],[value],[value]]

Unfortunately, from your script, I cannot understand about zero. So I used value as a variable. If you want to put the empty, please declare value as "".
This is a simple modification. So, of course, you can also create [[value],[value],[value],[value],[value],[value],[value],[value]] using a script.

Note:

If you want to put a value to a lot of cells, the RepeatCellRequest of batchUpdate method in Sheets API might be useful. Ref

Reference:

Method: spreadsheets.values.batchUpdate