Easy edit round robins using a Google Sheet

  • 21 September 2019
  • 8 replies
  • 8893 views

Userlevel 7
Badge +10

Round robins are pretty popular with my clients and I'm sure many others - usually used to assign leads evenly across a team when creating deals/contacts in CRMs like Pipedrive, Salesforce, Hubspot etc.

While it's quite easy to create them using a lookup table in your zap, adding/removing/reordering means editing your zap each time which isn't ideal.

I set out to find an easy solution to this - and managed to build a round robin zap that can be managed directly from a Google Sheet.

It's starts with this template:

Round Robin Template Column B and C are obvious, and more could be added as needed.

Column A is the start of the magic - it marks that person's position in the RR using the formula "=ROW()-1"

Screen Shot 2019-09-21 at 18.21.55.pngNotice what happens if you move someone from below the black bar into the RR - the positions remain correct:

Screen Shot 2019-09-21 at 18.23.37.pngYou'll also notice the count changes - this is achieved using another formula: "=COUNT(A2:A6)"

Screen Shot 2019-09-21 at 18.24.18.pngThe extra blank black row acts a buffer - when you move rows up into that section they'll always fall inside the COUNT formula.

Over in our zap, we have leads coming in from Facebook.

Before building, you'll want to create a key/value pair in Zapier Storage to store an integer. Set it to 1 for now.

The second step in your zap should retrieve this:

Screen Shot 2019-09-21 at 18.27.28.png

The next step is to look up that row on the Google Sheet, searching in the 'Position' column:

Screen Shot 2019-09-21 at 18.28.15.pngWith this person getting their lead (in Pipedrive in our example), we now need to increment that Storage value to keep the RR moving on:

Screen Shot 2019-09-21 at 18.29.49.pngNow, of course, with any further steps, this value would keep incrementing, eventually exceeding the number of people on the RR. We need some way to check this and reset the counter where needed.

So first, we want to go to the Google Sheet and retrieve that count number, so we know how many people are on the RR:

Screen Shot 2019-09-21 at 18.31.30.pngWith that done, in comes a Javascript Code step (code below to copy/paste):

Screen Shot 2019-09-21 at 18.32.33.pngWhat this essentially does is compare the Storage value with the count on the Google Sheet. If it's equal or below (i.e no need to reset the counter) the word "go" is returned. If the Storage value has been incremented about the sheet count (i.e the counter needs resetting), we get "stop"

Next, we filter. And why, might you ask, couldn't we have compared the numbers directly in the filter step? Well, because we can't map values in there (P.S @jesse/Zapier HQ...any chance of changing this?):

So instead we filter on what word we got back.

Screen Shot 2019-09-21 at 18.35.33.pngAnd that brings us to the final step in our zap - one that will only run if the counter needs resetting - A Storage "Set Value" step that sets the number back to 1:

Screen Shot 2019-09-21 at 18.37.12.pngDone 😀

I'd love to hear what people think!

var incriment = parseInt(inputData.incrimentStorage, 10);

var total = parseInt(inputData.totalSheet, 10);

if (incriment <= total) {

return {outcome: 'go'};

}

else if (incriment > total) {

return {outcome: 'stop'};

}







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

8 replies

Userlevel 5

Nice! Infusionsoft does this natively (which is the most common CRM we support — probably because we're partners), but I love having this solution handy for when someone is using something different!

Also, it's useful to see your thought process here. Thanks for sharing!


Userlevel 7
Badge +10

Thanks @TheDavidJohnson - I enjoyed writing this up... might do a few more of these going forward.

Good to know RE: Infusion - I'm surprised that most other CRMs don't offer this feature.


Userlevel 7
Badge +11

Thanks for sharing this super template 😀

The Support team will often recommend using the Formatter Lookup table to create round robins. The Lookup table works really well for simple cases where you don't have too many folks in the list and you wont be adding them very frequently, but it's not great for more complex cases.

Google Sheets is a fantastic option for longer lists, or when you know you'll need the same list for more than one Zap. Thanks for sharing your template and process!


Userlevel 6
Badge +6

Hey very nice Template 🙂 As an improvement you could use the Spreadsheet-Style Formula Action and the Modulo Operation to select the record from the Google Sheet. Then you could omit the filter, storage reset and code block.


Userlevel 7
Badge +10

Hey @tweiland - thanks for the feedback. Never done much Modulo - want to talk me through it?


Userlevel 1

Any more information on Modulo? I've used it in round robins previously, but would be curious as to how we can apply it to this method.


Userlevel 6
Badge +6

Sorry for the late answer. Unfortunately I did not see the notification. 

First create a storage. Instead of Get Value, choose Increment Value.

image.png

Next you get the number of people from the spreadsheet as described above.

image.png

Now you can do the magic with the modulo-operator. Create a new Formatter Zap. Select Numbers and use the Spreadsheet-Style Formula as your transform method. In the formula, select the value from the storage as the first parameter and the number of people as the second parameter. (Don't forget +1)

image.png

As a result you get a value to select the persons in the round robin.


Example:

Mod(1,4)+1 = 2

Mod(2,4)+1 = 3

Mod(3,4)+1 = 4

Mod(4,4)+1 = 1

Mod(5,4)+1 = 2

Mod(6,4)+1 = 3

Mod(7,4)+1 = 4

Mod(8,4)+1 = 1

....


Then get the person out of the sheet as described above (use the result of the formatter as the value) and continue working with them. The following steps like the code section are omitted.

image.png

I hope it is clear enough :)


Userlevel 7
Badge +10

Thanks @tweiland - that's really useful!