Currency format not getting allied to google sheet with gspread library

Question

I have CSV data that looks like this:

"Service Name   ap-northeast-1","2023-03","2023-04","2023-05","2023-06","2023-07","2023-08","2023-09","2023-10","2023-11","2023-12","2024-01","2024-02"
"AWS Direct Connect","1575.24","1512.77","1629.79","1497.54","1504.33","1484.96","1543.41","1510.99","1404.84","1428.71","1424.15","1361.66"
"AWS ELB","76.29","75.02","80.48","79.06","71.27","72.74","74.61","77.1","72.42","78.28","73.62","73.42"
"AWS S3","51.77","53.71","52.56","53.83","55.7","53.57","54.26","55.99","55.97","56.97","56.75","56.94"
"AWS VPC","0","0","0","0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","48.73"
"EBS Volumes gp2","403.58","414.48","414.48","414.48","427.1","438.48","438.48","438.48","438.48","438.48","438.48","438.48"
"EBS Volumes gp3","206.21","206.21","206.21","206.21","206.21","206.21","206.21","206.21","207.42","208.13","208.13","208.13"
"EBS Volumes","491.52","491.52","491.52","491.52","491.52","491.52","491.52","491.52","491.52","491.52","491.52","491.52"
"EC2 - Other","1512.1","1502.28","1523.26","1513.06","1518.93","1523.01","1533.47","1525.14","1502.29","1503.11","1492.42","1485.31"
"EC2","1235.61","1159.1","1175.3","1151.3","1155.16","1141.6","1123.86","1160.83","1120.61","1481.42","1939.9","1320.95"
"PublicIPv4:InUseAddress","0","0","0","0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","48.73"
"Other          ap-northeast-1","0.0","0.0","-0.01","-0.01","0.01","0.0","-0.0","0.0","0.0","-0.0","0.0","-48.73"
"TOTAL:         ap-northeast-1","4451.01","4302.88","4461.38","4294.78","4305.4","4275.88","4329.61","4330.05","4156.13","4548.49","4986.84","4347.01"

All but the first column represents US dollars. I want to apply a currency format to those cells, so I have this python code:

import gspread
import csv


... global vars and stuff ...

with open('your_csv_file.csv', 'r', encoding='utf-8') as csv_file:
    csv_reader = csv.reader(csv_file)
    for row in csv_reader:
        if not row:  # Check if the row is empty
            print(f"Adding blank row: {row}.")
            spreadsheet.sheet1.append_row(
                ['----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----',
                 '----'])
            row_count += 1
        else:
            while True:
                try:
                    print(f"Adding row {row}")
                    added_row = spreadsheet.sheet1.append_row(row)
                    row_count += 1
                    if "Service Name" in row[0]:  # Check if "Service Name" is in the first column
                        print(f"make HEADER BOLD row: {row}.")
                        spreadsheet.sheet1.format(f"A{row_count}:M{row_count}", service_name_row_format)
                    else:
                        print(f"set $$ format for row_count {row_count}")
                        spreadsheet.sheet1.format(f'B{row_count}:M{row_count}', currency_format)

... more unrelated code ...

I do see the output ...

set $$ format for row_count 2

... but the format of the money cells never gets changed. Here is a screenshot:

Update:

It turns out that all I needed to change was this line ...

spreadsheet.sheet1.append_row(row)

... to this ...

spreadsheet.sheet1.append_row(row, value_input_option="USER_ENTERED")

Answer 1

I believe your goal is as follows.

• From All but the first column represents US dollars. I want to apply a currency format to those cells, you want to append the CSV data, and set the bold type to the header row, and set the number format by adding $ to columns "B" to "M".
• You want to achieve this using gspread.

In your showing script, how about the following modification?

Modified script:

spreadsheetId = "###" # Please set your Spreadsheet ID.

spreadsheet = client.open_by_key(spreadsheetId)
worksheet = spreadsheet.sheet1

offset = len(worksheet.get_all_values())
values = []
formats1 = []
formats2 = []
with open('your_csv_file.csv', 'r', encoding='utf-8') as csv_file:
    csv_reader = csv.reader(csv_file)
    for i, row in enumerate(csv_reader):
        if not row:  # Check if the row is empty
            print(f"Adding blank row: {row}.")
            values(['----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----', '----'])
        else:
            values.append(row)
            if "Service Name" in row[0]:
                print(f"make HEADER BOLD row: {row}.")
                formats1.append(f'{gspread.utils.rowcol_to_a1(offset + i + 1, 1)}:{gspread.utils.rowcol_to_a1(offset + i + 1, len(row))}')
            else:
                print(f"set $$ format for row_count {i + 1}")
                formats2.append(f'{gspread.utils.rowcol_to_a1(offset + i + 1, 2)}:{gspread.utils.rowcol_to_a1(offset + i + 1, len(row))}')

worksheet.append_rows(values, value_input_option="USER_ENTERED")
if formats1 != []:
    worksheet.format(formats1, {"textFormat": {"bold": True}})
if formats2 != []:
    worksheet.format(formats2, {"numberFormat": {"type": "CURRENCY", "pattern": "\"$\"#,##0.00"}})

• In this modification, all values are put by append_rows.

• If the values are not put from column "A", please modify the above script as follows.

  • From

    worksheet.append_rows(values, value_input_option="USER_ENTERED")
    

  • To

    worksheet.update(gspread.utils.rowcol_to_a1(offset + 1, 1), values, value_input_option="USER_ENTERED")
    

Testing:

When your provided CSV data is used for this modified script, the following result is obtained. In this sample, your CSV values are appended from row 6.