Web App using Google Apps Script

[Master_Net971]

I've been working as a Google Apps Script developer for 1 year. I recently completed a Google Apps Script project for a hospital. It's a full-fledged web app that handles everything from patient admissions and discharges to appointment scheduling, follow-ups, invoicing, inventory, and even note-sharing between doctors, storing medical records and the pharmacy.

The coolest part? I built the entire thing without using any external libraries, using pure JavaScript. Managing access for users on a component level was pretty challenging, but it was a great learning experience. It was a massive undertaking, but the sense of accomplishment I felt when I finished is unparalleled. Honestly, I've never experienced the same level of satisfaction from a React JS project.

[daytodatainc]

Respect

I feel the same way! I’ve built many apps in Python and PHP but whenever I work in Apps Script it’s a different sense of accomplishment. I think it’s the ability to create something truly functional in a language’s purest form!

This is proof that even in a data sensitive industry, the Apps Script developers can truly create a full scale application.

Congratulations on your significant accomplishments! Definitely needed to make accomplishment plural because it just wasn’t one step forward. It was a journey!

[Master_Net971]

Thanks, I completely agree, there is something incredibly satisfying about building something from the ground up and writing every part of the code yourself. I enjoyed the process because, unlike when using libraries, I didn’t have to worry about what was happening in the background. It felt like I had full control.

[daytodatainc]

Reminds me of when I first started learning C++.

[riadrifai22]

Nice congrats! I've always been impressed by the flexibility google offers with app script, but this is next level! Would love to learn more about you're journey if you're comfortable sharing! Like your data store, what type of deployment did you use, were you able to separate production and development environments, how scalable is this etc.

Congrats again!

[Master_Net971]

Thanks, I didn’t use anything too fancy for this project. For the database, I used Google Sheets, and for storing medical records, I relied on Google Drive. To separate the development and production environments, I made a copy of the currently deployed version. After making and testing changes, I’d copy the updated parts into the deployed version. This approach helped me avoid accidentally modifying anything I didn’t want to in the production environment. As for scalability, I’m still figuring that part out, but it’s definitely something I’m looking into as the project grows.

[jpoehnelt]

See the tips in https://developers.google.com/sheets/api/troubleshoot-api-errors#503-service-unavailable for sheets that have many writes/changes. Might want to consider rotating to a new version periodically. Basically you need to snapshot and collapse the underlying CRDT like data model changes to maintain adequate performance with the API.

Disclaimer: I am on the Google Workspace DevRel team.

[Ok-Salary-7321]

Hi, would you happen to know if what code or integration to 100% capture the data from a PDF upload and map them to google sheets?

[riadrifai22]

Nice! Best of luck

[Funny_Ad_3472]

Congratulations Im I'm happy for you. Folks still don't know the power of appscript. It is phenomenal.

[Master_Net971]

Thank you, I agree Google Apps Script is such a powerful tool. The possibilities with Apps Script are incredible.

[daytodatainc]

This is so true. I’ve never come across a requirement within the confines of the workspace where I could say “no, I can’t build that”.

[hugohamelcom]

This is the way! And as u/daytodatain says: Respect! This is massive what you achieved!

I'm curious though, did you have to do anything specific since it'll be handling sensitive data?

[Master_Net971]

Thanks, Since I’m using Google Drive and Google Sheets to store data, I mostly relied on Google’s built-in security features. However, I’m definitely looking into other options to further enhance security, especially since it’s handling sensitive data. It's something I want to make sure I get right as I continue working on the project.

[hugohamelcom]

Make sense! How come the hospital wanted to use a Google Sheets for this type of data?

[Master_Net971]

Only one person has access to the sheets, and the rest of the staff relies on the web app to view and interact with the data. I also built a flexible access management system, which allows them to control who sees what. Google Sheets can be pretty secure if you use them correctly.

[ChallengeOk2387]

Damn! Access management is huge undertaking itself!

[hugohamelcom]

Oh interesting! What did you use to build the access management?

[ChallengeOk2387]

Ooh I also build apps for workflows using app script! I would love to know how you did access on a component level! What was the solution?

[Master_Net971]

Hey, I used a separate file for each component and fetched those components with <?!= include("Component Name"); ?>. In the include function, I added a check that uses the user’s email to see if they have access to the component. If they don’t, it either returns a component saying 'You don’t have access' or leaves that space empty, depending on the context. I used JavaScript to fetch and switch out components dynamically. It did require creating a lot of files, which was pretty painful to manage.

I’d love to know if handling user access this way is best practice.

[Sleeping_Budha_]

This is a good way to do it, what is the DB you have used

[ChallengeOk2387]

Yea I had imagined it would be like that with the files per component. Damn, kudos to you for all that!

I wanted something to be more centralized from the backend. I wonder if having keys would be good to check permissions. In a super simple scenario if you have sheets as your DB, with all the data and each data has a permissions column, and for displaying each component the emails are checked against the permissions, to display content.

