Parsing Records From Airtable to CSV or Google Sheet With Dynamic Data Structure

[CL_Visual_Developer]

I am working on a custom setup for a client that allows them to back up their Airtable bases regularly to CSV files or Google Sheets in their Google Drive folder. The goal would be to add attachment downloading in the future, but string-only backups work for now. Not too crazy.

The problem is, the client has about 20 bases each with 5-30 tables, and the ideal solution would be one scenario that can iterate through all the tables in a base and create a file for each table.

So far, I have a scenario that uses Airtable’s REST API to get a base’s schema and then get the records with string-only values (no complex variables). It first uses the schema to create a header row of fields. Then, the idea is that it would format the data and bulk add rows to fill out the table under the field headers.

I have the data, but for the life of me I can’t figure out how to format the array in a way that the Google’ Sheets module will accept. It keeps trying to stuff the entire array into one cell as a JSON string and throws a “50000 character limit exceeded for one cell” error.

What can I do to format this array properly so that it can fill out the table as expected? I would prefer a method that conserves operations (ie, not a module that has to run for every cell in every record–some of these tables have thousands of records and hundreds of fields). If a CSV-generation path is better than Google Sheets, that is fine. Thank you!

[Stoyan_Vatov]

Hey there,

are you mapping the entire array in the cell or something? Can you show how the modules are configured?

[CL_Visual_Developer]

Sure! I mapped the array using the “fields” key to isolate just an array where each record is a collection of the fields/values for the record. I have tried a handful of methods to parse to JSON and back, or use and iterator and aggregator, but I kept running into the issue where I needed the data structure to be dynamic (ie different for each table’s different field counts and names) and those required loading/selecting individual variables for a table.

I also tried using the Spreadsheet module’s data structure in the aggregator, but that also tried to put everything in one cell.

[Stoyan_Vatov]

Ohkay there is so much wrong with that. You don’t map the entire array like that. The second approach where you have the aggregator build the data structure is correct, but you have mapped the entire JSON output file of the iterator instead of the specific value.

[samliew]

Welcome to the Make community!

Try selecting a variable that is an array… not a bundle.

Combining Bundles Using Aggregators

Every result/item from some module types (like Trigger / Iterator / List / Search / Match modules) can potentially and likely output more than one bundle. These multiple bundles will individually run subsequent modules once per bundle, which is not optimal in most cases:

• one operation per bundle per module, which could lead to…
• use of multiple credits per bundle per module (some modules use more than one credit)

View example screenshots

Aggregator Example

The “Search Rows” module runs one time, returning 999 results (999 bundles).

• Without Aggregator: the tools module run 999 times (999 operations)
  

  
  
  (and if there are more modules, they run 999 times each)

• With Aggregator: the tools module only runs 1 time (1 operation)
  

  

	Warning:
	This can easily use your entire quota of credits if you are not careful or fail to understand this concept.

To “combine” multiple bundles into a single variable, so that you can process all of the items in a single operation, you’ll need to use an aggregator. Aggregators is a type of module that accumulates bundles and outputs one bundle (unless you are using “Group By”). An example of a commonly-used aggregator module is the Array aggregator module.

You can find out more about some other aggregator modules here:

• Array aggregator
• Numeric, Table, Text aggregators
• JSON aggregator
• CSV aggregator
• Archive (.zip) aggregator
• other app-specific aggregators

Question: Which is the best aggregator do you think you’ll need for your use-case?

Setting the Correct Aggregator Source

You need to set the “Source Module” field of the aggregator to where the bundles are coming from. This is usually an iterator module, but can also be a search/list/repeater module, or even the trigger module!

Mapping a Complex (Collection) Structure Into an Array Field

The Array Aggregator module is very powerful because it allows you to build a new complex array of collections that matches a later module’s array field to map multiple items (collections) to it. Such fields initially may allow you to manually add individual items, but toggle the “Map” switch on, and you can map an array variable (from an Array Aggregator) containing multiple collections.

Simply select the respective “Target structure type” in an Array Aggregator module.

As you can see from the example above, the “Map” toggle on complex array fields are used when you have an array variable (like from an array aggregator).

	Note:
	Other combinations of modules may also allow you to generate an array that matches a future module field’s array structure, like “Aggregate to JSON + Parse JSON”, or “Create JSON + Parse JSON”, but this is an advanced topic.

Question: Are you mapping your array into a field that accepts more than one item/collection?

Example

Here is an example of how your scenario could look like:

^{This is just an example. Your solution may or may not look like this depending on requirements and actual data.}

For more information, see “Mapping with arrays” in the Help Centre. I also suggest going through the Make Academy, which also covers the use of Iterators & Aggregators.

— @samliew
_{P.S.: investing some effort into the tutorials in the Make Academy will save you lots of time and frustration using Make!}

[CL_Visual_Developer]

I appreciate the responses. I suppose my understanding of the recently added “bundle” variable is not accurate. I believe I need all variables in the bundle to be aggregated into the array as a collection, not just a specific variable (hence why I was hoping variable containing the entire bundle would do the trick). If the table schemas did not need to be dynamic, I would normally just create a custom data structure for the collection and manually put each variable in the bundle into a corresponding variable in the data structure, or I would use the standard aggregator and select all of the variables from the bundle–but I obviously cannot do that when multiple tables are in play with different fields. Is what I am asking for even possible in Make at the moment?