How to export full Google Sheet tab as PDF and then print to local printer using Python

Question

After several weeks, I'm getting very close into solving my overall issue. I'm looking to connect my Google Sheet to a local printer & print the tabs "Label" and "Claims". Label needs to be printed x times, x being defined by the value in cell f2. Claims should be printed once as well.

This seems all taken care of, but for some reason the full Google Sheet tab isn't being exported to a PDF correctly.

It seems like the label is printing in portrait, when it should be landscape & the text maybe needs to be larger (image below how it is printing)?

[![Wrong Label][1]][1]

I can't seem to figure out how to adjust this. The "Claims" tab just prints blank, but I think it is the same issue for both.

Any thoughts on how I could adjust this code to fully print the tab? Sample URL added:

https://docs.google.com/spreadsheets/d/1MmUb7aPR9JVy-o2erbIDFzoY1iH9M5skcBRYNTuB_Ug/edit#gid=904472643

import os
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import win32print
from reportlab.pdfgen import canvas
from reportlab.lib.pagesizes import A5
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont

# Set up Google Sheets API credentials
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('creds.json', scope)
client = gspread.authorize(creds)

# Set up printer name and font
printer_name = "Rollo Printer"
font_path = "arial.ttf"

# Register Arial font
pdfmetrics.registerFont(TTFont('Arial', font_path))

# Open the Google Sheet and select Label and Claims worksheets
sheet_url = "URL"
label_sheet = client.open_by_url(sheet_url).worksheet("Label")
claims_sheet = client.open_by_url(sheet_url).worksheet("Claims")

# Get the number of label copies to print
num_copies = int(label_sheet.acell("F2").value)

# Set up label filename and PDF canvas
label_file = "label.pdf"
label_canvas = canvas.Canvas(label_file, pagesize=A5)

# Write label text to PDF canvas
label_text = label_sheet.get_all_values()
x_pos = 10  # set initial x position
y_pos = 260  # set initial y position
line_height = 14  # set line height for text
label_canvas.setFont('Arial', 8)
for row in label_text:
    for col in row:
        textobject = label_canvas.beginText()
        textobject.setFont('Arial', 8)
        textobject.setTextOrigin(x_pos, y_pos)
        lines = col.split("\n")
        for line in lines:
            textobject.textLine(line)
            y_pos -= line_height
        label_canvas.drawText(textobject)
        x_pos += 90
        y_pos = 260
    x_pos = 10

# Save the label PDF and print to the printer
label_canvas.save()
for i in range(num_copies):
    os.startfile(label_file, 'printto', printer_name, "", 1)

# Set up claims filename and PDF canvas
claims_file = "claims.pdf"
claims_canvas = canvas.Canvas(claims_file, pagesize=A5)

# Write claims text to PDF canvas
claims_text = claims_sheet.get_all_values()
y_pos = 260  # set initial y position
claims_canvas.setFont('Arial', 8)
for row in claims_text:
    textobject = claims_canvas.beginText()
    textobject.setFont('Arial', 8)
    lines = row[0].split("\n")
    for line in lines:
        textobject.textLine(line)
        y_pos -= line_height
    claims_canvas.drawText(textobject)
    y_pos = 260

# Save the claims PDF and print to the printer
claims_canvas.save()
os.startfile(claims_file, 'printto', printer_name, "", 1)

I figured out switching to landscape, but the labels still do not look right. is there a way to designate them as 4x6? or export from Google Sheet differently? The issue is the tabs are not being exporting as a PDF the way they should be formatted for a 4x6 label.

 import os
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import win32print
from reportlab.pdfgen import canvas
from reportlab.lib.pagesizes import landscape, A5
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont

# Set up Google Sheets API credentials
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('creds.json', scope)
client = gspread.authorize(creds)

# Set up printer name and font
printer_name = "Rollo Printer"
font_path = "arial.ttf"

# Register Arial font
pdfmetrics.registerFont(TTFont('Arial', font_path))

# Open the Google Sheet and select Label and Claims worksheets
sheet_url = "https://docs.google.com/spreadsheets/d/1eRO-30eIZamB5sjBp-Mz2QMKCfGxV037MQO8nS7G7AI/edit#gid=1988901382"
label_sheet = client.open_by_url(sheet_url).worksheet("Label")
claims_sheet = client.open_by_url(sheet_url).worksheet("Claims")

# Get the number of label copies to print
num_copies = int(label_sheet.acell("F2").value)

# Set up label filename and PDF canvas
label_file = "label.pdf"
label_canvas = canvas.Canvas(label_file, pagesize=landscape(A5))

# Write label text to PDF canvas
label_text = label_sheet.get_all_values()
x_pos = 10  # set initial x position
y_pos = 260  # set initial y position
line_height = 14  # set line height for text
label_canvas.setFont('Arial', 8)
for row in label_text:
    for col in row:
        textobject = label_canvas.beginText()
        textobject.setFont('Arial', 8)
        textobject.setTextOrigin(x_pos, y_pos)
        lines = col.split("\n")
        for line in lines:
            textobject.textLine(line)
            y_pos -= line_height
        label_canvas.drawText(textobject)
        x_pos += 90
        y_pos = 260
    x_pos = 10

