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

Hey Makers :make:

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
    }
}

1 Like

Wow, great solution @make_expert :muscle:

Thanks so much for sharing it with the community, Iā€™m sure this will be helpful to many Makers :pray: