Skip to main content
日本語

[Partial Early Release] Create User Profiles by Integrating with Google Sheets as an External Data Source

Updated
[Notice of UI Terminology Changes]
With sequential updates, the term "Member" is being changed to "User," and "Employee ID" is being changed to "User ID" within the help pages. If "Member" or "Employee ID" is still displayed on your screen, please read them as "User" or "User ID" respectively while using this help guide. For more details on the background of this change, please check this announcement.

Overview

In Josys, you can integrate employee information managed in Google Sheets as master data for your User Profiles. This page explains how to set up the Google Sheets integration.

Reference: Integrating with External Systems (External Data Sources) to Create User Profiles

What You Can Achieve with This Integration

Utilizing the Google Sheets integration provides the following benefits:

  1. Automatic Syncing of Employee Information: Employee data managed in Google Sheets can be automatically reflected in Josys's User Profiles.
  2. Centralized Management in Spreadsheet Format: HR administrators can continue managing the employee master data in the familiar spreadsheet format.
  3. Maintaining Existing Operational Flows: You can sync user data to Josys without making major changes to your current operations.
  4. Simple Bulk Updates: Updating data on the spreadsheet will automatically reflect in Josys during the next sync.
  5. Creation of New Departments: If there is a department column in the spreadsheet, new departments will be created in Josys.
⚠️ This feature is not available in the following cases:
・More than 5,000 users are registered in the spreadsheet.
・More than 5,000 users are already registered in Josys's User Profiles.
・You have created Custom Fields in Josys, and some of them are set as required.

Differences from Other External Data Sources (EDS)

Please note the following differences between the Google Sheets integration and other external data sources.

  • For integration with Josys, you must use a spreadsheet in the Josys standard format.
  • Syncing must be performed manually by clicking the sync button. Daily automatic syncing is currently under development.
  • Matching is performed solely by email address to link with existing users in the User Profiles. Users are not matched by User ID.
  • If a user is already linked to another EDS, their information will not be overwritten by the spreadsheet data.
  • Users synced with the spreadsheet will not have an integration icon displayed in the User Profiles.

Prerequisite 1: Preparing the Spreadsheet

For integration with Josys, you must use a spreadsheet in the Josys standard format. You can use it immediately by making a copy of the sample sheet. 
Click here for the sample spreadsheet.

⚠️ Important
Custom Fields are not supported. If there are required fields among your Custom Fields, the integration will not work.

Column Structure of the Standard Format

The spreadsheet must be structured with the following column order (the first row is the header row):

Column Column Name Description Required
A User ID Employee Number / Employee ID Either User ID or Email Address is required.
B Last Name Employee's last name -
C First Name Employee's first name -
D Employment Status Active / Terminated, etc.

Required. Enter one of the following:

Pre-hire / Active / On Leave / Terminated
E User Type Full-time / Contractor, etc.

Enter one of the following:

Executive / Contractor / External / Full-time / Other / Part-time / Outsourced / System
F Email Address Company email address Either User ID or Email Address is required.
G Department Department name If you prepare multiple columns such as Department 1, Department 2, etc., you can register multiple departments for a user.
You can create a department hierarchy by separating names with "/".
There is no limit to the number of departments; you can add as many departments as there are columns.
H Job Title Job title / Position -
I Start Date Date of joining Dates must be entered in YYYY-MM-DD format.
J End Date Date of termination Dates must be entered in YYYY-MM-DD format.
K Note Remarks / Additional information -
L Work Location Code Work location code -
M Username Login username -
N Personal Email Address Personal email address -

Spreadsheet Data Entry Example

Below is an example of data entered into the spreadsheet:

User ID Last Name First Name Employment Status User Type Email Address Department 1 Department 2 Job Title Start Date End Date Note Work Location Code Username Personal Email Address
EMP001 Yamada Taro Active Full-time yamada.taro@example.com Sales   Manager 2020-04-01     3    
EMP002 Suzuki Hanako Active Contractor suzuki.hanako@example.com Development   Engineer 2021-07-01     2    
EMP003 Sato Jiro Terminated Full-time sato.jiro@example.com General Affairs HR/HR Section Section Chief 2018-10-01 2025-12-31   6    
💡 Setting up data validation (dropdowns) can help prevent data entry errors.

💡 Input Format for Dates (Columns I and J)
Dates must be entered in YYYY-MM-DD format. You can automatically display dates in the correct format by setting "Format" -> "Number" -> "Custom date and time" to yyyy-mm-dd in Google Sheets.

Correct Examples:

2020-04-01
2025-12-31

Incorrect Examples:

❌ 2020/04/01
❌ 04-01-2020
❌ April 1, 2020

💡 Importance of Email Address (Column F)
The email address is a critical field used to uniquely identify users in Josys.

Either User ID or Email Address must be entered. Ensure there are no duplicates. Please enter a valid email address format.

💡 Entering Department Information
If you omit the department column(s) from the spreadsheet, columns G, H, and I will be misread as department information instead, which may not be your intention.
If you do not wish to update departments, please prepare a department column but leave its contents blank when integrating.
*Existing departments will not be deleted.

 

Please set the column names for departments using the following regular expression patterns:

Language Pattern Matching Examples Non-Matching Examples
Japanese ^部署\s*\d*$ (Case-insensitive, Unicode) 部署, 部署1, 部署2, 部署 1 (Supports both with and without spaces) 部署名, 所属部署
English ^department(?:\s+\d+)?$ (Case-insensitive) Department, Department 1, Department 2 Department1 (Space is required), Departments

