Array Aggregator to Google Sheets

[Chandrika_Shenoy]

I am building a scenario where I want to retrieve URLs from a Google sheet for a matching name and update them in airtable.
My Scenario is as given below:

The output bundle of the aggregator looks like this

I have used the following filter in my Google sheets module to match the names in column A of google sheet with the names from the array aggregator

The Google sheet input bundle looks like this

The Google sheet output bundle looks like this

I am aware that this is some issue with the way I have used the filter but not able to figure out what.
It would be great if any suggestions are shared.
Thanks in advance!

[Bjorn.drivn]

Hey @Chandrika_Shenoy , since you are first creating a new array using the Aggregator you can not use the array itself to search for something.
After your first aggregator you need to use an iterator again to iterate over each item. Each item will then be searched for and matched with the filter (also change the filter).

However, looking at your scenario I don’t think you need the first aggregator at all. The Airtable module will output bundles which iterate on it’s own, so just remove the aggregator and use the Airtable data as input for your gSheet.

Hope that helps you!
~Drivn team

[Chandrika_Shenoy]

Hi,
Thanks for your response. So wanted to know something
Airtable has some records and they are not a 1:1 match with the records in the Google sheet. So basically I want the scenario to do this

1. Search airtable records for company names that do not have websites listed against them.
2. Find a record from the Googe sheet that matches with each of those names ,
3. Update the airtable with the urls of those companies back in airtable.

Will this not require the aggregator after airtable? I would need to check each record from the array with the rows in the google sheet to find the match.

[Chandrika_Shenoy]

I was able to get the required result till the iterator. So the airtable is searched for records and placed in an array. Each element of the array is searched against the google sheet values and those that match pass through another aggregator. This array is then iterated. However the last module ‘Update a record’ though gives an output without error, does not actually update the airtable.
Here are the screenshots for the Iterator and Update Airtable record
Iterator input and output (5 records were matched with google sheet) and 5 bundles have been created.

Update airtable record input and output

The formula in the settings of this module

How can I get it to update rows by identifying the name? Do I need to use the map function in the record id section?

[Philippe_Billet]

Hi @Chandrika_Shenoy ,

I need some clarification:
Your problem is “I have some information in Airtable, I want to check if it belongs to a given Google Sheet, if it belongs, I update the corresponding information in Airtable”
That’s it?

For one call to the “Search rows” module, how many lines are expected?

BR,

Philippe

[Chandrika_Shenoy]

@Chandrika_Shenoy

How can I get it to update rows by identifying the name? Do I need to use the map function in the record id section?

I have names with missing urls in the airtable. The google sheet contains names and urls. I want to check if

1. the name from airtable matches with the name in the google sheet
2. If yes, collect the url from google sheet and update in airtable
3. If no, exit.

For one call to Search Rows 10 lines are expected as of now but it could increase as the data increases.

[Philippe_Billet]

Could you add a screenshot of your current scenario?

According to your explanation, after the “Search rows” module, you have an array of bundles (each corresponding row of the Google Sheet).
You should simply use an iterator and then the “Update a Record” module, it will loop by itself (cf. documentation).

[Chandrika_Shenoy]

Sure, here is the scenario

Are you saying that the array aggregator after Google Sheets module can be removed? Should the filter between Google sheets and Iterator remain?

Also, the output bundles in the iterator that contain the urls should now be updated in the airtable by mapping the names. I am a little confused about the usage of iterators.

[Philippe_Billet]

I think that the array aggregator is not useful, the output of the “Search Rows” module is an array (the array of all the rows found in your Google Sheets satisfying your request), then with the iterator and subsequently the call to the “Update a Record” module it should be sufficient.

According to the preceding comment of @Bjorn.drivn , you should also suppress the first array aggregator.

[Chandrika_Shenoy]

So the output from both airtable and google search modules result in separate bundles which is why the aggregators are being placed after each search. So now I have two aggregated arrays (one from airtable that has names and record ids) and the other from Google Sheets (that has names and urls). I want to compare each name from array 1 with the name from array 2 to find a match and only pass the matching names with urls to the iterator. How can I use get/map function to do this?

[Chandrika_Shenoy]

I also tried removing the first aggregator and have duplicated the scenario which
looks like this

So apparently if I use the Google sheets module right after airtable it outputs separate bundles and these bundles somehow do not get aggregated as I see the output of the aggregator, iterator and airtable with 3 operations each. I think this should not be the expected outcome.

What do you suggest?

Alternatively if I were to go by the first scenario that I had shared yesterday, it gives me an array for each search module. So I get an array for airtable and an array for google sheet. Now before iteration, I just need to compare the two arrays. Is there a function that can help me with that?

[Lauren_Moineau]

Hi @Chandrika_Shenoy I don’t know if someone has a more elegant solution but here’e one:

In my scenario, I use “userId” instead of URL

After the iterator, I added a filter to select the records without a userId.

Then I added a Google Sheet Search Rows (Advanced) passing in a query where the name in the sheet matches the name in Airtable, to get the userId cell (you just need to use the column letter in the query, not the header)

Then you just update Airtable with that data

Hope that helps

[Chandrika_Shenoy]

Thank you so much Lauren! This actually worked
Phew! I have literally racked my brain on different get and map functions for this scenario. Thank you once again!

[Lauren_Moineau]

My pleasure Chandrika. Glad it works