Introduction:
The purpose of this module is to import data in CSV format to a Google Sheet. This module will take the input CSV file and either replace or append while also either matching headers or not.
Technical Support:
If you need assistance in installing and configuring JetTrac ImportToGoogleSheets™, call Pro Technology Automation, Inc. at 805-527-1248 or email us at support@protechinc.com. Please note that the JetTrac ImportToGoogleSheets™ license fee does not cover configuration services and technical support so there may be an additional charge. Please ensure you read these instructions carefully before calling for technical support.
How to run JTImportToGoogleSheets:
A bat file or job step in Job Controller can execute this module.
To run JTImportToGoogleSheets, you will need to have these files in the same folder:
- JTImportToGoogleSheets.exe
- JTImportToGoogleSheets_config.ini
Executable files should not be edited for any reason.
All google modules need to be authorized to be allowed to run. To do this, make sure you are logged into the proper google account you want JetTrac to access, then run the Google Modules Authorization .bat file. It will ask you to log in and allow access to JetTrac to use the account. This only needs to be done once as the file will authorize all google modules.
Default Functionality:
Depending on the setup of the configuration .ini file, there are four scenarios that could occur when the CSV data is imported into Google Sheets using this module.
Scenario 1:
Config.ini:
GoogleSheetID=”12345”
AppendData=Y
MatchHeaders=Y
Input.csv:
CustomerName,CustomerEmail,CustomerPhone
Matt Sliwa,matthew.sliwa@protechinc.com,5555555555
Dan Roth,dan.roth@protechinc.com,5555555555
Google Sheet Before Import:
CustomerName,CustomerEmail,CustomerPhone
Jacob Waitkuweit,jacob.waitkuweit@protechinc.com,5555555555
Google Sheet After Import:
CustomerName,CustomerEmail,CustomerPhone
Jacob Waitkuweit,jacob.waitkuweit@protechinc.com,5555555555
Matt Sliwa,matthew.sliwa@protechinc.com,5555555555
Dan Roth,dan.roth@protechinc.com,5555555555
Scenario 2:
Config.ini:
GoogleSheetID=”12345”
AppendData=Y
MatchHeaders=N
Input.csv:
Matt Sliwa,matthew.sliwa@protechinc.com,5555555555
Dan Roth,dan.roth@protechinc.com,5555555555
Google Sheet Before Import:
CustomerName,CustomerEmail,CustomerPhone
Jacob Waitkuweit,jacob.waitkuweit@protechinc.com,5555555555
Google Sheet After Import:
CustomerName,CustomerEmail,CustomerPhone
Jacob Waitkuweit,jacob.waitkuweit@protechinc.com,5555555555
Matt Sliwa,matthew.sliwa@protechinc.com,5555555555
Dan Roth,dan.roth@protechinc.com,5555555555
Scenario 3:
Config.ini:
GoogleSheetID=”12345”
AppendData=N
MatchHeaders=Y
Input.csv:
CustomerName,CustomerEmail,CustomerPhone
Matt Sliwa,matthew.sliwa@protechinc.com,5555555555
Dan Roth,dan.roth@protechinc.com,5555555555
Google Sheet Before Import:
CustomerName,CustomerEmail,CustomerPhone
Jacob Waitkuweit,jacob.waitkuweit@protechinc.com,5555555555
Google Sheet After Import:
CustomerName,CustomerEmail,CustomerPhone
Matt Sliwa,matthew.sliwa@protechinc.com,5555555555
Dan Roth,dan.roth@protechinc.com,5555555555
Scenario 4:
Config.ini:
GoogleSheetID=”12345”
AppendData=N
MatchHeaders=N
Input.csv:
Matt Sliwa,matthew.sliwa@protechinc.com,5555555555
Dan Roth,dan.roth@protechinc.com,5555555555
Google Sheet Before Import:
CustomerName,CustomerEmail,CustomerPhone
Jacob Waitkuweit,jacob.waitkuweit@protechinc.com,5555555555
Google Sheet After Import:
Matt Sliwa,matthew.sliwa@protechinc.com,5555555555
Dan Roth,dan.roth@protechinc.com,5555555555
Job Step Configuration Window in JobConfig for JetTrac ImportToGoogleSheets:
The Job Step Configuration window for this module is the same as the configuration for JetTrac PushToGoogleSheets (due to their similar functionality), however, the Input XML is an Input CSV with JetTrac ImportToGoogleSheets.
When setting up a job step using JTImportToGoogleSheets in JobConfig the only lines of the config you need to worry about are the first and the second to last, the Config file and the Input CSV. If left as an asterisk * the input will be whatever file is in the data folder that triggered the job running. This works well if it’s the first step but if the input is also the output of a previous job step you would need to specify the fully qualified path of the input xml to be written to a spreadsheet in Google Sheets. All other fields should be filled with the fully qualified path to the intended file. Eg: C:\JetTrac\…\input.csv
In JobConfig, file paths entered should not be in quotes.
As with all modules, there is a dropdown to select whether or not the job should stop completely if this step fails. If you select Yes, any error will terminate the job process and write to the log file what happened, if no, then the job will try to continue anyways. However if any steps further down the line rely on the output of a job set to not stop on error, they may not work properly.
For more specifics go to the JetTrac Field ServiceJobConfig page.
JTImportToGoogleSheets Command Line:
Batch document:
C:\JetTrac\Programs\JTImportToGoogleSheets\JTImportToGoogleSheets.exe “C:\SampleCsv.csv” “C:\SampleIn.xml” “C:\SampleConfig.ini” “C:\JetTrac.log”
The command line begins with the file path to the module’s executable file followed by the path to the input CSV file, the path to the input XML file, the path to the configuration INI file, and the log file. Each part is the fully qualified path to the file, in quotes, and separated by a space.
JTImportToGoogleSheets Config.ini:
GoogleSheetID=GoogleSheetID
WorksheetName=”Sheet1″
AppendData=”Y”
MatchHeaders=”Y”
The GoogleSheetID key value will contain the sheet ID in quotes or an XML field name without quotes. If an XML field name without quotes is found in this key value, the module will pull the sheet ID from the input.xml with the specified field name.
The WorksheetName key value is the name of the worksheet tab you wish to import to. In Google Sheets you can add multiple tabs at the bottom of each sheet, the value of this should be the exact name of the tab you are importing to (this is necessary even if you only have a single tab).
The AppendData key value will be a Y or N. If Y, the data in the input.csv will be appended to the end of the existing data in the Google Sheet. If N, the data will replace the existing data in the Google Sheet, not including the first record containing the header field names.
The MatchHeaders key value will be a Y or N. If Y, when importing the module will look at the header names in row 1, and match the corresponding data to the correct header names in row 1 of the Google Sheet. If this key value is Y and the Header Names in the .csv do not exactly match those in the Google Sheet, the module should error. If this key value is set to N, then it is assumed that the columns do not have headers and the every row (including the first) will be imported to the Google Sheet in the exact column order as the input.csv file.