Skip to main content

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Learning Resources
All of our best learning resources can be found here.

Featured Articles Show & Tell Zapier's products AI Hub Zapier Tools Hub

Share your knowledge
Give back to the Community by sharing your automation wins and helping to answer questions.

Share your success See unanswered questions User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here. 

# 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.  

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.

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?

@Grove Mountain 

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

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.   

@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

Terms & Conditions