How do I split all merged cells for a specific sheet within a Google Spreadsheet using Python?

1
votes

I'm working with Google sheet API and I have defined the following functions:

def service():
    secret_id_gsuite_token = 'my_token' # AWS Secret with token
    gsheet = GSheets(secret_id_gsuite_token)
    return gsheet

To update a sheet with information present in a pandas dataframe:

def df_to_googlesheet(gsheet, df, gsheetId, sheet_name):   
    output = gsheet.spreadsheets.values().update(
    spreadsheetId = gsheetId,
    valueInputOption = 'RAW',
    range = sheet_name + '!A1',
    body = dict(
        majorDimension = 'ROWS',
        values = df.T.reset_index().T.values.tolist())
    ).execute()

To make a request to merge cells to form a "block":

def merge_cells_request(sheeId, start_row, end_row, start_col, end_col):
    request = [{"mergeCells": { "range": { "sheetId": sheeId,
                                           "startRowIndex": start_row,
                                           "endRowIndex": end_row,
                                           "startColumnIndex": start_col,
                                           "endColumnIndex": end_col},
                                "mergeType": "MERGE_ALL"}}]
    return request

I also have a similar function to make requests to color cells backgrounds. I put all my request in a requests list and apply them running:

gsheet.batch_update(gsheetId, requests)

This batch_update function uses the batchUpdate() method of google sheets api.

Finally this is the function to clear the sheet:

def clear_sheet(gsheet, spreadsheetId, sheeId):
    clear_request = [{"updateCells": {"range": {"sheetId": sheeId},
                                      "fields": "*"}}]

    gsheet.batch_update(spreadsheetId, clear_request)

The problem is that this last function does not clear the merged cells, I want to unmerge all merged cells in order to have the sheet totally clean again, but none of the options I found online seems to fit my code...

1
pythongoogle-sheetsgoogle-apigoogle-sheets-api

1 Answers

1
votes

I believe your goal and situation as follows.

  • From I want to unmerge all merged cells in order to have the sheet totally clean again, you want to unmerge cells in a sheet using Sheets API.
  • You want to achieve this using googleapis for python.
  • You have already been able to get and put values for Google Spreadsheet using Sheets API.

In this case, I would like to propose to use UnmergeCellsRequest of the batchUpdate method in Sheets API.

The request body is as follows.

{
  "requests": [
    {
      "unmergeCells": {
        "range": {
          "sheetId": sheeId
        }
      }
    }
  ]
}

  • Although I'm not sure about your whole script, from your following script

      def clear_sheet(gsheet, spreadsheetId, sheeId):
      clear_request = [{"updateCells": {"range": {"sheetId": sheeId},
                                        "fields": "*"}}]

      gsheet.batch_update(spreadsheetId, clear_request)

    • In this case, the request of gsheet.batch_update() might be as follows.

        [{"unmergeCells": {"range": {"sheetId": sheeId}}}]

Reference: