While working on a custom dashboard, I needed to import data from a Google Sheet. The CSV Data source in SquaredUp is great for reading structured data in .csv format, but in this case, I wanted a live connection to a Google Sheet that wasn’t published as a CSV. Rather than manually exporting and updating the file or publishing it, I used the Web API Data Source to fetch the data.
Here’s a quick guide on how to do it.
Step 1: Set Up Google Sheets API
- Enable API & Create API Key
o Go to: Google Cloud Console
o Create a new project (if needed).
o Navigate to APIs & Services > Credentials and click + Create
Credentials > API key.
o Copy the API key for later use. - Enable Google Sheets API
o In the same project, go to APIs & Services > Library.
o Search for Google Sheets API and enable it.
- Make the Sheet Public
o Open your Google Sheet.
o Click Share and change access to Anyone with the link.
o Copy the Spreadsheet ID from the URL.
Step 2: Configure SquaredUp Web API Data Source
- In SquaredUp, create a Web API data source.
- Use the following as the Base URL (replace spreadsheetId with your actual ID):
https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId - In the Query parameters, add:
Key: your_api_key
Step 3: Create a Tile and Query the Sheet
- Create a new dashboard and open the tile editor.
- Select your new WebAPI data source, then the HTTP Request data stream.
- On the Parameters panel, set the endpoint to
/values/Sheet1?
Or, if you want to specify a range of columns:/values/Sheet1!A:C?
(Replace Sheet1 with your actual sheet name and the column values as needed.)
4. Expand Inner objects.
5. In the Options panel, set Path to data as values
Final Output
You can also add shaping if required. Once configured the tile will display the data from your Google Sheet.
