Synchroniser les avis Google Maps avec Google Sheets

• •Informations d'identification Google Sheets API

{
  "id": "SyvBl5gLjnsRxFno",
  "meta": {
    "instanceId": "886f6aad18962318bf6e0e61dcc99fe4272655356eb72558c67fde0caf721bc9",
    "templateCredsSetupCompleted": true
  },
  "name": "Sync Google Maps Reviews to Google Sheets for Any Google Maps Query",
  "tags": [],
  "nodes": [
    {
      "id": "0deb227f-1199-4aef-ac8e-a4db53985e77",
      "name": "If - Résultats locaux ou établissements",
      "type": "n8n-nodes-base.if",
      "position": [
        4060,
        1540
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "f6df7b90-96e1-4e80-8539-cf4bbab719db",
              "operator": {
                "type": "object",
                "operation": "exists",
                "singleValue": true
              },
              "leftValue": "={{ $json.place_results }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "867b0ef5-cb51-468e-a4a9-957b2809a589",
      "name": "Diviser les résultats locaux",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        4340,
        1780
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "local_results"
      },
      "typeVersion": 1
    },
    {
      "id": "cdd460e6-2fbc-446f-bae5-2759e31ce68f",
      "name": "Boucler sur les établissements",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        4600,
        1780
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "2dfe9fa5-981c-4842-b100-d8e163961baa",
      "name": "Rechercher Google Maps",
      "type": "n8n-nodes-serpapi.serpApi",
      "position": [
        3820,
        1540
      ],
      "parameters": {
        "q": "italian restaurants in austin tx",
        "operation": "google_maps",
        "requestOptions": {},
        "additionalFields": {
          "json_restrictor": "local_results,place_results"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "72e14880-b142-4afc-bb2d-faa88584c915",
      "name": "Note adhésive",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        4300,
        1780
      ],
      "parameters": {
        "color": 7,
        "width": 500,
        "height": 320,
        "content": "\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n## Loop Over Each Place \n\nSplits out places in the `local_results` array and loops over each."
      },
      "typeVersion": 1
    },
    {
      "id": "7221f8cd-722a-4036-9431-8528268ce6fb",
      "name": "Note adhésive1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3780,
        1140
      ],
      "parameters": {
        "color": 7,
        "width": 420,
        "height": 560,
        "content": "## Find places on Google Maps\n\n**Important** Update the \"Search Google Maps\" node to set your own Google Maps query in the \"Search Query\" field.\n\nThe If node here checks whether the Google Maps results are for a single place or an array of multiple places.\n\nIf there is a single place, the workflow will proceed to get reviews and not attempt to loop for other places.\n\nIf there are multiple places, the workflow will loop to get reviews for each place one-by-one."
      },
      "typeVersion": 1
    },
    {
      "id": "a3554de2-cf47-4364-9e01-48c80cdf09ca",
      "name": "Mettre à jour le nombre d'avis et le jeton de page suivante",
      "type": "n8n-nodes-base.code",
      "position": [
        5600,
        1440
      ],
      "parameters": {
        "jsCode": "// Get the global static data object\nconst data = $getWorkflowStaticData('global');\n\ndata.count += $('Get Reviews').first().json.reviews.length;\n\nif (!!$('Get Reviews').first().json.serpapi_pagination) {\n  data.next_page_token = $('Get Reviews').first().json.serpapi_pagination.next_page_token\n}\n\nreturn data;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "9c1b7cd3-b528-4163-a686-f3ad4dd96373",
      "name": "Définir num pour la pagination",
      "type": "n8n-nodes-base.code",
      "position": [
        6140,
        1240
      ],
      "parameters": {
        "jsCode": "// Get the global static data object\nconst data = $getWorkflowStaticData('global');\n\ndata.num = 20;\n\nreturn data;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "45bfe23c-41f0-4596-8295-9ce4bc42c27e",
      "name": "Aiguiller l'étape suivante",
      "type": "n8n-nodes-base.switch",
      "position": [
        5860,
        1440
      ],
      "parameters": {
        "rules": {
          "values": [
            {
              "outputKey": "Get More Pages",
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "loose"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "d128590f-c232-494d-b379-607736dc31c1",
                    "operator": {
                      "type": "boolean",
                      "operation": "true",
                      "singleValue": true
                    },
                    "leftValue": "={{ !!$json.next_page_token && ($json.review_limit > $json.count + 20)}}",
                    "rightValue": ""
                  }
                ]
              },
              "renameOutput": true
            },
            {
              "outputKey": "End Workflow",
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "loose"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "3d4d7947-ca15-4409-8629-45bc0af39fe8",
                    "operator": {
                      "type": "boolean",
                      "operation": "true",
                      "singleValue": true
                    },
                    "leftValue": "={{ $json.is_single_business && (!$json.next_page_token || $json.review_limit < $json.count + 20 ) }}",
                    "rightValue": ""
                  }
                ]
              },
              "renameOutput": true
            },
            {
              "outputKey": "Get Next Place",
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "loose"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "f6f60b0f-55dc-480f-ad07-77bd617b7334",
                    "operator": {
                      "type": "boolean",
                      "operation": "true",
                      "singleValue": true
                    },
                    "leftValue": "={{ !$json.is_single_business && (!$json.next_page_token || $json.review_limit < $json.count + 20 ) }}",
                    "rightValue": ""
                  }
                ]
              },
              "renameOutput": true
            }
          ]
        },
        "options": {},
        "looseTypeValidation": true
      },
      "typeVersion": 3.2
    },
    {
      "id": "30a54c94-1826-4412-a2cb-5e07b0eeeee7",
      "name": "Ajouter les avis",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        5320,
        1440
      ],
      "parameters": {
        "columns": {
          "value": {
            "rating": "={{ $json.reviews.rating }}",
            "snippet": "={{ $json.reviews.extracted_snippet.original }}",
            "iso_date": "={{ $json.reviews.iso_date }}",
            "place_name": "={{ $('Initialize Vars').first().json.place_name }}"
          },
          "schema": [
            {
              "id": "place_name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "place_name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "iso_date",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "iso_date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "rating",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "rating",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "snippet",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "snippet",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {
          "useAppend": true
        },
        "operation": "append",
        "sheetName": {},
        "documentId": {}
      },
      "typeVersion": 4.5
    },
    {
      "id": "5e1afade-a5ef-4232-a8dc-3afb7a971f28",
      "name": "Diviser les avis",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        5080,
        1440
      ],
      "parameters": {
        "include": "selectedOtherFields",
        "options": {},
        "fieldToSplitOut": "reviews",
        "fieldsToInclude": "place_info.title"
      },
      "typeVersion": 1
    },
    {
      "id": "4e1aedd8-c4e5-44a0-a28a-7752f16cfb0e",
      "name": "Si avis présents",
      "type": "n8n-nodes-base.if",
      "position": [
        4840,
        1460
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "a303ad79-5e85-4782-a328-1eb2b3a42f35",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              },
              "leftValue": "={{ !!$json.reviews || $('Initialize Vars').item.json.is_single_business}}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "d4ab1a92-1492-4a89-8c9b-86edcbd3ea72",
      "name": "Obtenir les avis",
      "type": "n8n-nodes-serpapi.serpApi",
      "position": [
        4580,
        1460
      ],
      "parameters": {
        "place_id": "={{ $json.place_id }}",
        "operation": "google_maps_reviews",
        "requestOptions": {},
        "additionalFields": {
          "num": "={{ $json.num }}",
          "next_page_token": "={{ $json.next_page_token }}"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "5926cd59-f490-4d42-9832-c2f05a3ea4c5",
      "name": "Exécuter le workflow",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        3360,
        1540
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "3eff82f5-e399-40f8-8b46-b383f22287d3",
      "name": "Note adhésive2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        4240,
        1140
      ],
      "parameters": {
        "color": 7,
        "width": 260,
        "height": 560,
        "content": "## Initialize Variables\n\nThis node initializes several variables to faciliate the workflow.\n\nDo not update this code unless you have a particular need and know what you're doing."
      },
      "typeVersion": 1
    },
    {
      "id": "751033b8-2f34-4f57-be82-c91633ed143b",
      "name": "Note adhésive3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        4540,
        1140
      ],
      "parameters": {
        "color": 7,
        "width": 440,
        "height": 480,
        "content": "## Get Reviews\n\nFetches a page of reviews. If it's the first page, `num` and `next_page_token` are null. Subsequent pages will includes these.\n\nIf there are no reviews in the response and there are multiple places, the workflow will loop to the next place. Otherwise, it will proceed forward."
      },
      "typeVersion": 1
    },
    {
      "id": "0ded93ff-5973-42d8-a1d1-0e4132c47ac8",
      "name": "Note adhésive4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        5020,
        1140
      ],
      "parameters": {
        "color": 7,
        "width": 440,
        "height": 480,
        "content": "## Add Reviews to Google Sheet\n\nAppends batch of reviews to Google Sheet. In case the settings get wiped when you add your Google Sheet, here is how everything should be set. They must be 'Expressions'.\n\nplace_name: `{{ $('Initialize Vars').first().json.place_name }}`\niso_date: `{{ $json.reviews.iso_date }}`\nrating: `{{ $json.reviews.rating }}`\nsnippet: `{{ $json.reviews.extracted_snippet.original }}`"
      },
      "typeVersion": 1
    },
    {
      "id": "1ed58029-fec3-496d-bc0b-3444ff1fea29",
      "name": "Note adhésive5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        5500,
        1140
      ],
      "parameters": {
        "color": 7,
        "width": 800,
        "height": 480,
        "content": "## Route Next Step\n\nFirst code node increments `count` of reviews fetched already and sets `next_page_token`.\n\nSwitch node routes as follows:\n* If more pages to fetch->Get More Pages\n* If single place and no more pages to fetch->End Workflow\n* If no more pages to fetch, but more places->Get Next Place\n\nSecond code node sets `num` param to request max reviews per page."
      },
      "typeVersion": 1
    },
    {
      "id": "b413476d-dd5a-46b4-aa80-ebbf1e094b79",
      "name": "Note adhésive6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2660,
        1080
      ],
      "parameters": {
        "width": 660,
        "height": 1100,
        "content": "## Sync Google Maps Reviews to Google Sheets for Any Google Maps Query\n\n### How it works\n\nThis workflow accepts any query you might run on actual Google Maps to search for places. The search happens through [SerpApi's Google Maps API](https://serpapi.com/google-maps-api).\n\nOnce the workflow receives place results from Google Maps, it loops through each place fetching reviews using [SerpApi's Google Maps Reviews API](https://serpapi.com/google-maps-api). By default, the workflow will be limited to fetch up to 50 reviews per place. This can be customized in the 'Set Review Limit' node}.\n\nThe first page of reviews for a place will only return 8 reviews. All subsequent pages will return up to 20 reviews.\n\nThe fetched reviews are sent to a connected Google Sheet.\n\n### How to use\n\n1. Create a free SerpApi account here: https://serpapi.com/\n1. Add SerpApi credentials to n8n. Your SerpApi API key is here: https://serpapi.com/manage-api-key\n1. Connect your Google Sheets accounts to n8n. Help available here: https://n8n.io/integrations/google-sheets/\n1. Create a Google Sheet with these column headers: `name`, `iso_date`, `rating`, `snippet`\n1. Connect your Google Sheet in the 'Append Reviews' Google Sheet node\n1. Update the Search Query in the 'Search Google Maps' node to set your own query\n1. (Optional) Update the review limit from the default 50 in the 'Set Review Limit' node. Set it to a very high number (e.g. 50000) to get all possible reviews.\n1. Hit 'Test Workflow' to manually trigger the workflow.\n\n### Limitations\n\nCan only retrieve the top 20 results from Google Maps. It won't paginate to get more results. The workflow could be extended to support Google Maps Pagination.\n\n### Warning\n\nEach request to SerpApi consumes 1 search credit. Be mindful of how many search credits your account has before requesting more reviews than your account supports.\n\nAs an example, if a Google Maps query returns 20 results and you fetch the default limit of 50 reviews per page, this will use up to 61 SerpApi search credits.\n\n### Documentation\n\n[Google Maps API](https://serpapi.com/google-maps-api)\n[Google Maps Reviews API](https://serpapi.com/google-maps-reviews-api)\n[SerpApi n8n Node Intro Guide](https://serpapi.com/blog/boost-your-n8n-workflows-with-serpapis-verified-node/)"
      },
      "typeVersion": 1
    },
    {
      "id": "a1de7c43-2b8d-48bb-8554-30d76dfe3207",
      "name": "Définir la limite d'avis",
      "type": "n8n-nodes-base.set",
      "position": [
        3580,
        1540
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "b8a2d158-d09e-41e4-9ce0-55096e55a840",
              "name": "review_limit",
              "type": "number",
              "value": 50
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "e13ab771-0cce-427c-826b-2e8a83bbfd9a",
      "name": "Initialiser les variables",
      "type": "n8n-nodes-base.code",
      "position": [
        4320,
        1460
      ],
      "parameters": {
        "jsCode": "const data = $getWorkflowStaticData('global');\n\ndata.review_limit = $('Set Review Limit').first().json.review_limit;\ndata.place_id = $input.first().json.place_id ||$input.first().json.place_results.place_id;\ndata.place_name = $input.first().json.title || $input.first().json.place_results.title;\ndata.is_single_business = !!$input.first().json.place_results ? true : false;\ndata.num = null;\ndata.count = 0;\ndata.next_page_token = null;\n\nreturn data;"
      },
      "typeVersion": 2
    },
    {
      "id": "7291f010-f211-474c-a5be-c7b2f55f9486",
      "name": "Note adhésive7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3500,
        1140
      ],
      "parameters": {
        "color": 7,
        "width": 260,
        "height": 560,
        "content": "## Update Review Limit\n\nYou can update the review limit here to customize the maximum reviews to fetch per place. Default limit is 50 reviews per place."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "0da2918a-3686-47b2-8468-c67375215a78",
  "connections": {
    "d4ab1a92-1492-4a89-8c9b-86edcbd3ea72": {
      "main": [
        [
          {
            "node": "4e1aedd8-c4e5-44a0-a28a-7752f16cfb0e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5e1afade-a5ef-4232-a8dc-3afb7a971f28": {
      "main": [
        [
          {
            "node": "30a54c94-1826-4412-a2cb-5e07b0eeeee7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "30a54c94-1826-4412-a2cb-5e07b0eeeee7": {
      "main": [
        [
          {
            "node": "a3554de2-cf47-4364-9e01-48c80cdf09ca",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e13ab771-0cce-427c-826b-2e8a83bbfd9a": {
      "main": [
        [
          {
            "node": "d4ab1a92-1492-4a89-8c9b-86edcbd3ea72",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "45bfe23c-41f0-4596-8295-9ce4bc42c27e": {
      "main": [
        [
          {
            "node": "9c1b7cd3-b528-4163-a686-f3ad4dd96373",
            "type": "main",
            "index": 0
          }
        ],
        [],
        [
          {
            "node": "cdd460e6-2fbc-446f-bae5-2759e31ce68f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5926cd59-f490-4d42-9832-c2f05a3ea4c5": {
      "main": [
        [
          {
            "node": "a1de7c43-2b8d-48bb-8554-30d76dfe3207",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "cdd460e6-2fbc-446f-bae5-2759e31ce68f": {
      "main": [
        [],
        [
          {
            "node": "e13ab771-0cce-427c-826b-2e8a83bbfd9a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a1de7c43-2b8d-48bb-8554-30d76dfe3207": {
      "main": [
        [
          {
            "node": "2dfe9fa5-981c-4842-b100-d8e163961baa",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "4e1aedd8-c4e5-44a0-a28a-7752f16cfb0e": {
      "main": [
        [
          {
            "node": "5e1afade-a5ef-4232-a8dc-3afb7a971f28",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "cdd460e6-2fbc-446f-bae5-2759e31ce68f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2dfe9fa5-981c-4842-b100-d8e163961baa": {
      "main": [
        [
          {
            "node": "0deb227f-1199-4aef-ac8e-a4db53985e77",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "867b0ef5-cb51-468e-a4a9-957b2809a589": {
      "main": [
        [
          {
            "node": "cdd460e6-2fbc-446f-bae5-2759e31ce68f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "9c1b7cd3-b528-4163-a686-f3ad4dd96373": {
      "main": [
        [
          {
            "node": "d4ab1a92-1492-4a89-8c9b-86edcbd3ea72",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "0deb227f-1199-4aef-ac8e-a4db53985e77": {
      "main": [
        [
          {
            "node": "e13ab771-0cce-427c-826b-2e8a83bbfd9a",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "867b0ef5-cb51-468e-a4a9-957b2809a589",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a3554de2-cf47-4364-9e01-48c80cdf09ca": {
      "main": [
        [
          {
            "node": "45bfe23c-41f0-4596-8295-9ce4bc42c27e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}