Designing Dynamic Database PWA

Introduction

After a deep dive into the programming world, I felt it was time to return to the familiar realm of medicine.

  • I paused for a moment to reflect: with my current understanding of app development, how can I address some common needs of pharmacists?
  • In the past, we often relied on no-code platforms like Open As App and Wistify to convert spreadsheets into databases.
  • While convenient, these platforms often came with limitations, specifically imperfect implementations and premium pricing.

That is when the idea struck: what if we built a PWA that directly parses data from a public spreadsheet into a local IndexedDB?



Importing A New Database

Quick Steps

  • Go to Settings.
  • Click Import New Database.
  • Name your database and paste the public Google Sheet URL (make sure it is shared with "Anyone with the link can view").
  • If your spreadsheet includes introductory text or empty rows, you can adjust the header row number accordingly.
  • You may also add an optional informational URL, which will hyperlink from the main page.
  • Click Fetch Column to select the unique ID field that will serve as the keyID in IndexedDB.
  • Then click Import Database.

File and Format Requirements

  • The file must be a Google Sheet, not an Excel file.
    • An error message will appear if the link is invalid or incorrectly formatted.
  • Only the first sheet of the Google Sheet is used to build the database.
  • Ensure there are no missing fields in the header row or the unique ID field ID column.
    • Fields from the header row are used as labels on the detail page.
    • Any missing fields in the header row can cause data to be misaligned during import, leading to incorrect mapping of values across columns. Hence, files with any missing header fields will be rejected when importing.
    • Rows with empty values in the unique ID field will be skipped.

Important Data Preparation Tip

If you are converting from Excel to Google Sheets, make sure to delete all unused columns to the far right of your data.
  • You can do this by selecting the first unused column header, holding Ctrl + Shift, and pressing Right Arrow to highlight the rest, then deleting them.
  • Otherwise, you may encounter a “header contains empty cells” warning during import, even when no empty cells appear visible in the header row.
  • Common causes are stray spaces, invisible characters in header cells or formatting that extends far beyond your actual data.

Security Considerations

  • For your security, only import Google Sheet URLs you trust and ensure they are free from malicious script.
  • Avoid using this app with spreadsheets containing sensitive or private information.



Display and Search Configuration

Once imported, your database will appear under Data Sources, with options to edit, sync or delete.

  • The Edit option lets you quickly update the database name, Google Sheet URL, unique ID field, header row and information URL.
  • To store your database persistently, enable Storage Persistence at the bottom of the Settings page.
  • There is also a Clear All Local Data button, but note that it does not revoke the browser's storage permission once granted.
In the Display and Search:
  • Set the title and subtitle fields for how entries appear in the list view.
  • Select fields to include in the searchable index.
  • Tick fields to hide from the detail page.

NOTE: Data syncing occurs client-side; the Google Visualization API is employed to populate a local IndexedDB, while DOMPurify provides essential sanitization.



Start Searching

Return to Home page and allow the app a moment to load your IndexedDB database.

  • Load time depends on the spreadsheet size and your device’s performance.
  • Once ready, you can begin searching immediately.
Key Features:
  • Use the Sync button (top-right) to manually update your database from the Google Sheet.
  • Switch between databases using the Database icon, next to Sync button.
  • If your sheet contains HTML content, it will render correctly in the app.
  • A built-in calculator is available on the detail page for convenience.
  • The Jump Menu lets you quickly navigate to different field sections.
  • The app works entirely offline once your data is synced.

NOTE: Offline functionality is enabled through the combination of state-based navigation, a service worker and IndexedDB for persistent local storage.



Summary

This project took only a few hours to build, but many more to refine and polish.
  • With focused effort and a solid grasp of the Firebase Studio, designing the app was not too difficult.
Interestingly, though originally designed for drug databases, the app can support any spreadsheet-based database.

Comments

Post a Comment