🚀 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:

• 📡 Fetch candidate data from our API.
• 📄 Store and visualize data in Google Sheets.
• 🔄 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
• 2. 🖥️ 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. 3. ⏰ Set a Trigger to refresh the data at regular intervals.
2. 4. 🔐 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"]
  ]
}
        

🔄 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:

• ⚡ Reduce direct DB load.
• 🚀 Speed up query execution.
• 📊 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. ✅📉💡

#excelsheets #automation #javascript #python