Connect Google Play, Trustpilot and Glassdoor Reviews to Google Sheets, Power BI or Looker Studio
Pull Google Play, Trustpilot and Glassdoor reviews into Google Sheets with Apps Script, into Power BI with a Python data source, and on to Looker Studio, refreshed daily. Tested code.
Short answer: call FetchLayer’s review endpoints from a small script that writes rows into your tool: an Apps Script in Google Sheets, or a Python data source in Power BI. Looker Studio then reads the Google Sheet. The scripts below pull Google Play, Trustpilot and Glassdoor reviews, skip ones you already have, and refresh daily. A daily Sheets refresh of all three is 3 requests, about $0.18 a month at $1.99 per 1,000, and the first 30 requests are free.
What you need
- A FetchLayer API key (get one, no card).
- The ID of each thing you track:
- Google Play: the package name in the Play URL, such as
com.spotify.music. - Trustpilot: the company’s domain, such as
notion.so. - Glassdoor: the company’s Glassdoor ID. Find it with the
search-companiesendpoint, or pass a Glassdoor company URL. Notion Labs is3304926.
- Google Play: the package name in the Play URL, such as
What each source returns per review:
| Source | Columns we keep |
|---|---|
| Google Play | review ID, date, rating, reviewer, text, app version |
| Trustpilot | review ID, date, rating, title, text, link |
| Glassdoor | review ID, date, overall rating, headline, pros, cons, job title |
Google Sheets: an Apps Script that refreshes itself
- Open your sheet, then Extensions → Apps Script.
- In Project Settings → Script properties, add
FETCHLAYER_API_KEYwith your key, so it isn’t written into the code. - Replace the contents of
Code.gswith:
// Pulls new reviews into one tab per source. Run refreshReviews() once to
// authorize, then add a daily time-driven trigger for it. Each source is one
// request per run; for a first backfill, add pages: 5 to a source's body.
const SOURCES = [
{
tab: 'Google Play',
path: 'playstore/reviews',
body: { appIdOrUrl: 'com.spotify.music', sortBy: 'newest', reviewsPerPage: 100, pages: 1 },
header: ['id', 'date', 'rating', 'reviewer', 'review', 'app version'],
row: (r) => [r.reviewId, r.date, r.rating, r.reviewer, r.body, r.appVersion],
},
{
tab: 'Trustpilot',
path: 'trustpilot/company-reviews',
body: { company: 'notion.so', sort: 'recency' }, // one page of 20, newest first
header: ['id', 'date', 'rating', 'title', 'review', 'url'],
row: (r) => [r.reviewId, r.publishedAt, r.rating, r.title, r.text, r.reviewUrl],
},
{
tab: 'Glassdoor',
path: 'glassdoor/company-reviews',
body: { company: '3304926' }, // Notion Labs' Glassdoor id; one page of 10
header: ['id', 'date', 'rating', 'headline', 'pros', 'cons', 'job title'],
row: (r) => [r.reviewId, r.reviewedAt, r.ratingOverall, r.summary, r.pros, r.cons, r.jobTitle],
},
];
function refreshReviews() {
const key = PropertiesService.getScriptProperties().getProperty('FETCHLAYER_API_KEY');
const book = SpreadsheetApp.getActiveSpreadsheet();
for (const source of SOURCES) {
const res = UrlFetchApp.fetch('https://api.fetchlayer.dev/' + source.path, {
method: 'post',
contentType: 'application/json',
headers: { Authorization: 'Bearer ' + key },
payload: JSON.stringify(source.body),
muteHttpExceptions: true,
});
const data = JSON.parse(res.getContentText());
if (res.getResponseCode() !== 200) {
console.warn(source.tab + ': ' + data.code + ' ' + data.error); // failed calls aren't billed
continue;
}
let sheet = book.getSheetByName(source.tab);
if (!sheet) {
sheet = book.insertSheet(source.tab);
sheet.appendRow(source.header);
}
// Column A holds review ids: skip the ones already in the sheet
const seen = new Set(sheet.getRange('A:A').getValues().flat().map(String));
const rows = data.reviews.filter((r) => !seen.has(String(r.reviewId))).map(source.row);
if (rows.length) {
sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
}
console.log(source.tab + ': ' + rows.length + ' new');
}
}- Run
refreshReviewsonce and approve the permissions. It creates one tab per source with a header row. - Under Triggers, add a time-driven trigger for
refreshReviews, every day.
Each run appends only reviews whose ID isn’t already in column A, so a daily trigger never duplicates rows. We ran this exact Code.gs against the live API on 14 November 2026, with stand-ins for Google’s spreadsheet services:
--- first run
Google Play: 100 new
Trustpilot: 20 new
Glassdoor: 10 new
--- second run
Google Play: 0 new
Trustpilot: 0 new
Glassdoor: 0 newTo track another app or company, copy a block in SOURCES and change its tab and body.
Looker Studio: build the dashboard on the sheet
Looker Studio reads the same Google Sheet, so the Apps Script above is the data pipeline:
- In Looker Studio, Create → Data source → Google Sheets, then pick the spreadsheet and one tab.
- Repeat for the other tabs, or blend them on
date. - Useful first charts: average rating by week, review count by rating, and a table of the latest one- and two-star reviews.
Because the trigger appends new rows each day, the dashboard stays current without touching Looker Studio again.
Power BI: a Python data source
Power BI Desktop can run a Python script as a data source and imports every pandas DataFrame the script defines (Microsoft’s guide). You need Python installed with pandas and requests.
- Get data → Other → Python script → Connect.
- Paste this script, with your key in place of
ss-your-key:
import os
import pandas as pd
import requests
# Power BI imports every DataFrame this script defines as a table
API_KEY = os.environ.get("FETCHLAYER_API_KEY", "ss-your-key")
def reviews(path, body):
res = requests.post(
f"https://api.fetchlayer.dev/{path}",
json=body,
headers={"Authorization": f"Bearer {API_KEY}"},
timeout=300,
)
res.raise_for_status()
return pd.DataFrame(res.json()["reviews"])
def as_dates(frame, column):
# One date type for every source: UTC, without a timezone suffix
frame[column] = pd.to_datetime(frame[column], utc=True, format="mixed").dt.tz_localize(None)
return frame
play = as_dates(
reviews("playstore/reviews", {"appIdOrUrl": "com.spotify.music", "sortBy": "newest", "reviewsPerPage": 200, "pages": 1})
[["reviewId", "date", "rating", "reviewer", "body", "appVersion"]],
"date",
)
trustpilot = as_dates(
reviews("trustpilot/company-reviews", {"company": "notion.so", "sort": "recency", "limit": 100, "pages": 5})
[["reviewId", "publishedAt", "rating", "title", "text"]],
"publishedAt",
)
glassdoor = as_dates(
reviews("glassdoor/company-reviews", {"company": "3304926", "limit": 50, "pages": 5})
[["reviewId", "reviewedAt", "ratingOverall", "summary", "pros", "cons", "jobTitle"]],
"reviewedAt",
)- In the Navigator, tick
play,trustpilotandglassdoor, then Load.
We ran this script on 14 November 2026 and summarized each table it produced:
play 200 rows date: 2026-11-13 to 2026-11-13 avg rating 3.71
trustpilot 100 rows publishedAt: 2025-12-19 to 2026-11-13 avg rating 2.06
glassdoor 50 rows reviewedAt: 2024-09-04 to 2026-11-11 avg rating 3.92Each refresh is 11 requests: one for Google Play, and five pages each for Trustpilot and Glassdoor. Dates come back as one type for all three sources, so you can relate the tables on a calendar table and chart them together. Microsoft’s limits apply: only pandas DataFrames import, and a script that runs longer than 30 minutes times out.
What it costs
| Refresh | Requests a month | Pay-as-you-go |
|---|---|---|
| Google Sheets, daily, one page per source | ~90 | ~$0.18 |
| Power BI, daily, as above | ~330 | ~$0.66 |
| 20 apps and companies, daily, one page each | ~600 | ~$1.19 |
Every request returns a page of reviews and costs one credit: up to 200 Google Play reviews, 20 Trustpilot or 10 Glassdoor reviews. Failed requests aren’t billed. Monthly plans start at $25 for 15,000 requests.
Troubleshooting
- A tab stays empty: check the Apps Script log. A failed request prints its
codeanderror, such asinvalid_inputwith what was wrong, and isn’t billed. - Glassdoor says the company isn’t an ID: Glassdoor needs a company ID or Glassdoor URL, not a name. Look it up with
search-companiesfirst. - Fewer rows than expected: each request returns one page unless you set
pages. For a first backfill, raisepages(andlimitfor Trustpilot and Glassdoor), then go back to one page for the daily refresh. - Power BI only shows recent reviews: each refresh reloads the pages the script asks for and replaces the table, so it never duplicates rows but doesn’t keep older ones. For a growing history, let the Apps Script build the Google Sheet and load that sheet into Power BI.