How to set data and format in batch_update

Question

I want to fill some cells with values in my Python script. The first would be a date value. To format this date value is my problem. (I need it because it would be a part of a diagram where I would like to display HH:MM:SS but store the original date string in the cell.)

With my actual solution, I can write all the data, (the code is working in this aspect) but I have to constantly go into Google Sheets and format the first column in TIME format. I wonder if I can make it in the batchUpdate Sheet API method.

I am using gspread Python library to access Google Sheets API. The following is the relevant part of my code:

date_time_format= "%Y-%m-%d %H:%M:%S"

....

now = datetime.now()                    # current date and time
date_time_str = now.strftime(date_time_format)
NapiFogyasztas.batch_update([{
                        'range': 'A{0}'.format(next_row),
                        'values': [[date_time_str]],
                        }, {
                        'range': 'B{0}:G{0}'.format(next_row),
                        'values': [[pill_fogyasztas, pill_termeles, fogyasztas, termeles, "=D{0}-E{0}".format(next_row), "=B{0}-C{0}".format(next_row)]],
                        }], value_input_option='USER_ENTERED')

I have tried to add cell formatting, as I found here, without success.

Could anyone help me, please? What did I do wrong? How do I set the first cell value format in my Python code?

Answer 1

As I suggested, the problem is because of the gspread library. In their batch_update, I can only specify "range"s and "values" but not "format" They have another function call: batch_format, which could set the format of the cell(s). So my problem is solved.

    r2 = NapiFogyasztas.batch_format([{
        "range": date_range_str,
        "format": {
            "numberFormat": {
                "type": "DATE",
                "pattern": "hh:mm:ss"
            }
        }
    }])

It's fine for now, but I would like to reduce the numbers of the I/O for HTTP requests, so I am looking for a most precise solution to unify this 2 steps into only one as the Google Sheet API can do it in the batchUpdate method. Maybe I will look for another python library for writing spreadsheets or directly using the Google API.

But the script is working as was expected! Halleluja!