OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Google Sheet Python API Cell formatting issue with 'repeatCell' request, resulting in no replies

  • Thread starter Thread starter Yunjong Guk
  • Start date Start date
Y

Yunjong Guk

Guest

  1. I am trying to format multiple cells with spreadsheets().batchUpdate(spreadsheetId=ssid, body=body).execute()


  2. firstly, I get formatting request using the following functions:

Code:
#this function gets called with all necessary variables from outside, the actual cell formatting dictionary object gets made at cell_config = self.get_cell_config.... 
    @get_spreadsheet
    def get_format_request(
        self,
        ssid:str,
        sheet_name:str,
        mode:str,
        start_row_idx:int,
        start_col_idx:int,
        hor_count:int,
        ver_count:int,
        **kwargs) -> 'Sheeter':
        request = {
            'repeatCell': {
                'range': {
                    'sheetId': self.get_sid(ssid, sheet_name),
                    'startRowIndex': start_row_idx+1,
                    'startColumnIndex': start_col_idx+1,
                    'endRowIndex': start_row_idx + ver_count,
                    'endColumnIndex': start_col_idx + hor_count
                },
                'fields': f'userEnteredFormat.{mode}'
            }
        }
        cell_config = self.get_cell_config(mode, **kwargs)
        request['repeatCell']['cell'] = cell_config
        self.spreadsheets[ssid]['requests'].append(request)
        return self

Code:
#mode is the keys in json representation section in the link
    def get_cell_config(self, mode:str, **kwargs) -> dict:
        # cell format documentation: "https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#CellFormat"
        cell_config = {'userEnteredFormat': {}}
        cell_config['userEnteredFormat'][mode] = kwargs
        return cell_config

Code:
#this is how .get_format_request() gets called
        sheeter = Sheeter()
    sheeter.get_format_request(
        ssid,
        sheet_name,
        'numberFormat',
        2,
        2,
        1,
        1,
                #below are kwargs input to the function to make cell_config
        type='NUMBER',
        pattern='#,##0'
    ).exhaust_requests(ssid)

Code:
# get result from google sheets api call
            body = {
                'requests': requests:list
            }
            result = self.ss_service.spreadsheets().batchUpdate(spreadsheetId=ssid, body=body).execute()

the following is my request and result:

Code:
REQUEST:
    body: {'requests': [{'repeatCell': {'cell': {'userEnteredFormat': {'numberFormat': {'pattern': '#,##0',
                                                                                        'type': 'NUMBER'}}},
                                        'fields': 'userEnteredFormat.numberFormat',
                                        'range': {'endColumnIndex': 3,
                                                  'endRowIndex': 3,
                                                  'sheetId': 1069313986,
                                                  'startColumnIndex': 3,
                                                  'startRowIndex': 3}}}]}
RESULT:
    result: {'code': 200,
             'error': None,
             'result': {'replies': [{}],
                        'spreadsheetId': '12dfnGKlA_vOFCPos3jiyqsHZQZtEVSJttnMM1Pn3gOQ'}}

I can clearly see that the response code is 200 but There are no replies and cell formatting does not get applied to the actual sheet.

Why is this happening? I have been searching for an hours?

I have tried with type='NUMBER', type='NUMBER' + pattern=f'{pattern}'

I saw that there is an option for updateCells, but I figured I might have to retrieve the cell values for and resend everything back, but I don't want to do this due to API quota limits.

Continue reading...
 

Latest posts

Top