How to send Offline Event Conversion data to Facebook from Google Sheets for multiple clients using one single automation

[clagrowmedia]

Please help me create a Pabbly automation to send Offline Conversion Event Data to Facebook based on a specific Partner ID and the associated Dataset ID.

1. Fetch the data when a new row is added to Google Sheets in a default tab, (Ex: "First Name = ABC, Last Name = XYZ, Email = [email protected], Price = 10000, Value = 1" in "Master Sales Data" tab)
2. Find the value of the "Partner ID" column. (Ex: "Partner ID" = "CLAG001")
3. Look for a tab that contains the Partner ID in the tab name. (Ex: "Tab Name" = "CLAG001/Client A")
4. Look for the modified data that is added back to the specific tab using the Email. (Ex: "First Name = ABC, Last Name = XYZ, Email = [email protected], Price = 10000, Value = 11000")
5. Look for the Dataset ID associated with that Partner ID in a different sheet. (Ex: "Partner ID = CLAG001, Dataset ID = 123456789" column is "Master Client Data" Sheet)
6. Send the offline conversion event data to Facebook for the specific dataset. (Ex: "First Name = ABC, Last Name = XYZ, Email = [email protected], Price = 10000, Value = 11000" to "Dataset ID = 123456789")

 

[Preeti Paryani]

Hello @clagrowmedia

It seems your use case is feasible.

After looking into your use case it seems you are maintaining two separate Google sheets one "master sales data" and another "master client data".

Please elaborate a bit more on these two steps, as on what basis you would look for the Partner ID and what you refer to by tab name.

2. Find the value of the "Partner ID" column. (Ex: "Partner ID" = "CLAG001")
3. Look for a tab that contains the Partner ID in the tab name. (Ex: "Tab Name" = "CLAG001/Client A")

It would be better if you could provide us with a short screen recording explaining your use case and showing the Google Sheets structures as it would help us to better assist you.

 

[clagrowmedia]

First of all, thanks for your valuable time and response Preeti.

Here's the detailed explanation:

1. Each of my clients has a unique "Partner ID" (E.g., CLAG001=Client A, CLAG002=Client B) that's stored in the "Master Client Data" sheet along with their other details, including the dataset ID.
2. Each client has a unique link that contains their Partner ID. When they click on the link, their partner ID is pre-filled (E.g., domain.com/update?id=CLAG001).
3. The client then fills out the form to update the sales conversion data which needs to get updated in the "Master Sales Data" sheet under the client's specific tab/sheet who has filled out the form as every client has a separate tab/sheet using their Partner ID and Name in the "Master Sales Data" sheet. (Tab/Sheet 1="CLAG001/Client A", Tab/Sheet 2="CLAG002/Client B")
4. Now once the form is submitted, it has sales conversion data filled by the client along with his/her Partner ID through which we can fetch which client filled out the form (E.g., Partner ID=CLAG001)
5. Once we trigger the event based on form submission, we need to fetch the value of the Partner ID in the form response and then search for the tab/sheet in the "Master Sales Data" sheet that matches the Partner ID. (E.g., "Partner ID=CLAG001"="CLAG001/Client A")
6. Then we need to add the form data in a new row in that specific sheet.
7. After that we need to fetch the dataset id of that client from the other sheet "Master Client Data" and then send that data to Facebook as an Offline Conversion Event.

Please lemme know if you need any further details or clarity.

 

[Preeti Paryani]

Hello @clagrowmedia

Thank you for the information provided! We're still missing a few key details to fully configure this in Pabbly Connect, particularly around the trigger event—what specifically should start the workflow? Additionally, steps around locating the correct tab by Partner ID and moving data need further clarification.

Could you please share a short screen recording of the full scenario? This would help us guide you more effectively. Thank you!

 

[clagrowmedia]

Will share the video but can you tell me how to lookup spreadsheet row data and search the worksheet by entering the worksheet name manually instead of selecting the worksheet from dropdown because I was trying to do it but I'm getting an error. It says, invalid parameters and that's the main problem I'm facing. I tried putting the worksheet name in " " but that also didn't work.

 

[Preeti Paryani]

Hello @clagrowmedia

While using the Google sheet lookup action you can select the sheet either by selecting the sheet name from the drop-down or by turning on the map toggle button and mapping the Google sheet ID from which you want to search for the data, if you would enter the Google sheet name after turning on the map toggle button the action will return error.

 

[clagrowmedia]

So can you please help me with the step-by-step process to fetch the sheet ID for a specific sheet dynamically and look up the data?

 

[Preeti Paryani]

Hello @clagrowmedia

Please provide us with the workflow URL in which you are trying to achieve this.

 

[clagrowmedia]

Here's the video explaining all the challenges I'm facing: https://www.loom.com/share/78d3c11bffe14f028769fbbe8292013c?sid=7eeaf44e-87e9-48af-81f6-227248be3b20

And here's the workflow URL: https://connect.pabbly.com/workflow...JBT0IHAQACF4Ac1daU39SUQd9URwHfAFQDzwMUgIqUzI#

 

[Luv]

Hey @clagrowmedia

Based on our understanding from the screen recording you provided, the issue seems to be related to separating the two sheets by name/profile ID. The "Lookup Rows" function searches for values within rows and columns, not across different sheet names.

For your specific case, we recommend using a router to handle the two sheets separately. You can set up a filter based on the Profile ID columns. Please refer to the video below for detailed instructions on how to proceed -

 

[clagrowmedia]

Thanks for sharing the video but even if I use the Router, the problem is I can't select the worksheet dynamically to either add or lookup new row data. My goal is to choose worksheets dynamically not manually.

 

[ArshilAhmad]

You can map the Sheet ID from the 'Lookup Spreadsheet Rows' action step in this manner to achieve your use case.