Bulk Add/Update Google Sheet rows from HTTP GET request

[JCaruso]

What is your goal?

I need to populate a google spreadsheet once a day to update/add multiple building location information pulled from my CRM database (FMX). I already have another automation that adds new building information once its received throughout the day, however there is a particular bit of information related to the building that does not have a usable API from the provider to date and requires manual intervention with a simple spreadsheet upload. I built a basic scenario and tried everything I could possibly think of but cant get it to work.
I would like to pull existing data with HTTP request, search existing Google Sheet rows and filter to match existing building IDs between the modules. If no existing ID → add new google sheet row with information from the HTTP pull. If existing ID → find and update corresponding row with missing information.

My scenario:
HTTP GET request (pulls all building information) → Google Sheets Search Rows (pre-populated with existing building information) → Router → Path 1 - Filter if ID does not exist in Google Sheet but does exist in HTTP → Bulk Add Rows. Path 2 - Filter if ID does exist in Google Sheet and does exist in HTTP → Buld Update Rows.
I’d paste a screenshot here, but there is no option to do that. See image and blueprint attachments below.

I figured the bulk add/update modules would be used as I may get a dozen or more building updates a day.

What is the problem & what have you tried?

I cannot get the filters to work properly and it will only update the first dataset of information and ignores everything else, even though the bundles exist in the module outputs. I read thru some other threads about using iterators and aggregators, but I cannot get all part of the scenario to work as I’d like. It will either iterate everything in the database (wasting hundreds of credits) or the filters dont work or I cant map the appropriate data from previous modules.

Screenshots (scenario flow, module settings, errors)

[samliew]

You used a Search module. Here’s what you need to do next.

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

[JCaruso]

Hi Samliew

Thanks for the quick reply. I’ve read thru your response and went thru quite a few of the Academy modules but Im still missing something. Here is my updated scenario. Disregard the sleep modules as I added just in case the credits started running away from me and I could stop the process.

So I aggregated both the HTTP and Search Google Sheets module like you recommended. Below are the outputs for each.

Im assuming this is the format I need with 1 bundle and a massive collection within the array? If so, Im have a hard time understanding how to filter beyond the router to determine if the ID from the google sheet matches the id of the HTTP request, then update the corresponding row to add Yes to the Requires finalization cell.

If the ID from the google sheet doesnt match the id from the HTTP request, then add a new row that includes id, name and address from the HTTP then append a Yes to the Requires finalization cell.

I understand that using the aggregator array keys only spits out 1 response, so Im assuming there is something else I need to include that filters thru every collection in both aggregators to match them up and continue down either router path?

[samliew]

Yes, you can probably add a filter here.

If you need further assistance, please provide the following:

Please provide the Output bundles of the first Aggregator; Sheets Search modules [id: 106; 107] from the Scenario History, or by re-running the scenario.

Expand Instructions ◀

Click on the white speech bubbles on the top-right of each module and select “Download output (or input) bundles”.

A. Upload a Text File

Save each bundle contents in a plain text editor as a moduleNumber-output.txt file. Open the file to check if it has not added additional formatting or encoded the plain text.

• You can upload files and images to this forum by clicking on the upload button:
  

  Uploading an attachment here will look like this:
  module1-input.txt (1.2 KB), module1-output.txt (3.4 KB)

B. Insert a Formatted Code Block

If you are unable to upload files on this forum, alternatively you can paste the bundles AND format them correctly with the “Preformatted Text” (code) button in the rich-text editor. Otherwise the forum software will change the content, making them invalid!

_{Formatting IS Important!}

Here are some ways to provide text content in a way that it won’t be modified by the forum.

• Method 1: Type code fence manually —
  Manually type three backticks ``` in a separate line before and after the content, like this,

  ```
  text goes here
  ```
  

• Method 2: Highlight the pasted content, then click the preformatted text/code button —
  

• Method 3: Upload your file elsewhere and share the public link —
  This method is only advised for large files exceeding the forum upload limit.

Providing the Input / Output bundles will allow others to replicate what is going on, especially if there are complex data structures (like nested arrays and collections) and data from external services. This helps us with answering your question with valid examples, like mapping raw variable names (instead of the label/display name).

---

This will allow others to better assist you. Thanks!

— @samliew

[JCaruso]

Thanks for the help samliew.
I tried putting the filter where you suggested but my format is incorrect, and/or I cant seem to wrap my head around proper filtering formulas, as it continues to match one item. I know this is a common issue for newbies, so I appreciate the patience.

I have attached input/output bundles for both module 106 and 107 as requested. To me, the output of module 107 looks odd with the quoted numbers? I appreciate any further assistance you can provide on explaining how this works.

module106-input.txt (15.6 KB)

module107-output.txt (33.5 KB)

module107-input.txt (339 Bytes)

module106-output.txt (19.6 KB)