I need to create a new Google Sheets with filtered information from an existing Google Sheet

Hi ​@zamshaRR 👋

You’re definitely on the right track with trying a Code by Zapier action. It could output a list of column names where the corresponding field contained a value. Then use the output from that Code step to create a spreadsheet with only those columns. I expect the prompt for the Code step might just need a bit of tweaking.

Here’s the prompt I gave the AI code generator for a Python, not Javascript Code action:

It then created this code:

# Define the mapping of input fields to their corresponding column names
field_to_column = {
    'productNameField': 'Product Name',
    'productIDField': 'ID',
    'costField': 'Cost',
    'colorsField': 'Colors',
    'qtyField': 'Stock Quantity'
}

# Collect column names for fields that have values
columns_with_values = [
    column_name for field, column_name in field_to_column.items() 
    if input_data.get(field)  # Check if the field has a value
]

# Join the column names into a comma-separated string
output = {'columns': ', '.join(columns_with_values)}

The full set up looked like this:

Which correctly output just the Product Name and Cost column names:

Then when I selected that set of column names from the Code step in a Create Spreadsheet action it successfully created a new spreadsheet with only those columns:

The trouble you’d have with is approach is if you’d want to then add values into that spreadsheet the Zap just created—as each field would need to be mapped so that the Zap knows where to put each value, but only the columns that were added to the spreadsheet would be available to map a value to:

So it might be better to create a spreadsheet with all the possible columns, map each field form the Google Sheets trigger and then run a script on Google’s end to automatically hide any columns that don’t contain a value. I found a post on Stack overflow that gives an example of how to do that: Hide columns when cell is empty in google script

Or, perhaps you could use an API Request action or Custom Action for Google Sheets to map the fields to the relevant columns without the need to map them. Both of those types of actions are a bit more advanced but we have some general guides on how to set them up here:

• Set up an API request action
• Create a custom action

Hope that helps to get you pointed in the right direction. If you run into any issues on any of that or have further questions do let us know—happy to assist further! 🙂