DEV Community: Saurabh Saha

🚀 Build Dashboard Using Google Sheets API 🚀

Saurabh Saha — Tue, 04 Feb 2025 20:07:37 +0000

🎯 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

🆔 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

🖥️ 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);
  });
}

⏰ Set a Trigger to refresh the data at regular intervals.
🔐 Secure the API using an App Key for controlled access.

⚠️ Issues Encountered

🚫 ngrok is blocked – We were unable to use ngrok for API tunneling.
❌ localhost did not work – Direct local API connections failed.
✅ 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

⏳ Trigger: Google Apps Script trigger initiates the data refresh.
🔗 API Call: Apps Script fetches data from our API.
🔐 Security: API is secured using an App Key.
📥 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. ✅📉💡

Custom Multipart Request in Dart for Video Uploads

Saurabh Saha — Thu, 30 Jan 2025 20:34:01 +0000

When implementing video uploads from Camera in our Flutter web application, we initially used http.MultipartRequest to send the video file. However, we ran into multiple issues with the backend, which was using Express.js + Multer for handling file uploads.

Initial Dart Code (That Didn't Work)

  final request = http.MultipartRequest('POST', uri);
  request.headers.addAll(headers);
  request.files.add(await http.MultipartFile.fromBytes(fileType, 
 bytes, contentType: mediaType));
  final response = await request.send();

NodeJS Multer Code

Our backend was using Express.js with Multer to handle the video uploads:

const upload = multer({
    dest: 'uploads/',
    limits: {
        fileSize: 100 * 1024 * 1024, // ✅ Set max file size to 100MB
        fieldSize: 25 * 1024 * 1024, // ✅ Increase field size limit (for form fields)
    }
});

app.post('/upload', upload.single('videoResume'), (req, res) => {
    if (!req.file) {
        return res.status(400).send('No file uploaded.');
    }

    console.log("File uploaded: ${req.file.originalname}");
    res.status(200).send('File uploaded successfully.');
});

Issues Faced with Multer in Node.js

1️⃣ File Length Too Long Issue
Multer would sometimes reject the request, stating that the file was too long or exceeding expected limits.

Even after increasing fileSize in Multer's configuration, the issue persisted.

2️⃣ req.file Undefined Issue

Multer did not recognize the uploaded file at all.

req.file was undefined, meaning the file wasn't being parsed correctly.

The request headers and structure seemed correct, yet the backend couldn't extract the file.

Solution: Writing a Custom Multipart Request

After multiple failed attempts we decided to manually construct the multipart request, ensuring that:

✅ The file is properly wrapped in a multipart boundary.
✅ The correct Content-Type is used.
✅ The request format is exactly as expected by the backend.

Final Working Dart Code (Manual Multipart Request)

Future<dynamic> multipartRequestWebVideo(
    String url, XFile videoFile, String fileType) async {
  try {
    final bytes = await videoFile.readAsBytes();
    final boundary = '----WebKitFormBoundary7MA4YWxkTrZu0gW'; // Random boundary string
    final uri = Uri.parse(url);

    // Start building the body
    String body = '';
    body += '--$boundary\r\n';
    body += 'Content-Disposition: form-data; name="$fileType"; filename="${videoFile.name}"\r\n';
    body += 'Content-Type: video/webm\r\n\r\n';

    // Convert the body to a Uint8List (for binary data)
    List<int> bodyBytes = []..addAll(utf8.encode(body))..addAll(bytes);

    // Append the closing boundary
    bodyBytes.addAll(utf8.encode('\r\n--$boundary--\r\n'));

    // Get headers from our function (which adds auth tokens, etc.)
    var headers = await addHeaders(isMultiPart: true, isStream: true);

    // Override necessary headers
    headers['Content-Type'] = 'multipart/form-data; boundary=$boundary';
    headers['Content-Length'] = bodyBytes.length.toString();

    // Send the request
    final response = await http.post(
      uri,
      headers: headers,
      body: bodyBytes,
    );
  } catch (e) {
    return {
      'error': e.toString(),
    };
  }
}

Given the limited time we had, we decided to create a custom multipart request from scratch.

Dynamically Generating Interfaces and Validation Schemas in TypeScript with Yup

Saurabh Saha — Fri, 24 Jan 2025 08:03:26 +0000

In a recent project, I encountered a problem where we needed to validate an object with keys dynamically defined by a constant map and enforce that at least one key has a valid value.

The Challenge

We had a MetadataMap object that defined valid keys and their corresponding types:

const MetadataMap = { userId: Number, utmSource: String, utmMedium: String, utmCampaign: String, } as const;

From this map, we needed to:

Dynamically generate a TypeScript interface to enforce type safety.
Create a Yup validation schema that validates the object based on the map.
Ensure at least one key in the object has a valid, non-undefined value.
Avoid hardcoding keys to make the solution maintainable.

But, TypeScript enforces static types at compile time, while Yup handles runtime validation.

Step 1: Generating the Interface
To generate the TypeScript interface from the MetadataMap, we used keyof and mapped types. Here’s how we defined it:

type Metadata = { [K in keyof typeof MetadataMap]: typeof MetadataMap[K] extends NumberConstructor ? number : string; };

This approach ensured that any updates to MetadataMap were automatically reflected in the Metadata interface. For example:

// Resulting Metadata interface: interface Metadata { userId?: number; utmSource?: string; utmMedium?: string; utmCampaign?: string; }

Step 2: Dynamically Generating the Yup Schema

We needed to dynamically create a Yup schema that matched the keys and types in MetadataMap. Using Object.keys and a reducer, we mapped each key to its corresponding Yup validator:

const metadataSchema = Yup.object( Object.keys(MetadataMap).reduce((schema, key) => { const type = MetadataMap[key as keyof typeof MetadataMap]; if (type === Number) { schema[key] = Yup.number().optional(); } else if (type === String) { schema[key] = Yup.string().optional(); } return schema; }, {} as Record<string, any>) );

This method eliminated hardcoding and ensured that changes in MetadataMap were reflected in the schema without manual updates.

Step 3: Adding the “At Least One Key” Rule

The next challenge was ensuring that at least one key in the object had a defined value. We added a .test method to the Yup schema:

metadataSchema.test( "at-least-one-key", "Metadata must have at least one valid key.", (value) => { if (!value || typeof value !== "object") return false; const validKeys = Object.keys(MetadataMap) as (keyof typeof MetadataMap)[]; return validKeys.some((key) => key in value && value[key] !== undefined); } );

This logic:

Ensures the object is valid.
Extracts valid keys dynamically from MetadataMap.
Verifies that at least one key has a non-undefined value.

The Result
Here’s how the final schema behaves:

const exampleMetadata = { userId: undefined, utmSource: "google", extraField: "invalid", // This key is ignored. };
metadataSchema .validate(exampleMetadata) .then(() => console.log("Validation succeeded")) .catch((err) => console.error("Validation failed:", err.errors));

In this example, validation succeeds because utmSource is a valid key with a non-undefined value, even though userId is undefined and extraField is not part of MetadataMap.

Hellow World : Coder , freelancer

Saurabh Saha — Tue, 21 Jan 2025 07:12:10 +0000

Hello world. Seems almost right to say that. I am a coder :) would love to hear stories from other coders how they spend their day ? After 10 years of corporate work u am freelancing for 1 company now. The pay heck if 1/4th of what I earned but it’s satisfactory.