Best answer

Combining Rows on Common Column in Google Sheets


I am wanting to send a text message to customers letting them know when parts of their order have arrived at our distribution center. Frequently we’ll get multiple pieces of their total order that arrive, but not their entire order at 1 time. So the text message is to tell them X number of items of your order have arrive and this is what they are.

 

Right now I have it set up to where it will send a text message for each individual item. But I want to combine all items into 1 text message and list each item on a separate line.

 

This is an example of the data in Google Sheets. As you can see the Order # (column K) is the same on each row.

 

 

And here is the text message that sends via Twilio:

 

 

But that obviously would send 8 text messages to the customer. I want to send 1 text message to the customer, with each item listed (so 8 total rows at the bottom of the text message).

 

Hope this makes sense!

icon

Best answer by PaulKortman 2 July 2020, 00:04

Hi @badke This is a perfect use case for Digest by Zapier Essentially you could look at it this way, each row in your spreadsheet could be a value in a digest for that order number. So each time a product comes in a new digest entry is appended.

 

There are a lot of steps to this, so I have a bunch of screenshots below, it might be confusing the first couple of times through but let me know if you get stuck and we’ll see if we can help!

 

In this example I’m using the Order Number as the Digest Title, the Entry would be written in such a way it would look like each row you want to appear in the Text message

 

I would then have a storage by Zapier step that pushes a value onto a “to-notify” list. That value would be the order number, so I have a list of all order numbers that need to have a text message sent.

the (if not already in the list) will prevent duplicate notifications, since this is per order number

 

They have a delay until the time of day you want to send the notifications, I assume 6pm

 

Now here’s where the tricky part comes in, so far this zap is running once for each row/product that arrived. We don’t want to keep all of these going, only one per order number, and that’s why we put the order number into a list that de-duplicated it, so each order number appears only once in that list. We can now remove our current order number from that list and if it’s no longer there this zap will stop, if however it is there this zap will continue to run:

Note the text on the bottom, “ If left blank, Zap will halt on an empty list.” - Also note: This screenshot has “Order Number” as the key but it shoud have the “to-notify” as the key

Be sure to now use the order number that output(ed) from this step in subsequent steps. I was working with dummy data and so my screenshots below do not show which step I’m pulling the Order Number from, but you will want to pull it from this “pop value from list” step from now on.

 

 

And then a Digest step to “Release Existing Digest”

Which will give you the multiple rows for that order example:

The “Value” variable gives you all the rows combined, the “list” gives it in line-item format


Then you can write your sms message

This is where you put the “header” part of the text message and then let the result of the the digest be the rows.

Pheww…. that’s a lot of steps, but it should do exactly what you want!

View original

12 replies

Userlevel 7
Badge +9

Hi @badke This is a perfect use case for Digest by Zapier Essentially you could look at it this way, each row in your spreadsheet could be a value in a digest for that order number. So each time a product comes in a new digest entry is appended.

 

There are a lot of steps to this, so I have a bunch of screenshots below, it might be confusing the first couple of times through but let me know if you get stuck and we’ll see if we can help!

 

In this example I’m using the Order Number as the Digest Title, the Entry would be written in such a way it would look like each row you want to appear in the Text message

 

I would then have a storage by Zapier step that pushes a value onto a “to-notify” list. That value would be the order number, so I have a list of all order numbers that need to have a text message sent.

the (if not already in the list) will prevent duplicate notifications, since this is per order number

 

They have a delay until the time of day you want to send the notifications, I assume 6pm

 

Now here’s where the tricky part comes in, so far this zap is running once for each row/product that arrived. We don’t want to keep all of these going, only one per order number, and that’s why we put the order number into a list that de-duplicated it, so each order number appears only once in that list. We can now remove our current order number from that list and if it’s no longer there this zap will stop, if however it is there this zap will continue to run:

Note the text on the bottom, “ If left blank, Zap will halt on an empty list.” - Also note: This screenshot has “Order Number” as the key but it shoud have the “to-notify” as the key

Be sure to now use the order number that output(ed) from this step in subsequent steps. I was working with dummy data and so my screenshots below do not show which step I’m pulling the Order Number from, but you will want to pull it from this “pop value from list” step from now on.

 

 

