Google Sheet and HKConnector – Bulk processing Engine @ Healthkart

Process optimization becomes a necessity when any organization grows and flourishes. This optimization could be in any vertical technical, functional, business or financial and in any form of efficiency, stack, methodologies or roadmaps, etc , but each of these needs to leverage the existing system workflow to scale up and, redefine itself from the intelligence and expandable enough to see the unforeseen future.

We at Healthkart have completed 9 successful years in industry, expanding each day with new ideas, onboarding new users from varied segments pan India. Hence, to pace up with the fast moving business we have been working on many initiatives to have technology as its enabler. 

In this blog post, we will introduce you to our HKConnector, the Bulk Processor

What led to its foundation?

Healthkart, an omni channel health commerce, one of its kind in Indian Industry, listing n no. of features, was facing an human efficiency lag in cataloguing the business requirements owing to the increasing products and addon(s). So there was a dire need to pace it up with smart tech solutions

The Groundwork

With few product-tech interactions we understood that there are many stakeholders involved with following challenges :

  1. any new listing on site,
  2. another hurdle for them is email threads of excel reviews and approval.
  3. New Development with each bulk feature.

To minimize this we needed a common platform for sharing and reporting that was easily adaptable and was easy to use, hence we choose, Google Sheets, the most familiar Cloud excel for any business/marketing personality, giving us out of box feature of sharing and controlling the data.

The HKConnector (aka HKC)

Now we had to leverage all the existing CRUD APIs for these bulk operations, interacting with google sheets and providing the update in a minimal time frame. With all this in mind we designed HKConnector, a Spring Boot based application, leveraging Google Sheet API v4. An application which acts as a black box connector to any API hosted on servers, not just limited to Healthkart Ecosystem. 

All you need is to add the API server base url on HKC & whitelist HKC server IP on this API Server. Next identify the API which is used for crud operations, and the required request parameters. Add this in BulkConfiguration section and click save. Upon successful save, 2 buttons would be generated(currently support with html and js):

  • Bulk Sheet Generate Button : Copy/Paste this on the UI of your server which will be used by the end user to generate the sheet. Along with this an info icon will also be generated on button, which can be leveraged to educate user on how to add data on excel sheet and other guidelines.
  • Process Button : Copy paste this along with the text box, where the user has to enter the google sheet url to be processed.

We leverage the Google authorization for sheet processing. 

Below Network diagram is for the explanation followed for bulk processing with HKC:

SHEET Generation :

  • Request to generate google sheet for bulk upload by clicking BulkSheetGenerationButton, is sent to HKC. [Ref. 1]
  • HKC validates the request [Ref. 6,7] and redirect to check for authorization with google. [Ref. 2]
  • Google sends a request directly to the user for permission grant while in subsequent requests this steps is skipped by google till permission token expiry time.[Ref. 3,4]
  • With grant/deny from user, HKC receives success/failure from google. On Success, HKC internally redirects request to google sheet api v4 to generate sheet which is created in user’s google drive.[Ref. 5,6,7,2,5,8]

Bulk Data Processing :

  • Once user has filled in the data, the sheet URL has to be copy pasted in the text box on user screen(one whose source code was generated at time of button generation). Once done a process button would be visible. On clicking it request sent to HKC [Ref. 1]
  • Next, same steps are followed for authorization as in sheet generation.
  • Once successfully authorised, HKC is able to read the data.
  • Each row data constitutes form param(s) posted to the API on server [Ref Business Logic Server] configured on the HKC portal [Ref. 10, 11]

The code is available on : https://github.com/healthkart/Healthkart_Connector

Suggestions welcome!!

Photo by Charlota Blunarova on Unsplash

Published by Arpit Harkawat

A product engineer at healthkart, solving challenges in ecommerce industry

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: