Iterator vs mapping array to a variable

Getting Started
1

Hi all,

I’m new to Make so will also be browsing these forums for great ideas over the next few days. I’ve been working on a problem all day that I can’t seem to solve and wondered if anyone more experienced has any pointers.

I am trying to update an Airtable base with records from a Google Sheet. Initially they are both identical but then every day the Google sheet changes; sometimes with new rows and sometimes with changes to records. (Basically the Google Sheet is overwritten every day with a fresh version). I want the Airtable base to pick up those changes daily. I’m not ‘watching’ the Google Sheet because it is basically emptied and recreated each day.

Each record has an ID number and we only need to look for and change one variable in Airtable which is “Sold” (Yes/No).

I wanted to avoid iteration because eventually we’ll be looking at 1000s of records and sales and this would burn through operations. I’ll have to accept this though if it isn’t possible any other way.

The way I’ve been trying to do it today is this:

  1. Search airtable base for records marked as sold. Get lead id. Aggregate to array.
    2.Search Google Sheets for records marked as sold. Get lead id. Aggregate to array.

Now you’ll have something like this. 100 IDs in the airtable array and, say, 105 in the GSheets array. Those 5 extra ones are the one we’ll need to update in Airtable. I can iterate through all 105 one by one and check whether they’re marked as Sold in airtable but is there a way I can simply compare the two arrays of lead IDs and find the 5 different ones?

I’ve tried mapping these arrays and creating variables but then I struggle to get to compare them both again.

Very curious as to any other approaches here.

Many thanks,

Ryan

2

Hi Ryan,

You should be able to iterate your Google Sheets array and use a dynamic filter between the iterator and your “update record” module in Airtable. The filter should be using the map() function to return any item in the Airtable array that matches the Google sheet ID, if not you let it go through.

If you do this you should only use 5 operations for that specific bit in your above example.

1 Like
3

airtable google sheets differences
airtable google sheets differences1400×282 39.5 KB

Thanks Loic! That’s a great suggestion.

I’ve also managed to solve it a different way now by following advice from Make support but also mainly this: Integromat Filter Tutorial: How to Bulk Filter Data Records - YouTube (above screenshot shows my way of doing it).

Get records from airtable. Use text aggregator to put record IDs into a string.

Get records from Sheets. Use text aggregator to put record IDs into a string.

Filter records from Sheets to only show the differences, i.e “[airtable text string] does not contain [google sheets array]”. It’s working!

4

Hi @RyanCHM,

That way is even easier :+1: The great Youtube video is from @andyoneil :wave:

2 Likes
5

Glad the video helped!

2 Likes
6

Thanks @andyoneil! I’ve now watched all (literally!) of your videos and they’ve been a wonderful source of inspiration and help. Thank you.

2 Likes