And then a Digest step to “Release Existing Digest”

Which will give you the multiple rows for that order example:

The “Value” variable gives you all the rows combined, the “list” gives it in line-item format


Then you can write your sms message

This is where you put the “header” part of the text message and then let the result of the the digest be the rows.

Pheww…. that’s a lot of steps, but it should do exactly what you want!

Thank you @PaulKortman! This is working great. I am having a slight issue though, but I did modify your example and removed the “Delay Until” step. We want these order notifications to go out instantly, not once a day which is why I removed it. So maybe that’s causing my problem, which is…

If an order # exists more than once and is grouping each product into 1 text as I want, but it’s sending an additional text with NO products listed as well. So the customer is getting 2 texts - the first has all of their items in it, the second has no items listed.


Text 1

Paul, great news! Part of your order (Order #12345) has arrived at our Distribution Center. We will let you know as soon as your entire order is ready. Here is what we just received:

1 of 1 SKU 12345 Test Product 1
1 of 1 SKU 12345 Test Product 2


Text 2

Paul, great news! Part of your order (Order #12345) has arrived at our Distribution Center. We will let you know as soon as your entire order is ready. Here is what we just received:

Userlevel 7
Badge +9

Great work tweaking my recommendation!!!

 

To prevent the second text just add a filter step after the “Release Existing Digest” step, and set the filter rule to only continue if the “Value” is not empty. 

 

That should prevent the second “empty” sms from sending. 

@PaulKortman any other specific direction you could add by chance? I added that and I’m now getting quite a few errors and no texts are going out. The “value” you’re referring to is from “Push Value Onto List” is that right?

 

 

Userlevel 7
Badge +9

nope, the “Value” I’m looking for is the output of step 8, the “Release Existing Digest” 

Userlevel 7
Badge +9

If after making that change the texts are still having issues change your filter from testing if it Exists to testing if it contains SKU like so:

 

Ignore the “ID” that was the only field I had to be able to grab this screenshot, in your case it will say “Value” in the first dropdown.

 

@PaulKortman I think there’s something wrong more than just a value being blank. If a Digest has 3 entries for example, it’ll send a message with 2 of the rows, another message with 1 of the rows, and then a final message that is blank.

Userlevel 7
Badge +9

@badke sorry for the delay in responding, I’ve been out of the office for a bit. 

 

So what's the update, have you been able to fix this? My guess is that the filter can stop the empty text but I’m surprised that the digest isn’t working correctly. Is it possible that the order number (or whatever you are using for the digest) is somehow coming in as two different numbers/texts?

Hi,

I’m having the same request like the above and great that this article did help.  Thx.

I manage to solve it but now I’m facing some block.  After getting the result, I would like to email the customer using HTML as Body Type, but it doesn’t appear as I wish to have, ie. display as a Line Item.

Kindly refer to the screenshot below:

 

Looking forward to hearing from you soon.

 

Userlevel 7
Badge +10

Hi @SWMH!

It would be helpful to know more about how you’ve got that HTML set up in the Gmail step. Based on that screenshot it looks like there’s either an issues with the output of the Release Digest step or that you’ve mapped the multiple line items from that step into a single <li> element.

Hi @nicksimard 

Thanks for your reply and heads-up.

Initially I didn’t put any HTML at the Digest by Zapier.

 

Then I insert HTML code into the Digest by Zapier

 

and it working fine.

 

Thanks again. @nicksimard 

 

One more thing.  The Zap runs twice which is one is Success and one is Filtered and it takes up the Task Usage.  Is there anyway to avoid this?  Kindly advise.

 

Userlevel 7
Badge +10

@SWMH Happy to hear that you got it working :)

As for the success vs filter, the best way to avoid using extra tasks is to have the filter as early in a Zap as possible. That may not always match the workflow you’re using (like if you have to run some steps in order to then check the filter, for example).

Is there a way you could have a filter earlier in the Zap, or maybe make two slightly different Zaps somehow, so you don’t use tasks unnecessarily?

Reply