Multi-location Google Sheets dashboard with Make

What you get

Every morning, your Google Sheets spreadsheet gets a new row per location: the date, the location's name, its review count, its average rating, the number of answered reviews and the rating breakdown. From one day to the next, you follow how each location evolves and compare them, in a Sheets chart or in Looker Studio connected to the spreadsheet.

The scenario uses Make's generic HTTP and Google Sheets modules: there is no Ma Belle Note app in Make, and none is needed. The labels quoted are those of Make's English interface.

What you need

• A Make account and a Google account with Google Sheets.
• A Ma Belle Note API key created with the Read only template: the establishments:read scope is enough.

1. Prepare the spreadsheet

Create a Google Sheets spreadsheet with a sheet named Review tracking, and this header row:

2. Call the API

Create a scenario and add the HTTP › Make a request module:

In the API key keychain:

• Key: Bearer followed by your key, for instance Bearer mbn_live_.... The word Bearer and the space are part of the value.
• API key placement: in the request header.
• API key parameter name: Authorization.

The key thus stays in Make's keychain, not in plain text in the scenario. Run the module once with Run once: Make discovers the structure of the response, and its fields become available to the following modules.

3. Go through the locations

Add the Flow Control › Iterator module, and pick the data array of the HTTP module's response as Array. The following modules then run once per location.

4. Write one row per location

Add the Google Sheets › Add a Row module, connect your Google account, pick the spreadsheet and the Review tracking sheet, then map the columns to the iterator's fields:

5. Schedule the scenario

In the scenario's scheduling settings, pick a daily run, for instance at 6 a.m., then turn the scenario on. Each run makes a single API call, far from the limit of 120 calls per minute, and uses a few operations of your Make plan per location.

Going further

• More than 100 locations: turn on Pagination in the HTTP module, in cursor mode, with data as the items path, nextCursor as the next page cursor path and cursor as the cursor parameter name.
• Reviews received the day before: for each location, a second HTTP module on https://api.mabellenote.com/v1/establishments/<id>/stats?granularity=day returns the last 30 days, day by day, oldest first. The last point of points is the current day and the one before it the previous day, with its review count (reviewCount) and its response rate (responseRate). You then need the stats:read scope, included in the Read only template.
• Response rate: add a computed column to the sheet, answered reviews divided by reviews.

The pagination guide and the reference detail the available fields.