🚀 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);
  });
}

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", "[e-mail]", "2024-02-01"],
    ["Jane Smith", "[e-mail]", "2024-02-02"]
  ]
}

🔄 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. ✅📉💡