To the untrained eye, Google Sheets is merely a bunch of rows and columns where numerical data is stored. But for those who know their stuff, it can be a thing of beauty. With various integrations in real time, it imparts us with a certain flexibility to use our spreadsheet data with other platforms. Software makers can work on new tools that allow them to sync data between a GSheet and their application for easy data modifications. Polly, our cloud-based data analytics platform has recently integrated GSheets API in an attempt to make the app experience better for our research scientists.
A glimpse at the conundrum
We recently uncovered an issue during our routine checks. Whenever a validation problem was encountered in the input file on Polly, we directed the user to re-upload the data with suitable changes in the rows. They had to rectify these rows and then go through all the initialization parameters again. This came as a really annoying distraction for researchers who just wanted to get this done with and move on to the visualisation of their data. Therefore, we attempted to fix this issue such that minute mistakes in filling the data does not lead to this time-consuming data entry process again.
Well, now you can just sit back and watch in glee as our engineers steady this exquisite vessel, the SS POLLY, using GSheets as an anchor to guide you through the darkest of seas, and do all those scary end-to-end computations on your path to drug discovery and nirvana.
This is because the Elucidata engineers have devised a simple and elegant solution involving Gsheets. This integrates the auto-save and sharing features of Google Sheets with our indigenous validation algorithm and acts between three parties, namely – Frontend, Backend and the User respectively. Polly users can then use this microservice to edit the input sheet uploaded by them in case of any errors during the validation phase.
This process can be differentiated into 3 sections.
- The Initiation
Our microservice comes to life when an error is encountered by the user. Backend then prepares a public GSheet using specific API’s and also downloads the corresponding error files from S3. These files are identified by our validation algorithm, the iteration ID’s for which are shared by Frontend.
Fig 2: If errors are found in any of the uploaded files, the user is intimated.
- The Immune System
We use a batch process to implement a 24 hour expiry period for all the sheets. This is cross-checked every 5 hours to avoid discrepancies. Meanwhile, a CSV file is maintained on S3 with the iteration ID’s and creation time of all the google sheets. This is used to cross-check various google sheets and delete the ones older than 24 hours. These ID’s are sent back to Frontend, where they display this google sheet in an Iframe to the users who can now interact with this sheet.
Fig 3 : A basic layout of the process can be understood through this flowchart.
- The Response
These changes are shared as a patch request to backend, where its sheet ID is used to cross check the ownership of the file. The data is fetched using Google API and the files in S3 are overwritten with the new data provided by the user.
If a sheet is about to expire, it is backed up in S3. Both the sheet and its entry in the .csv file are deleted so that it cannot be touched directly from the AWS console.
Fig 4 : The errors are displayed in a colour coded pattern for easy understanding.
This process essentially skips all the inconveniences the user might face while uploading the files and give them exactly what they came for, without any further hiccups.
You may understand through this that the Google Sheets API plays an important role in making the solution effortless for all the parties involved. It offers developers a way to more deeply integrate the company’s products. This leads to more companies being interested in picking up Google’s productivity suite.
So, go on, experiment with these integrations and uncomplicate the software experience for your clients.
The Polly platform is relatively new. The more experience we have with it, the more possibilities we see. Our innovative advances are being validated daily by our client network around the world, who are adopting it more and more in their daily life. Enthusiasm and usage are growing, and so is our need for engineering talent. If you can think out of the box and are excited to assist in drug discovery by analyzing and interpreting complex biological data sets to derive insights, we are hiring.
Check out https://www.elucidata.io/ to learn more about research & data science at Elucidata.
Banner Image Credit: Patrick Tomasso on Unsplash