Change Google Spreadsheets cell horizontal alignment using python

Question

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()

score 2 · Accepted Answer · answered Oct 23 '21 at 00:28

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

Hello again!! It works perfectly for my case, thank you for your help!! — Orestis D, Oct 25 '21 at 07:32
Hello @Tanaike. I've got this sheet: https://docs.google.com/spreadsheets/d/1VMnBIiiymEpTDYKQTUHd7V_g6ZWe7ueLZ91PtFQgp2s/edit?usp=sharing On the "Report" Tab, I'm looking for a way to get the Uppercase cells from the range `A5:B` to be centered on the Data Validation selection event. How would you do that? I'll ask a fully-fledged new question if needed. Thanks in advance for your help! — Lod, Feb 19 '23 at 21:12
@Lod I would like to support you. But, this is not your question. So, can you post it as a new question by including more information? By this, it will help users including me think of a solution. If you can cooperate to resolve your question, I'm glad. Can you cooperate to do it? — Tanaike, Feb 19 '23 at 22:39
Sure, I posted a question here: https://stackoverflow.com/questions/75510180/how-to-horizontally-center-align-only-the-uppercase-cells-from-a-range-with-goog Thanks! — Lod, Feb 20 '23 at 14:00
@Lod Thank you for replying. When I saw your new question, I noticed that your issue has already been resolved. I'm glad about it. — Tanaike, Feb 21 '23 at 00:44

score 1 · Answer 2 · answered Oct 22 '21 at 15:16

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.

Change Google Spreadsheets cell horizontal alignment using python

2 Answers2

Issue and workaround:

Modified script:

Reference:

Under the resource SpreadsheetProperties it is specified:

Linked