Making the first line bold using google sheets API

Question

I am trying to make the first line bold of a google sheets sheet:

from oauth2client.service_account import ServiceAccountCredentials
import gspread
from df2gspread import df2gspread as d2g


GSHEETS_SCOPES = [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive.file",
    "https://www.googleapis.com/auth/drive"
                 ]


# Establishing connection to google sheets

CLIENT_SECRET_GOOGLE_SHEETS = r"folder\client_secret_google_sheets.json"
creds = ServiceAccountCredentials.from_json_keyfile_name(CLIENT_SECRET_GOOGLE_SHEETS, GSHEETS_SCOPES)
client = gspread.authorize(creds)
sheet = client.open("my_sheet")

I am able to write a pandas dataframe to the sheet like so:

d2g.upload(df, sheet.id, 'test', clean=True, credentials=creds, col_names=True, row_names=False)

Then I am trying to change so that the first line would be bold, I've checked this answer and tried this:

DATA = {'requests': [
    {'repeatCell': {
        'range': {'endRowIndex': 1},
        'cell':  {'userEnteredFormat': {'textFormat': {'bold': True}}},
        'fields': 'userEnteredFormat.textFormat.bold',
    }}
]}

sheet.spreadsheets().batchUpdate(spreadsheetId=sheet.id, body=DATA).execute()

But I get :

AttributeError: 'Spreadsheet' object has no attribute 'spreadsheets'

Then I checked this answer and tried:

ws = sheet.worksheet('test') 

DATA = {'requests': [
    {'repeatCell': {
        'range': {'endRowIndex': 1},
        'cell':  {'userEnteredFormat': {'textFormat': {'bold': True}}},
        'fields': 'userEnteredFormat.textFormat.bold',
    }}
]}

sheet.spreadsheets().batchUpdate(spreadsheetId=ws.id, body=DATA).execute()

But I am still getting the same error.

score 3 · Accepted Answer · answered Aug 19 '21 at 11:52

I was able to achieve it using:

ws = sheet.worksheet('test') 
ws.format('1', {'textFormat': {'bold': True}})

For further formatting I used:

ws.format("1", {
    "backgroundColor": {
      "red": 1.0,
      "green": 1.0,
      "blue": 1.0
    },
    "horizontalAlignment": "CENTER",
    "textFormat": {
      "fontSize": 12,
      "bold": True
    }
})

This documentation is really helpful.

score 0 · Answer 2 · answered Aug 19 '21 at 11:54

I have used the formated the local excel file using openpyxl library documentation. Hope that this might help.

from openpyxl import load_workbook, Workbook
from openpyxl.styles import *
from openpyxl.utils import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows


    try:
        file_name = root + f"my_file.xlsx"
        sheet_name = f"Stats Details"

        try:
            writer = load_workbook(file_name)
        except FileNotFoundError:
            writer = Workbook()
        ws = None
        exist = True
        try:
            ws = writer[sheet_name]
        except KeyError:
            writer.create_sheet(title=sheet_name, index=0)
            ws = writer[sheet_name]

            # (Optional) For removing the other sheets except sheet_name where we will write the data
            for s_names in writer.sheetnames:
                if s_names != sheet_name:
                    writer.remove(writer[s_names])

            exist = False
        finally:
            # Writing the data into excel sheet
            # print("current Worksheet :", ws)
            if exist == False:
                # if Excel Sheet doesn't exist then will include the header
                for rows in dataframe_to_rows(df_data, index=False, header=True):
                    ws.append(rows)
            else:
                # if Excel Sheet already exist then will include only data
                for rows in dataframe_to_rows(df_data, index=False, header=False):
                    ws.append(rows)

            # Formating the header
            for col in ws["1:1"]:
                col.font = Font(bold=True)
                col.border = Border(left=Side(style='thin'),
                                    right=Side(style='thin'),
                                    top=Side(style='thin'),
                                    bottom=Side(style='thin'))
                col.alignment = Alignment(horizontal="center", vertical="center")
                col.fill = PatternFill(fgColor="FFFFFF00", fill_type="solid")

            # Formating the values
        column_values_widths = []
        for i, col in enumerate(df_data.columns):
            column_values_widths.append(max(df_data[col].astype("str").str.len()))
        column_header_widths = [len(i) + 5 for i in df_data.columns]
        column_widths = []
        for value, header in zip(column_values_widths, column_header_widths):
            column_widths.append(max(value, header))
        for i, column_width in enumerate(column_widths):
            ws.column_dimensions[get_column_letter(i + 1)].width = column_width
            ws.column_dimensions[get_column_letter(i + 1)].alignment = Alignment(horizontal="center",
                                                                                 vertical="center")
        # Saving the
        writer.save(file_name)
        print(f"Data saved in {file_name}")
        return file_name
    except Exception as e:
        print(f"Error occurred While prepare for excel data\n{e}")

Making the first line bold using google sheets API

2 Answers2