# Save the label PDF and print to the printer
label_canvas.save()
for i in range(num_copies):
    os.startfile(label_file, 'printto', printer_name, "", 1)

# Set up claims filename and PDF canvas
claims_file = "claims.pdf"
claims_canvas = canvas.Canvas(claims_file, pagesize=landscape(A5))

# Write claims text to PDF canvas
claims_text = claims_sheet.get_all_values()
y_pos = 260  # set initial y position
claims_canvas.setFont('Arial', 8)
for row in claims_text:
    textobject = claims_canvas.beginText()
    textobject.setFont('Arial', 8)
    lines = row[0].split("\n")
    for line in lines:
        textobject.textLine(line)
        y_pos -= line_height
    claims_canvas.drawText(textobject)
    y_pos = 260

# Save the claims PDF and print to the printer
claims_canvas.save()
os.startfile(claims_file, 'printto', printer_name, "", 1)

I am thinking the code needs to look something more like this, but I'm receiving a traceback error (also below).

Code:

import os
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import win32print
from reportlab.pdfgen import canvas
from reportlab.lib.pagesizes import landscape, portrait
from reportlab.lib.units import mm
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont

# Set up Google Sheets API credentials
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('creds.json', scope)
client = gspread.authorize(creds)

# Set up printer name and font
printer_name = "Rollo Printer"
font_path = "arial.ttf"

# Register Arial font
pdfmetrics.registerFont(TTFont('Arial', font_path))

# Open the Google Sheet and select Label and Claims worksheets
sheet_url = "https://docs.google.com/spreadsheets/d/1eRO-30eIZamB5sjBp-Mz2QMKCfGxV037MQO8nS7G7AI/edit#gid=1988901382"
label_sheet = client.open_by_url(sheet_url).worksheet("Label")
claims_sheet = client.open_by_url(sheet_url).worksheet("Claims")

# Get the number of label copies to print
num_copies = int(label_sheet.acell("F2").value)

# Set up label filename and PDF canvas
label_file = "label.pdf"
label_canvas = canvas.Canvas(label_file, pagesize=landscape((6*72*mm), (4*72*mm)))

# Write label text to PDF canvas
label_text = label_sheet.get_all_values()
x_pos = 10  # set initial x position
y_pos = 260  # set initial y position
line_height = 20  # set line height for text
label_canvas.setFont('Arial', 18)
for row in label_text:
    for col in row:
        textobject = label_canvas.beginText()
        textobject.setFont('Arial', 18)
        textobject.setTextOrigin(x_pos, y_pos)
        lines = col.split("\n")
        for line in lines:
            textobject.textLine(line)
            y_pos -= line_height
        label_canvas.drawText(textobject)
        x_pos += 145
        y_pos = 260
    x_pos = 10

# Save the label PDF and print to the printer
label_canvas.save()
for i in range(num_copies):
    os.startfile(label_file, 'printto', printer_name, "", 1)

# Set up claims filename and PDF canvas
claims_file = "claims.pdf"
claims_canvas = canvas.Canvas(claims_file, pagesize=landscape((6*72*mm), (4*72*mm)))

# Write claims text to PDF canvas
claims_text = claims_sheet.get_all_values()
y_pos = 260  # set initial y position
claims_canvas.setFont('Arial', 18)
for row in claims_text:
    textobject = claims_canvas.beginText()
    textobject.setFont('Arial', 18)
    lines = row[0].split("\n")
    for line in lines:
        textobject.textLine(line)
        y_pos -= line_height
    claims_canvas.drawText(textobject)
    y_pos = 260

# Save the claims PDF and print to the printer
claims_canvas.save()
os.startfile(claims_file, 'printto', printer_name, "", 1)

Error:

PS C:\Users\amadl\JRPrintFinal> & "C:/Program Files/Python311/python.exe" c:/Users/amadl/JRPrintFinal/printerv7testing.py
Traceback (most recent call last):
  File "c:\Users\amadl\JRPrintFinal\printerv7testing.py", line 34, in <module>
    label_canvas = canvas.Canvas(label_file, pagesize=landscape((6*72*mm), (4*72*mm)))
                                                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
TypeError: landscape() takes 1 positional argument but 2 were given

  [1]: https://i.sstatic.net/UUv4C.png

Answer 1

According to [ReportLab.Docs]: Graphics and Text with pdfgen - More about the Canvas (emphasis is mine):

The pagesize argument is a tuple of two numbers in points (1/72 of an inch).

Pagesizes are specified as tuples, including the ones dynamically constructed (via functions: e.g.: landscape).
You can see such a function like a transformation from one page size (passed as an argument) to another (returned).

This can also be seen in the source code: [ReportLab.HG]: hg-public/reportlab - (v3.5.0) src/reportlab/lib/pagesizes.py (I'm not familiar with Mercurial so I don't know whether the URL is permanent (because of that Id it contains) or (if not,) for how long it will be valid).

So, you must change the faulty line to:

label_canvas = canvas.Canvas(label_file, pagesize=landscape((6 * 72 * mm, 4 * 72 * mm)))

As an additional note, I don't know what the page dimensions should be, but the specified values look kind of wrong to me (especially because of that 72 factor - which represents the DpI - multiplied by the rest).