Build Dashboard Using Google Sheets API

Introduction

We currently have candidate login and signup data, but we are not fully prepared to build a dedicated dashboard product to support a client. Developing a new dashboard requires coding effort, particularly integrating UI-based libraries that we are not equipped to handle. Additionally, most existing dashboard services come with additional costs, which we want to avoid for now.

As an alternative, using Google Sheets for a semi-manual dashboard is a great idea, especially since Google offers powerful Apps Script automation. This allows us to keep our data up-to-date without maintaining a full-fledged web-based dashboard.

Solution: Using Google Sheets as a Dashboard

Google provides the necessary infrastructure for handling live data updates within Google Sheets using Apps Script. By leveraging this, we can:

1. Fetch candidate data from our API.
2. Store and visualize data in Google Sheets.
3. Automate updates using triggers to refresh data periodically.

Steps to Implement

1. Get Google File and Sheet ID

Each Google Sheet has a unique file ID that can be accessed from the URL. Individual sheets within the file have unique sheet IDs.

How to Get Google File and Sheet ID

From a Google Sheets URL:
https://docs.google.com/spreadsheets/d/FILE_ID/edit#gid=SHEET_ID

File ID: Found between /d/ and /edit
Sheet ID: Found after gid= in the URL

1. ️ Write an Apps Script to fetch and store API data in Google Sheets.

Here is a basic script to fetch data from an API and update Google Sheets:

function fetchAPIData() {
  var url = "https://api.example.com/data";
  var response = UrlFetchApp.fetch(url);
  var json = JSON.parse(response.getContentText());
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();
  var headers = json.headers;
  sheet.appendRow(headers);
  json.rows.forEach(function(row) {
    sheet.appendRow(row);
  });
}
function fetchAPIData() {
  var url = "https://api.example.com/data";
  var response = UrlFetchApp.fetch(url);
  var json = JSON.parse(response.getContentText());

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();

  var headers = json.headers;
  sheet.appendRow(headers);

  json.rows.forEach(function(row) {
    sheet.appendRow(row);
  });
}
function fetchAPIData() {
  var url = "https://api.example.com/data";
  var response = UrlFetchApp.fetch(url);
  var json = JSON.parse(response.getContentText());

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();

  var headers = json.headers;
  sheet.appendRow(headers);

  json.rows.forEach(function(row) {
    sheet.appendRow(row);
  });
}

Enter fullscreen mode Exit fullscreen mode

1. ⏰ Set a Trigger to refresh the data at regular intervals.

2. Secure the API using an App Key for controlled access.

️ Issues Encountered

1. ngrok is blocked – We were unable to use ngrok for API tunneling.
2. localhost did not work – Direct local API connections failed.
3. Hosting with our existing domain worked – Using an AppRunner custom domain (pointed via GoDaddy) successfully allowed API access.

️ API Structure
A simple API should return structured JSON data.
The API should parse CSV headers and rows dynamically.

Example JSON structure:

{
  "headers": ["Name", "Email", "Signup Date"],
  "rows": [
    ["John Doe", "john@example.com", "2024-02-01"],
    ["Jane Smith", "jane@example.com", "2024-02-02"]
  ]
}
{
  "headers": ["Name", "Email", "Signup Date"],
  "rows": [
    ["John Doe", "john@example.com", "2024-02-01"],
    ["Jane Smith", "jane@example.com", "2024-02-02"]
  ]
}
{
  "headers": ["Name", "Email", "Signup Date"],
  "rows": [
    ["John Doe", "john@example.com", "2024-02-01"],
    ["Jane Smith", "jane@example.com", "2024-02-02"]
  ]
}

Enter fullscreen mode Exit fullscreen mode

Architecture Flow

1. ⏳ Trigger: Google Apps Script trigger initiates the data refresh.
2. API Call: Apps Script fetches data from our API.
3. Security: API is secured using an App Key.
4. Google Sheets Update: Data is parsed and inserted into Google Sheets.

️ Flow Diagram:

⏳ Trigger (Google Apps Script) → API Request → Secure via App Key → Insert Data into Google Sheets

️ Database Architecture Considerations
To optimize performance, we use Materialized Views in our database. This helps:

1. Reduce direct DB load.
2. Speed up query execution.
3. Improve dashboard response times

Conclusion

Using Google Sheets as a semi-automated dashboard is an effective and low-cost solution for handling candidate data. With minimal coding effort, we can keep data fresh and provide a functional dashboard without investing in a full-fledged UI-based system. This approach ensures scalability while keeping infrastructure costs low.

原文链接： Build Dashboard Using Google Sheets API