How to have bolded headers, but non-bolded cells - filled with worksheet.append_table of pygsheets==2.0.1

Question

The python3 (pygsheets 2.0.1) script below will bold all the cells starting at A2.
Is there an easy way (i.e., in one command) to ask for all these cells not to be bolded?

Code:

import boto3, botocore
import datetime
import json
import pygsheets

currentDT = str(datetime.datetime.now())

def create_spreadsheet(outh_file, spreadsheet_name = "jSonar AWS usage"):
    client = pygsheets.authorize(outh_file=outh_file, outh_nonlocal=True)
    spread_sheet = client.create(spreadsheet_name)
    return spread_sheet


def get_regions():
    region = "us-west-1"
    regions = dict()

    ec2 = boto3.client("ec2", region_name=region)
    ec2_responses = ec2.describe_regions()

    ssm_client = boto3.client('ssm', region_name=region)
    for resp in ec2_responses['Regions']:
        region_id = resp['RegionName']
        tmp = '/aws/service/global-infrastructure/regions/%s/longName' % region_id
        ssm_response = ssm_client.get_parameter(Name = tmp)
        region_name = ssm_response['Parameter']['Value'] 
        regions[region_id] = region_name
    return(regions)


def rds_worksheet_creation(spread_sheet, regions, spreadsheet_index):
    worksheet = spread_sheet.add_worksheet("RDS", rows=100, cols=26, src_tuple=None, src_worksheet=None, index=spreadsheet_index)
    worksheet.cell('A1').set_text_format('bold', True).value = 'DBInstanceIdentifier'
    worksheet.cell('B1').set_text_format('bold', True).value = 'MasterUsername'
    worksheet.cell('C1').set_text_format('bold', True).value = 'Region'
    worksheet.cell('D1').set_text_format('bold', False).value = 'Sent Query to (Name)'
    worksheet.cell('E1').set_text_format('bold', False).value = 'Sent Query to (email)'
    worksheet.cell('F1').set_text_format('bold', False).value = 'WorksheetCreated: %s' % currentDT
    cells_data = list()
    for region, region_h in sorted(regions.items()):
        client = boto3.client('rds', region_name=region)
        clnt = boto3.client('ssm', region_name=region)
        db_instances = client.describe_db_instances()
        for instance in db_instances['DBInstances']:
            MasterUsername = instance['MasterUsername']
            DBInstanceIdentifier = instance['DBInstanceIdentifier']
            cells_data.append([DBInstanceIdentifier, MasterUsername, region_h])
    worksheet.append_table(cells_data, start='A2')


if __name__ == "__main__":
    spread_sheet = create_spreadsheet(spreadsheet_name = "jSonar AWS usage",
                                                            outh_file = '/home/qa/.aws/client_secret.json')
    regions = get_regions()
    rds_worksheet_creation(spread_sheet, regions, 0)
    spread_sheet.share("me@corp.com")

Output:

I apologize for my poor English skill. Can I ask you about your question? 1. Your output image is the result you want? If it's the current result, can you provide the result you want? 2. Can I ask you about ``all the cells starting at A2.``? 2. Can I ask you about ``i.e., in one command``? — Tanaike, Jun 07 '19 at 23:48
{1} The image, @Tanaike, shows the current results. {2} all the rows 2-20. — boardrider, Jun 09 '19 at 07:30
Thank you for replying. Unfortunately, I couldn't understand about your goal from your replying. I apologize for my poor English skill. — Tanaike, Jun 09 '19 at 22:31

score 2 · Accepted Answer · answered Jun 08 '19 at 03:19

2

If i understand correctly you want to un-bold multiple cells in single command.

To set format to a range of cells create a Datarange and use apply_format.

model_cell = Cell('A1')
model_cell.set_text_format('bold', False)
Datarange('A1','A10', worksheet=wks).apply_format(model_cell)

docs

answered Jun 08 '19 at 03:19

Nithin

5,470
37
44

Thanks for the answer, @nithin. If I want all cells after 'A2' to be unbolded, is there a more elegant way to do that than to use `Datarange('A1','Z99999', worksheet=wks).apply_format(model_cell)`? (I tried removing the end_cell argument, but got `pygsheets.exceptions.InvalidArgumentValue: addr of type `) – boardrider Jun 10 '19 at 22:10
there is no inbuild way to skip this. but you can do `Datarange('A1',(1, wks.cols), worksheet=wks)` – Nithin Jun 11 '19 at 04:07
I don't think that's the correct way to get all non-heading lines to be bolded (it only unbolded the first row). I eventually used (the still non-elegant): `pygsheets.DataRange(start_cell, "ZZ%s" % worksheet.rows, worksheet=worksheet).apply_format(model_cell)` – boardrider Jun 13 '19 at 22:29

How to have bolded headers, but non-bolded cells - filled with worksheet.append_table of pygsheets==2.0.1

Code:

Output:

1 Answers1