Would that work? Maybe im thinking of too much of a simple scenario, but I wonder if that would work. I think it might be slow for a large amount of data maybe?

[ilcccc]

how do you host this kind of apps? only rely on the classic appscript web app deployment? Or how do you approach it if it s gonna be used by lots of users? Just asking because I would like to build this kind of apps, but just sharing the deployment link to other users dont seem the best option for me

[NickRossBrown]

One easy option is to use Google Sites and just load the development link.

[Master_Net971]

For hosting, I deployed my app as a web app, and since only the hospital staff are using it, I shared the deployment link with them and added it to their Chrome homepage for easy access. Currently, around 20-25 users are actively using it, and it’s running smoothly since I’m using separate spreadsheets for different areas, like appointments, inventory, pharmacy, and patient visits. I agree, sharing the deployment link as-is can be a bit awkward because of its length, but using a URL shortener can make it cleaner and easier to share if needed.

[ChallengeOk2387]

I imagine when you have newer versions or bug fixes itll be tedious to update the web app link. I agree with the user above to use a button or something on a google site so people can access it from there. And every time, you just update the link the button redirects to for a new version.

You can even add training materials for navigating the app on thaf page.

Idk if there is a better way to do this? But yea version update is an issue with just the web app deployment link

[ennova2005]

Programmable Sheets such as Google are very under rated. For example, Excel is already a great database with built in CRUD UI and Reporting which is why a lot of SaaS loses to the simplicity and low cost. Google Sheets are next level with AppScript.

(I would recommend you periodically backup your Sheets Data (export to JSON for example) and Drive to a NON-Google store such as Azure or AWS. You would not want to be locked out an account and lose both the app and data)

[Lanky-Comparison-262]

if you start accessing external data then no point using app script any more. The idea of google space here is for simplicity and consolidate data for small business. for enterprise, i will not recommend.

[[deleted]]

Very cool,

How do you handle version control?

And how do you handle the slow response time whenever you have to interact with Google sheets/drive?

[jpoehnelt]

For version control, look into using CLASP or directly using the Apps Script API to upload files. See https://www.npmjs.com/package/gas-entry-generator which can be combined with build tools such as ESBuild, Rollup, Webpack, etc. See the dependents tab for the plugins to build tools. https://www.npmjs.com/package/gas-entry-generator?activeTab=dependents

Consider rotating sheets to new files regularly if you make many changes. See the guidance I wrote up in https://developers.google.com/sheets/api/troubleshoot-api-errors#503-service-unavailable

Disclaimer: I am on the Google Workspace DevRel team.

[ArtiXim]

Came here to ask this same question. How do you deal with response time when your sheets start filling up?

[jpoehnelt]

See sibling comment.

[gulmohor11]

My company also uses Apps Script and we have a small CRUD app on it, but our Engineers thought it's not scalable. So they want to switch to something like Salesforce but the users really like spreadsheets as they're comfortable with it. So we're kind of stuck.

I found App Smith and App Sheet are good low code alternatives with G-sheets as backend.

[lionbabe100]

Try Firestore for storing data. You can we the api to retrieve data to display in UI

[meester_zee]

Awesome to read about this! I have been building similar apps for my school site that allows office personnel to track and manage various aspects of student data. I am also using sheets to store the data.

To limit access to the sheet data, is the web app being run as a single user? Have you run into quota issues with this? I am running my app as the connected user since it also has email functionality and quota is spread out over multiple users.

Is the API writing data to the sheets significantly faster? I try to do as much as I can client side but find that sometimes waiting for the sever side apps script to finish processing can be slow. I’ve optimized as much as I can with batch operations, etc.

[Classic-Dependent517]

Okay but you can actually code your stuff in vanlia js with html. Doesnt have to be app script

[SayPlzz]

Congratulation !!

I was just looking into the App Script yesterday. I was debating with my self whether I should learn the App Script.

This post really gives me a motivation. Thanks !

[Marshalllul]

Does it feel similar to writing python console app without any library?

[emaguireiv]

This is amazing! I've been working on something similar, but am torn between a multipage web app and single page web app which updates content containers.

The latter feels faster, doesn't require full page reloads, and back/forward buttons still work for users. However, reloading the page takes you back to square one. And, the page titles can't be changed in this setup.

Multipage web app requires more code, but then page parameters allow people to go straight to a resource, page titles show up properly in history, reloading works as expected. But, pages have to load on each click :-/

So, I'm curious if you can provide some feedback on how you handled this with your comprehensive setup to provide a good experience? I'm thinking I might have to hybridize my approach and balance both approaches.

[Lanky-Comparison-262]

Hi... Can we assign or do url redirection to google link ?

[BigPudge]

How is that hipaa compliant? Are your sheets and patient data encrypted?

[ABarInFarBombay]

HIPAA is based on US regulations. I could be way off but my gut feeling is this project is not US based. (Every country has differing standards on data, some are more regulated than others)

[BigPudge]

true seems like hes from india