Documentation › Connecting data
Google Sheets
Connect a Google Sheets workbook to Embiro BI, sign in with Google once, and chart every tab as a dataset.
Embiro BI reads Google Sheets live: when someone changes the sheet, the next chart refresh shows the change. Each connection points at one workbook (one spreadsheet file), and every tab in it becomes a table you can turn into a dataset.
Who can do this: Super Admin, Admin and Analysts. The person who connects the workbook needs access to it in Google.
Before you start
- Keep the data in a tidy table: one header row in row 1, one row per record, no merged cells, no totals or notes under the table. Put summaries on a separate tab.
- Give columns short, plain headers (
order_date,region,amount_ugx). They become column names. - Use real dates and numbers in Google Sheets (Format → Number), not text that looks like them.
1. Build the connection address
A Google Sheets connection is identified by its workbook's URL. Copy the URL from your browser's address bar while the sheet is open and paste it below. The box turns it into the connection address (SQLAlchemy URI) Embiro BI needs.
To build it by hand:
- Cut the URL off right after
/edit. Drop everything after it, such as?usp=sharingor#gid=0. - URL-encode it (
:becomes%3A,/becomes%2F). - Put
gsheets:///in front, with three slashes.
Browser URL: https://docs.google.com/spreadsheets/d/1AbCdEf/edit#gid=0
Address: gsheets:///https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2F1AbCdEf%2Fedit
2. Create the connection
- Click + (top right) → Data → Connect database, and choose Google Sheets.
- Click Connect this database with a SQLAlchemy URI string instead.
- Paste the address from step 1 into SQLAlchemy URI and give the connection a Display name, such as Sales tracker (Google Sheets).
- Open Advanced → Security and tick Impersonate logged in user (see why).
- Click Connect, then Finish.
3. Sign in with Google
The first time someone uses the connection, by picking a tab when creating a dataset or by running a query in SQL Lab, Embiro BI shows Authorization needed with a link.
- Click the link. A Google window opens.
- Choose the Google account that can open the sheet and click Allow. Embiro BI only asks to read your spreadsheets.
- The window closes and the list of tabs appears.
You only do this once. Embiro BI keeps the authorization and renews it in the background.
Your colleagues don't need to sign in
Once an owner or admin has authorized a connection, other members of your organization use that authorization automatically. A member who never signed in with Google still sees the charts. This also means a member's SQL Lab queries on that connection read the sheet with the authorizing person's Google access, so connect workbooks everyone in the organization may see.
4. Create datasets from the tabs
- Open Datasets → + Dataset.
- Choose the Google Sheets connection. Each tab is listed by its name, such as
ordersortargets. - Pick a tab and click Create dataset and create chart.
The dataset's description shows the tab's name, so you can tell datasets apart in the list.
Why "Impersonate logged in user" matters
This setting makes Embiro BI send the Google authorization along with every query. Without it, the list of tabs still appears, but selecting a tab or opening a chart fails and Embiro BI keeps asking you to authorize again: "Authorization needed" comes back no matter how often you sign in.
If that happens, edit the connection (Settings → Database Connections → pencil icon → Advanced → Security), tick Impersonate logged in user, and save.
Querying sheets in SQL Lab
Each tab is a table named by its URL. The easiest way to get it right is to pick the tab in SQL Lab's table list, which writes the query for you:
SELECT region, SUM(amount_ugx) AS revenue
FROM "https://docs.google.com/spreadsheets/d/1AbCdEf/edit?title=orders#gid=0"
GROUP BY region
Limits
- Google limits how often a sheet can be read. Charts are cached for an hour, so a dashboard that many people open doesn't read the sheet each time. Use Force refresh when you need the latest numbers right away.
- Very large sheets (above roughly 50,000 rows) are slow, because the whole tab is read for every query. Move them to a database.
- A Google Sheets connection counts as one data connection, however many tabs and charts use it.
Troubleshooting
- "Authorization needed" keeps coming back
- Tick Impersonate logged in user. See above.
- "Something went wrong while doing OAuth2"
- Sign in with a Google account that can open the sheet. If the sheet belongs to someone else, ask them to share it with you.
- The tab list is empty or shows other workbooks
- Check the address: it must contain the encoded URL right after
gsheets:///, cut at/edit, with nothing after it. - "GSheetsAPI.init() got an unexpected keyword argument"
- The sheet URL was added after a
?in the address. Rebuild it with the box above.
Something unclear or missing? Tell us and we'll improve this page.