Change Google Spreadsheets cell horizontal alignment using python-CodePudding

I've been trying to configure the cell horizontal alignment format of a google spreadsheet in python. I've read the original google developers information and have also checked other examples from people facing the same issue, but none of them applies to my example. The following code is the one I use to configure my spreadsheet request body. What I want to do is to create a new spreadsheet in which the horizontal alignment of all the cells is centered. As a result, when a user types anything in any cell, it will be centered automatically. Any tips?

spreadsheet_body = {
    'properties': {
        # spreadsheet name
        'title': sheet_name,
        "defaultFormat":{
            "horizontalAlignment":'CENTER'
        }
    },
    'sheets': [{
        'properties': {
            # worksheet name
            'title': 'Φύλλο1',
            'gridProperties': {
                # row\column number
                'rowCount': 100,
                'columnCount': 20
            },
        },
        'data': [{'rowData': [{'values': header_row}]}] # Added
    }
    ]
}


request = service.spreadsheets().create(body=spreadsheet_body)
response = request.execute()

CodePudding user response：

Under the resource SpreadsheetProperties it is specified:

defaultFormat

This field is read-only.

So unfortunately it is not possible to create with the Sheets API a spreadsheet where all cells are centered automatically - just like it is not possible to do it via the UI.

CodePudding user response：

I believe your goal is as follows.

When a new Spreadsheet is created using the method of spreadsheets.create of Sheets API, you want to set the horizontal alignment of the cells.
You want to achieve this using googleapis with python.

Issue and workaround:

Unfortunately, "defaultFormat":{"horizontalAlignment":'CENTER'} of Spreadsheet property cannot be used. This is the current specification. This has already been mentioned ziganotschka's answer.

When I saw your script, the created new Spreadsheet has one sheet. In this case, when this sheet is created, the horizontal alignment of all cells in the sheet can be set as CENTER. But in this case, when a new sheet is created, the default format is used. Please be careful about this. So this answer is a workaround.

When this workaround is reflected in your script, it becomes as follows.

Modified script:

rowCount = 100
columnCount = 20

rowData = []
for r in range(1, rowCount):
    temp = []
    for c in range(0, columnCount):
        temp.append({'userEnteredFormat': {'horizontalAlignment': 'CENTER'}})
    rowData.append({'values': temp})
hlen = len(header_row)
if hlen < columnCount:
    for c in range(0, columnCount - hlen):
        header_row.append({'userEnteredFormat': {'horizontalAlignment': 'CENTER'}})
rowData.insert(0, {'values': header_row})

spreadsheet_body = {
    'properties': {
        # spreadsheet name
        'title': sheet_name
    },
    'sheets': [{
        'properties': {
            # worksheet name
            'title': 'Φύλλο1',
            'gridProperties': {
                # row\column number
                'rowCount': rowCount,
                'columnCount': columnCount
            },
        },
        'data': [{'rowData': rowData}]
    }]}

request = service.spreadsheets().create(body=spreadsheet_body)
response = request.execute()

Reference:

Method: spreadsheets.create