Showing results for 
Search instead for 
Did you mean: 

Build a Custom HRIS Integration With GoogleSheet

Rising Star III
Rising Star III

Hi Folks,

Happy New Year! I hope you all had a wonderful holiday and are recharged for the brand new 2024.

Reflecting on 2023, one topic frequently comes to mind: user onboarding – it’s my favourite topic actually, yet a universal pain point, regardless of perspective.

From an SME’s viewpoint, the main considerations are:

  • How can we be prudent when investing in an HRIS?
  • Will our existing HRIS integrate seamlessly with the critical systems, such as the core directory?
  • How do we bridge any gaps between the directory and HRIS?

If you’re facing dilemmas around these points, I’m here to help 😁

A Middle Ground Approach - Using the Spreadsheet

So, I’m expanding on this topic by adding more use cases to the post I wrote last year, as I have learned over the time that it’s not uncommon for the firms to use a spreadsheet for:

  • An interim HRIS solution.
  • A mediator/broker between the HRIS / core directory & various applications.

Not judging here – it probably works effectively. We can certainly achieve the goal of making user onboarding a better experience with the readily available essential data from that sheet.

You might have noticed there is a feature in JumpCloud where we can create a custom API integration for importing users. What if I told you that this could be integrated with the spreadsheet to automate the user onboarding – on an hourly basis?

Here is how. 

Get Started

You will need

  • A workflow automation platform - i.e. (I’ll be using it as a demo),,  Zapier, Worakto, or many more out there.
  • A spreadsheet specialised for onboarding, ideally hosted in Google drive. 
  • Make sure the spreadsheet has the columns like below:
    • Employee Id
    • First Name
    • Last Name
    • Cost Center
    • company
    • location
    • department
    • employeeType
    • jobTitle

[Update - 3rd Feb 2024] You can download and import my workflow blueprint (for here. And the updated CSV template here

Part 1 - On Workflow Engine (

  • Create a webhook for exposing the spreadsheet.shawnsong_0-1705650672335.png


  • Then, let’s protect the webhook with a self-generated “API key”. ( You can sign-up for a higher plan to use a “password” variable to avoid clear-texting.)shawnsong_1-1705650690707.png
  • Connect to your Google Sheet.shawnsong_2-1705650709018.png


  • Make the columns from Google Sheet as the values here.shawnsong_3-1705650738498.png


  • Create a JSON data structure like this:shawnsong_5-1705650788087.png


  • Done.

Part 2 - On JumpCloud

  • Create a custom application for user importing:shawnsong_6-1705650835573.pngshawnsong_7-1705650836097.png
  • Once getting past the wizard, go the app you just created, follow the steps as below screenshots indicate:shawnsong_8-1705650867860.pngshawnsong_9-1705650867841.png






  • Once you fill in the actual employee data on that spreadsheet, you should be able to see this once you click the blue “Preview” button, before you save the whole thing.shawnsong_13-1705650932552.png
  • Done.

Part 3 - Start Importing the Users.

  • Now you should see the “Import Users” section, and you have freedom to choose from manual import or automation on-a-hourly-basis.shawnsong_14-1705651012569.png


  • You will also be able to track the user import events in Directory Insights in 1 click:shawnsong_15-1705651032647.png


    That’s it, thanks for reading this far as usual, please comment below if you have any questions. 

    Happy Friday!




Novitiate II

This is really useful, thanks. Any chance you can share the link to the template please?

Good callout Steven, I somehow missed the links 😂. Post updated with the links. 

Novitiate II

Thanks 😁

I am having some trouble creating the JSON structure. Any chance you could go into a bit more detail? Im not sure how you got the Total Count part.

no worries. 

so the `Total Count` should came from the array module like this:


the array module will essentially pack each "new row" and count them hence that attribute is representing. 

Novitiate II

Thanks for the updated information. 

I am still having trouble with the Total Count. I dont seem to have __IMTAGGLENGHT__ in the Array. I am probably missing something really simple but i have tried a few times.

sorry to hear that Steven, are you able to share a sanitised screenshot similar to my last reply?

Novitiate II

I have created this a clip of the scenario which you can view here

Thanks again for your help, it is much appreciated.

all good, Steven. and thanks for uploading the video, it helps! 

Now I know why 😀 - it seems you have not run the flow yet, hence there is no data been aggregated by the "array aggregator" module, so it can't tell the "total number" of object (basically how many rows of the record in Gsheet). probably you can try put in some placeholder data and run the scenario once to flow in the data? 


You can do a POST from PostMan to the webhook we created in the very first step for testing purposes.


Or, you can run the gsheet module only to get the data - then it will be picked up by the "array aggregator":


let me know how it goes.