Importing Org Data with Google Sheets
You can quickly ingest your organization data into Faros via a simple spreadsheet. This spreadsheet can be populated from an existing system that stores organizational data. To get data into Faros from an existing org & teams data dump you must:
Create a Google Sheets Spreadsheet
Your Google sheet should have two tabs, Org and Teams The Org tab contains data for all of your employees, while the Teams contains your organizational structure. You can create yours by copying the example sheet from here.
Tip: If you don't have your org hierarchy data available, you can just name the teams by their lead, e.g
Johnny's Org.
Org
Not all columns are required, and if you decide to include them you do not need to include a value for every row in that column. For example if you don't know a certain employees email you can leave that cell blank. You'll notice the spreadsheet above is missing certain cells.
Your org sheet should have the following columns:
| Column Name | Required | Description |
|---|---|---|
| ID | yes | A unique ID for your employee |
| Name | yes | Employee's full name |
| no | Employee's email | |
| Team ID | no | Employee's team ID(s) as defined in the Teams tab. You can supply multiple using a comma separated array |
| Level | no | A numeric level for the employee |
| Start Date | no | Date the employee began. This is used to calculate employee tenure |
| End Date | no | Date the employee left the company. |
| Inactive | no | A boolean field (true or false) to indicate if the Employee is no longer at the company. If no value is provided this value will default to false. |
| Ignored | no | A boolean field (true or false) to indicate if the Employee should be ignored from importing. If no value is provided this value will default to false. |
| Location | no | Location associated with this employee, e.g 501 2nd St Suite 100, San Francisco, CA 94107 |
| Location Name | no | Name of the location associated with this employee, e.g HQ, Remote, Home |
| Title | no | Title of the employee, e.g Senior Developer, Executive Director |
| Type | no | Employment type of the employee. Can be one of the Faros enum values (FullTime, PartTime, Freelance, Contractor, Intern) or any custom string |
| Role | no | Role of the employee, e.g.Engineer, Product Manager, Designer |
Link Identities
You also have the option to include account mappings in your org spreadsheet. These mappings are what connect an employee to their accounts in your various systems. If you are unsure what ID is used in a system you can inspect your data in Faros. If you don't have this information in bulk you can enter these mappings through the UI. If you need to specify multiple per employee, you can provide them as a comma separated array.
Note: Users can only be linked to one employee
Your feed will fail if you have the same user (e.g. Jira user) listed for two different employees. Please check that each user is only listed once.
| Column Name | Required | Description |
|---|---|---|
| VCS User | no | UID of the vcs_User (Version Control System e.g. GitHub) associated with this employee |
| VCS GitHub User | no | UID of the GitHub vcs_User associated with this employee |
| TMS User | no | UID of the tms_User (Ticket Management System e.g. Jira) associated with this employee |
| TMS Jira User | no | UID of the Jira tms_User associated with this employee |
| IMS User | no | UID of the ims_User (Incident Management System e.g. PagerDuty) associated with this employee |
| AMS User | no | UID of the ams_User (Activity Management System e.g. Everhour) associated with this employee |
| CAL User | no | UID of the cal_User (Calendar System e.g. Google Calendar) associated with this employee |
Teams
Similar to the Org sheet, you don't need to include non-required columns, or values for those columns.
Your Teams sheet should have the following columns:
| Column Name | Required | Description |
|---|---|---|
| ID | yes | A unique ID for your team |
| Team Name | yes | Team's name |
| Parent Team ID | yes | ID of the parent team as defined in this tab |
| Description | no | Team's description |
| Team Lead ID | no | ID of the team lead as defined in Org tab |
Authenticating to Google
As with other sources, to connect to Google sheets you need to create a connection in Faros. The Google Sheet API requires either a Google Service Account private key (recommended) or Google API key.
Creating a Google Service Account
First, enable Google Sheet API
Next, navigate to your Google API console page and create a service account. Follow these steps.
Once your Service Account has been created, share Google Sheet document with the email of the service account and Viewer permissions. Copy the entire private key. You'll need this to create a connection in Faros.
Add a Faros Credential
Now that your Google Service Account has access to the sheet, we'll need to authorize Faros to use this account.
To add the credential simply go to Admin Settings > Data Control > Credentials and add a new credential with a source type of Org Import from Google Sheets and paste your personal access token. This can also be done when creating the source.
You can find more details on creating a credential here.
Note: your private key is the entire
jsonblob. See the image below for the correct format
Google Private Key in JSON format
Create Faros Source
Now that you have the spreadsheet and have given Faros the correct permissions you're ready to create a source to import this data into Faros.
Under Admin Settings > Data Control > Sources, click +New and search for “Org Import” or click on Org.
Create a new Org Import from Google Sheets source and use the credential you created above. You'll also need the sheet ID which can be found in the url. Remember to make sure this spreadsheet is shared with your service account's email.
You can learn more about creating new sources here.
If you included any identity mappings in your org information you'll need to specify the source of those in the Advanced Settings. If Location column is specified, you can also enable location resolution with a geocoding API to better dashboard experience.
This source will begin syncing data at the interval specified in the configuration. Remember, because this source will sync regularly, any changes to teams or employees done in the UI will be overwritten. All changes should be made in the spreadsheet.
Continue to the 🧑🏽💼 Managing your Organization section to validate your data looks correct.
Updated about 1 month ago