Importing with Google Sheets
For larger organizations it's much easier to manage an organization through a 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 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
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:
|ID||yes||A unique ID for your employee|
|Name||yes||Employee's full name|
|Team ID||no||Employee's team ID as defined in the Teams tab|
|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 |
|Location Name||no||Name of the location associated with this employee, e.g |
|Title||no||Title of the employee, e.g |
|Type||no||Employment type of the employee. Can be one of the Faros enum values (|
|Role||no||Role of the employee, e.g.|
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.
|VCS User||no||UID of the |
|TMS User||no||UID of the |
|IMS User||no||UID of the |
|AMS User||no||UID of the |
|CAL User||no||UID of the |
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:
|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|
|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 Connection
Now that your Google Service Account has access to the sheet, we'll need to authorize Faros to use this account.
Simply go into
Connections page and add a new connection with a source type of
Org Import and paste your private key. This can also be done when creating the source.
Note: your private key is the entire
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.
Create a new
Org Import source and use the connection 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.
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 6 months ago