Prerequisite 2: Granting Access Permissions

Please grant view permissions for the spreadsheet to the Google account used for OAuth authentication.

  1. Click "Share" in the top right corner of the spreadsheet screen.
  2. Enter the email address of the Google account you will use to integrate Josys with the spreadsheet.
  3. Set the permission to "Viewer" (Editor permission is also acceptable). Click "Send."
    💡 For security reasons, we recommend granting permissions only to the minimum necessary accounts.

Integration Steps with Josys

1. Checking the Spreadsheet ID

  1. Open the Google Spreadsheet you want to integrate.
  2. Check the URL displayed in your browser's address bar.
    The URL is formatted as follows:
    https://docs.google.com/spreadsheets/d/[Spreadsheet ID]/edit

    Example: https://docs.google.com/spreadsheets/d/1fRdfrOLVYMQOzg3PYrpJFDRsxgAsyJTn_PEyEEGbTPU/edit#gid=0
    The Spreadsheet ID is the part after "d/" and before "/edit".
    In this case: 1fRdfrOLVYMQOzg3PYrpJFDRsxgAsyJTn_PEyEEGbTPU

2. Integrating with Josys

  1. Click "Integrations > External Data Sources" from the left sidebar in Josys.
  2. In the Users tab, click "Connect" for Google Sheets.
  3. A window to enter the required integration details will appear on the right side.
  4. Enter the required information for the integration.
    • Integration Memo: Used as a label for the integration. We recommend entering the email address account name so you can easily identify which account was used for the integration.
      Example: Integrated with hanako.yamada@josys.com / Contractor Management Sheet, etc.
    • Spreadsheet ID: The ID obtained from the URL of the spreadsheet you want to integrate.
      Example: 1fRdfrOLVYMQOzg3PYrpJFDRsxgAsyJTn_PEyEEGbTPU
    • Josys API Key, API Secret: These will be automatically generated when you click the key icon.
  5. Running a Connection Test
    Once all fields are filled out, click the "Sign in with Google Sheets" button. Josys will test whether it can retrieve data from the specified Spreadsheet ID.

3. Syncing Data

  1. To sync data, click the "Sync Now" button on the External Data Sources settings screen. The sync will start, and the progress will be displayed.
  2. Once the sync is complete, the results (number of successful records, error count, etc.) will be displayed.
💡 Unlike other external data sources, automatic syncing does not run every morning, so please perform manual syncs.

Steps for Syncing and Updating User Information

  1. The system compares the Email Address registered in the Google Spreadsheet with the Email Address in Josys's User Profiles.
    If a match is found, all information in Josys is overwritten with the spreadsheet data.
    *The Email Address is recognized as identical regardless of uppercase or lowercase letters.
  2. If no matching Email Address is found, the user is added as a new user.
  3. The system compares the Department registered in the Google Spreadsheet with the Department in "Settings > Departments" in Josys.
    If no department with the same name is found, the department is registered. If the same department name is listed in multiple columns within the same row, that department is skipped, and duplicate departments will not be created.

Best Practices

1. Setting Up Data Validation

To prevent data entry errors, we recommend setting up data validation (dropdowns) for the following columns:

Employment Status (Column D):

  1. Select the entire Column D.
  2. Click "Data" -> "Data validation."
  3. Select "Dropdown" as the criteria and enter the following:
    Pre-hire,Active,On Leave,Terminated,Deleted,Archived,Other
  4. Click "Done."

User Type (Column E):

  1. Select the entire Column E.
  2. Click "Data" -> "Data validation."
  3. Select "Dropdown" as the criteria and enter the following:
    Executive,Contractor,External,Full-time,Other,Part-time,Outsourced,System
  4. Click "Done."

2. Utilizing Conditional Formatting

Set up conditional formatting so you can visually identify any missing required fields.
Example: Highlight the Email Address column in red if it is empty.

  1. Select Column F (Email Address).
  2. Click "Format" -> "Conditional formatting."
  3. Set the format rule to "Format cells if... Is empty."
  4. Set the background color to red.
  5. Click "Done."

3. Protecting the Sheet

Protect the first row to prevent accidental deletion or modification of the header row:

  1. Select the first row.
  2. Right-click and select "Protect range."
  3. Click "Set permissions" to restrict which users can edit it.

4. Utilizing Version History

Regularly check the version history of your Google Spreadsheet so you can restore it if any incorrect changes are made.

  1. Click "File" -> "Version history" -> "See version history."
  2. Restore to a previous version if necessary.

Frequently Asked Questions (FAQ)

  • Q: Can I freely change the column names or order in the spreadsheet?
    A: No, you must maintain the column names and order defined in the Josys standard format. If you change column names or rearrange the column order, data will not sync correctly.
  • Q: Can I integrate multiple spreadsheets?
    A: Currently, only one spreadsheet can be integrated per Josys tenant.
  • Q: Is it possible to support Custom Fields?
    A: To support Custom Fields outside of the Josys standard format, individual script creation and deployment to your tenant are required. If you wish to request this, please contact the Josys Support Team.
  • Q: Can I integrate using a personal Google account instead of a Google Workspace organization account?
    A: Yes, it is possible. However, since you will be handling corporate employee data, we strongly recommend using an organization account for security reasons.
  • Q: Can I create a department hierarchy?
    A: Yes, it is possible. If you want to create a hierarchy, enter the names separated by "/" within a single cell.
    Example: If you enter "Information Systems/IT Management/Planning Team", the departments will be registered in the following hierarchy:
    Information Systems
    └IT Management
    └─Planning Team

Related articles