Should be simple in Zapier.... not for me

Question

I need help with the following setup:

HubSpot (HS) form is filled out: name, email. zip code.
Zapier adds row to an existing Google Sheet 1 (GS1) with 4 columns: name, email, zip, department
Google Sheet 2 (GS2) has two columns : zip, department
Zapier needs to look up zip code in GS1. match zipcode in GS2, lookup department in GS2 and insert it into GS1 department

I need help with Number 4. As soon as I have that I can send info back to HubSpot and carry on with the flow I am making.

Can anyone help ? Thanks in advance.

score 0 · Accepted Answer · answered Mar 13 '17 at 16:58

0

I think we may have a quicker solution at-hand, assuming you prefer one less Zap for the final step.

If you create a second Worksheet in GS1 to house your Zip+Department data, you can use a VLOOKUP to solve your data reconciliation challenge.

Set up your first Zap to copy only Name (Column A), Email(Column B), and ZIP (Column C).
Add your ZIP+Department data to a second sheet.
In column D, use this formula :

=VLOOKUP( C2, $RANGE_OF_ZIPCODES, 2, FALSE )

What this does...

C2 < This is the ZIP you want to look up for the current row.
[$RANGE_OF_ZIPCODES] < Replace this with either a Named Range of Cells
2 < If the ZIP from C2 is found, select the value from the SECOND column in the range
FALSE < Require an exact match of the ZIP value provided in C2

If the ZIP code is found, the matching department will be returned! If it's not found, you will see an error value in Column D.

Think this approach will work for you?

answered Mar 13 '17 at 16:58

Kirk H

441
2
6

Thank you very much! It works well finding the data it should. But can i get this formula to be predefined in the column D? Reason: The Zap adds a new row every time it receives data, and the formula is not pre-set in new rows in the column. Can I set this so it just adds the fomula to all new rows in the column by default? – Birgitte Mar 15 '17 at 08:49
How do I automatically set this formula: =VLOOKUP( C2, partner!A:B, 2, FALSE ) into every new cell in a specific column? (and get it to take into consiceration that C2 is sequential (so C3 and C4....) – Birgitte Mar 16 '17 at 10:00
@Birgitte, If the answer answers the posted question, mark it as accepted and post a new question asking how to automatically extend the formula. Comments shouldn't be used to ask new questions. – Mark Fitzgerald Mar 17 '17 at 09:08

score 0 · Answer 2 · answered May 03 '17 at 17:27

Assuming there are not too many zip code & department relationships, you could avoid steps 3 and 4 altogether and use Code by Zapier to look up the department in between steps 1 and 2. Then when you add the Google Sheets row in step 2 you will already know the department to insert.

Your "data out" for the Google Sheet in Zapier will then have access to department which will be a valid department string, or 'No Match!'

Example:

var hubspotZip = inputData.zip;
output = [];

var departments = {
    "00001": "Department A",
    "00002": "Department B",
    "00003": "Department B",
    "00004": "Department C"
};

if (departments.hasOwnProperty(hubspotZip)) {
    var zipDepartment = departments[hubspotZip];
    output.push({department: zipDepartment});
} else {
    output.push({department: 'No Match!'});
}

Should be simple in Zapier.... not for me

2 Answers2