Skip to main content
Question

Printing properly aligned text from Python converted data

# parse-sheet.py
from datetime import datetime, timedelta, date
import json

book_days = 15
now = datetime.now()
date_format = "%m/%d/%Y"
outstring=""" Preamble text
"""

# Set the Header
# Location, Date, Type, Due Date
line_format ="{0:<25}{1:<25}{2:<25}{3:<25}\n" 
outstring+=line_format.format("Location:", "Date:", "Type:", "Due Date:")

data=json.loads(input_data['rows'])
for row in data:
    try:
        event_date=datetime.strptime(row[0], date_format)
        due_date=event_date - timedelta(days=book_days)
        event_out=event_date.strftime(date_format) 
        due_out=due_date.strftime(date_format)
    except:
        pass
    location=row[1]
    type=row[2]
    if event_date > now:
        line=line_format.format(location, 
                                event_out,
                                type, 
                                due_out
                                )
        outstring+=line

output={'output':outstring}

I’m using the code above to grab some data from a spreadsheet, do some basic conversions and determine whether to output the data.  

It doesn’t seem the Python string formatting is working as expected, though I suspect it has something to do with the JSON serialization happening with the output dictionary.   The output doesn’t align properly for the fields.:
 

'Location:'              'Date:'                  'Type:'                  'Due Date:'             
'Raleigh,NC'             '05/21/2024'             'Direct Customer'        '05/06/2024'            
'Cary, NC'               '05/21/2024'             'Field Marketing'        '05/06/2024'            
'Durham, NC'             '05/22/2024'             'Direct Customer'        '05/07/2024'            

 

Is there a better “Zapier” way to be doing this?  Totally fine just passing a bunch of values and letting Zapier format the output.  

Or is there a way to keep consistent string formats in the output string.  

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

5 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @Grove Mountain 

For us to have more context, explain the purpose of the Zap you are trying to configure along with the data input/outputs you are using.

Screenshots are helpful.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
G

The overall purpose of the Zap is to extract a fixed set of columns from a Google Sheet, do some basic comparisons on a date field and format output to be sent in a Slack message to a group of Slack channels.

So

Data Input: Get Many Spreadsheet Rows (Advanced, output as Line Items) in Google Sheets

Data Output: Text with a header row ("Location:", "Date:", "Type:", "Due Date:”) and each row has a left justified output for exactly those fields.  Due Date is calculated based on Date.  Only dates in the future will be displayed (there are past events in the spreadsheet still).  

 

 

All the logic and data transformations are working properly and I am getting output to Slack, it’s just not formatted as I’d expect given my string formatting rules in Python.  The attached screenshot more easily shows the “ragged” output.  I thought maybe “Formatter” would be appropriate here, but it doesn’t seem to have what I’m looking for.   

BTW, the data is ragged in the output from “Run Python in Code by Zapier”, so again, it feels like the translation from a Python formatted text block to the JSON translation is where the problem is in my solution.  Is there something else I can pass that dictionary to that will format the data in a pretty table output?

Troy Tessalone
Userlevel 7
Badge +14

@Grove Mountain 

Unless you have a table to structure the data, the text values have varying lengths so those won’t align properly/consistently.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
G

It sounds like you’re implying I need some kind of table structure to have this format properly.  Does this mean I need to import the data into a Zapier table of some kind to display it with the proper formatting?   If so, what’s the proposed solution and which actions are needed to do this properly?   
 

This also still doesn’t explain why the Python formatting doesn’t work.  The python string formatting is designed to align text properly with varying lengths into fixed length output that’s what this formatting is doing (which is setting each text field as 25 characters left justified):

line_format ="{0:<25}{1:<25}{2:<25}{3:<25}\n"

Again, I’m guessing this is some kind of JSON conversion issue, but it’d be helpful to actually know what’s going on here.   

Troy Tessalone
Userlevel 7
Badge +14

@Grove Mountain 

The output would likely need to be included in a Code block within the Slack message.

There's no built-in support for table markdown, you can use code blocks made with backticks.
This helps you format your table right inside the message by putting it between three sets of backticks. ```

 

https://slack.com/help/articles/202288908-Format-your-messages

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
Terms & Conditions