About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

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

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

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.

Best answer

Consolidate Similar Line Itme

  • 16 January 2023
  • 5 replies
  • 47 views
W

Hey Zapier Community! I was hoping someone can help with my Zap, I’m looking to conslidate similar line items. Happy to donate a coffee for anyone who can help :)

Current Zap:

  • Step 1: Lookup Spreadsheet Rows (output as Line Items) in Google Sheets - it’s pulling a list of drinks (we’re a small events business)
    • Output is: 
      YB Prosecco x 1  Water x 1,Prosecco x 1 Water x 1,Coke No Sugar x 1 Water x 1,Coke No Sugar x 1 Water x 1,YB Prosecco x 1 Water x 1
  • Step 2: Formattter - Utilties - Line-item to Text, seperator is “,”
    • Output is:
      YB Prosecco x 1
      Water x 1,Prosecco x 1
      Water x 1,Coke No Sugar x 1
      Water x 1,Coke No Sugar x 1
      Water x 1,YB Prosecco x 1
      Water x 1
  • Step 3: Formatter - Text - Repalce “,” with [:newline:]
    • Output is: 

      YB Prosecco x 1

      Water x 1

      Prosecco x 1

      Water x 1

      Coke No Sugar x 1

      Water x 1

      Coke No Sugar x 1

      Water x 1

      YB Prosecco x 1

      Water x 

  • Step 4: Email: send output from step 3 to our internal staff.

 

I would love to consolidate the list from step 3 and create a total for each type of drink.

  • Prefered Output:
    YB Prosecco x 2
    Water x 4
    Prosecco x 1
    Coke No Sugar x 2

Is this possible? I’ve spoken to Zapier Support who suggest this could be achieved with custom code - perhaps setups 2 and 3 could be cut out to save on the number of zaps. Happy to donate a coffee for anyone who can help :)

icon

Best answer by GetUWired 17 January 2023, 15:23

View original
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

robschmidt
Userlevel 7
Badge +9

@wandrew 

Here’s the python script from chatgpt for this that you can use to aggerate the result: you will need to change the input and output format, also not sure if the library is supported

from collections import Counter

def aggregate_data(data):
    data_list = data.strip().split('\n')
    data_count = Counter(data_list)
    return '\n'.join([f"{item} x {count}" for item, count in data_count.items()])

data = "YB Prosecco x 1\nWater x 1\nProsecco x 1\nWater x 1\nCoke No Sugar x 1\nWater x 1\nCoke No Sugar x 1\nWater x 1\nYB Prosecco x 1\nWater x 1"
print(aggregate_data(data))

 

Outliant.com | Zapier Expert
W

@robschmidt

thank you for your help, I will give that a try.

Sorry I forgot to mention:

- The is triggered every day at 10 am

- The type of drinks and the number of drinks pulled from Google Sheets will be different every day. We have 20 different types of drink on the menu

 

Thank you for your patience, this is my first time posting

GetUWired
Userlevel 7
Badge +12

Hi @wandrew 

yes, code would work great for this. The below solution uses a Javascript Code block and assumes you’re passing the output from step 3 (separated by new lines) and consolidates exact matches in the name. 

 

let data = inputData.data.split("\n").filter(line => line !=""); //split data at new line character & filter out empty lines

let consolidated_data = {}

data.forEach(line => {
  let temp = line.split(" x "); //split up at the x
  let name = temp[0] //name is first part
  let qty = parseInt(temp[1]) //qty is the second part;
  if (consolidated_data[name]) {
    //we've seen this drink before, sum qty
    consolidated_data[name] += qty; 
  } else {
    // new drink
    consolidated_data[name] = qty;
  }
})

let message = "";
for (const [key, value] of Object.entries(consolidated_data)) {
//all data has been consolidated, rebuild items for message
  message += key + " x " + value + "\n"; 

}

return {message, consolidated_data}

 

W

@GetUWired That worked - that you so much! I’m very impressed. I owe you a coffee!

GetUWired
Userlevel 7
Badge +12

Happy to help! 

Terms & Conditions