Use less operation when exporting to Google Sheet | Bulk add google sheet rows

[make_expert]

Hey Makers

Problem
When doing any sort of export operations like:

• Shopify to Google sheet export
• CRM customer info to google sheet export
• Or anything similar where you need to export data out of a platform and into google sheet

It requires one operation per row, however, it can be reduced with the following set of modules.

Solution
Copy and paste these code to your scenario and you will be able to send data to google sheet via API as batchupdate.

{
    "subflows": [
        {
            "flow": [
                {
                    "id": 3,
                    "module": "builtin:BasicFeeder",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "array": "{{1.data.data.appCategory.apps.results}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 222,
                            "y": -9
                        },
                        "restore": {
                            "expect": {
                                "array": {
                                    "mode": "edit"
                                }
                            }
                        },
                        "expect": [
                            {
                                "mode": "edit",
                                "name": "array",
                                "spec": [],
                                "type": "array",
                                "label": "Array"
                            }
                        ]
                    }
                },
                {
                    "id": 7,
                    "module": "util:TextAggregator",
                    "version": 1,
                    "parameters": {
                        "feeder": 3,
                        "rowSeparator": "other",
                        "otherRowSeparator": ","
                    },
                    "mapper": {
                        "value": "[\"{{3.legacyId}}\",\"{{3.name}}\",\"{{3.logo.mainUrl}}\",\"{{replace(3.description; \"/,|\"\"|:|\\n/g\"; emptystring)}}\",\"{{3.isPremium}}\",\"{{3.isUpcoming}}\",\"{{3.isBeta}}\",\"{{3.slug}}\",\"{{3.profileUrl}}\", \"{{3.categories[1].title}}\",\"{{3.categories[2].title}}\",\"{{2.i}}\"]"
                    },
                    "metadata": {
                        "designer": {
                            "x": 507,
                            "y": -6
                        },
                        "restore": {
                            "extra": {
                                "feeder": {
                                    "label": "Iterator [3]"
                                }
                            },
                            "parameters": {
                                "rowSeparator": {
                                    "label": "Other"
                                }
                            }
                        },
                        "parameters": [
                            {
                                "name": "rowSeparator",
                                "type": "select",
                                "label": "Row separator",
                                "validate": {
                                    "enum": [
                                        "\n",
                                        "\t",
                                        "other"
                                    ]
                                }
                            },
                            {
                                "name": "otherRowSeparator",
                                "type": "text",
                                "label": "Separator"
                            }
                        ],
                        "expect": [
                            {
                                "name": "value",
                                "type": "text",
                                "label": "Text"
                            }
                        ],
                        "advanced": true
                    }
                },
                {
                    "id": 4,
                    "module": "google-sheets:makeAPICall",
                    "version": 2,
                    "parameters": {
                        "__IMTCONN__": 757031
                    },
                    "mapper": {
                        "qs": [
                            {
                                "key": "insertDataOption",
                                "value": "INSERT_ROWS"
                            },
                            {
                                "key": "valueInputOption",
                                "value": "RAW"
                            },
                            {
                                "key": "includeValuesInResponse",
                                "value": "false"
                            }
                        ],
                        "url": "spreadsheets/GOOGLE_SHEET_ID_HERE/values/Sheet1!A2:O21:append",
                        "body": "{\n  \n  \"range\": \"Sheet1!A2:O21\",\n  \"values\": [{{7.text}}]\n}",
                        "method": "POST",
                        "headers": [
                            {
                                "key": "Content-Type",
                                "value": "application/json"
                            }
                        ]
                    },
                    "metadata": {
                        "designer": {
                            "x": 910,
                            "y": -5
                        },
                        "restore": {
                            "expect": {
                                "qs": {
                                    "mode": "chose",
                                    "items": [
                                        null,
                                        null,
                                        null
                                    ]
                                },
                                "method": {
                                    "mode": "chose",
                                    "label": "POST"
                                },
                                "headers": {
                                    "mode": "chose",
                                    "items": [
                                        null
                                    ]
                                }
                            },
                            "parameters": {
                                "__IMTCONN__": {
                                    "data": {
                                        "scoped": "true",
                                        "connection": "google"
                                    },
                                    "label": "Sabbir Ahmed"
                                }
                            }
                        },
                        "parameters": [
                            {
                                "name": "__IMTCONN__",
                                "type": "account:google",
                                "label": "Connection",
                                "required": true
                            }
                        ],
                        "expect": [
                            {
                                "name": "url",
                                "type": "text",
                                "label": "URL",
                                "required": true
                            },
                            {
                                "name": "method",
                                "type": "select",
                                "label": "Method",
                                "required": true,
                                "validate": {
                                    "enum": [
                                        "GET",
                                        "POST",
                                        "PUT",
                                        "PATCH",
                                        "DELETE"
                                    ]
                                }
                            },
                            {
                                "name": "headers",
                                "spec": [
                                    {
                                        "name": "key",
                                        "type": "text",
                                        "label": "Key"
                                    },
                                    {
                                        "name": "value",
                                        "type": "text",
                                        "label": "Value"
                                    }
                                ],
                                "type": "array",
                                "label": "Headers"
                            },
                            {
                                "name": "qs",
                                "spec": [
                                    {
                                        "name": "key",
                                        "type": "text",
                                        "label": "Key"
                                    },
                                    {
                                        "name": "value",
                                        "type": "text",
                                        "label": "Value"
                                    }
                                ],
                                "type": "array",
                                "label": "Query String"
                            },
                            {
                                "name": "body",
                                "type": "any",
                                "label": "Body"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "metadata": {
        "version": 1
    }
}

[Michaela]

Wow, great solution @make_expert

Thanks so much for sharing it with the community, I’m sure this will be helpful to many Makers

[Joseph_Accountant]

@make_expert - Thank you so much for sharing your code! I was getting stuck because I wasn’t adding " " in my array, your code made it clear and my code is working now.

Also just to share, instead of using ‘append’ API function, I am using ‘Values : BatchUpdate’ - see screenshot below for example. I am not sure the pros/cons of using append vs Batch Update, but either option is so fast compared to using the add a row module for large data sets.

I am curious what the upper limit on what I can send, I think I saw somewhere that batching in 10k rows or less is a good idea, so far I tested up to 3.5k without a problem.

Thank you again for sharing!

PS - for others looking to use the make an api call to google sheet - just know that your data may need to be formatted, @make_expert shows in his code how to use ‘replace’ functionality to remove some of those characters, so even though bulk update or append api calls may be faster and use less operations, it is also more advanced and subject to issues compared to the standard ‘add a row’ module for google sheets.

[AdrianMake]

OK, I’m falling at the first hurdle here.
“Copy and paste these code to your scenario and you will be able to send data to google sheet via API as batchupdate.”
I copied the code supplied into a notepad file and named it googlesheets.json but when I import it, no modules show up, I just see a blank canvas.

What am I missing?

Thanks

-A

[Joseph_Accountant]

@AdrianMake - These posts/messages were before the Make team added the ability to ‘Bulk Add Rows’ natively in the Google Sheets App. So now there is no need to do it manually as discussed in this thread/showcase.

Just note that using the Make built in Bulk Add Rows module does require the use of aggregators and it’s a bit tricky using it the first time, but once you get it working it will ‘click’.

@samliew shared some helpful info in this post:

How to use Bulk Add/Update Rows in Google Sheets?

Welcome to the Make community! Every result (item/record) from a search/match module will output a bundle. To “combine” them into a single structure, you’ll need to use an aggregator of some sort. Aggregators are modules that accumulate multiple bundles into one single bundle. An example of a commonly-used aggregator module is the Array aggregator module. The next popular aggregator is the Text Aggregator which is very flexible and has applies to many use-cases. There are other types of aggre…

To answer your question about how to ‘copy/paste’ the code - you just need to copy the JSON from the first post and in an open Make scenario (one that is in the ‘edit’ mode) - you just ‘paste’ the JSON directly and Make will convert it to the 3 modules.

I hope this helps!