Introduction:
Easily captures data submitted from PDF forms to Google Sheets that can be reviewed and approved before data is submitted to other systems
Technical Support:
If you need assistance in installing and configuring JetTrac PushToGoogleSheets™, call Pro Technology Automation, Inc. at 805-527-1248 or email us at support@protechinc.com. Please note that the JetTrac PushToGoogleSheets™ 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 JTPushToGoogleSheets:
A bat file or job step in Job Controller can execute this module.
To run JTPushToGoogleSheets, you will need to have these files in the same folder:
- JTPushToGoogleSheets.exe
- JTPushToGoogleSheets_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:
Server-side updating of Google Sheets from data in an XML file that is collected from PDF forms
Job Step Configuration Window in JobConfig for JetTrac PushToGoogleSheets:
When setting up a job step using JTPushToGoogleSheets 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 XML. 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.xml
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.
JTPushToGoogleSheets Command Line:
Batch document:
“C:\JTPushToGoogleSheets.exe” “C:\JTPushToGoogleSheets_Input.xml” “C:\JTPushToGoogleSheets.ini” “C:\JTPushToGoogleSheets.log” “–values=’Sample'”
The command line begins with the file path to the module’s executable file followed by the path to the input XML file, the path to the config file, and the log file. Each part is the fully qualified path to the file, in quotes, and separated by a space.
At the end of the main command line you will see a –values= parameter. This is used to define values directly on the command line instead of in the config.ini file values key value. This is optional on the command line and is mainly used for our WebLog functionality. The syntax is “–value= and then a pipe-separated list of the values you wish to assign ending with a ”
JTPushToGoogleSheets Config.ini:
processingType= “Update” or “Insert” depending on if you wish to update lines that already exist with new data or simply insert new lines. This is an optional key value and will default to “Insert” if not present in the config.ini file.
spreadsheetKey= “Static Key from GoogleSheet URL” or XMLTag Name ( without the < > Symbols )
worksheetName=”Static Text” or XMLTag Name ( without the < > Symbols )
columnHeaders=”Static Text”|”Static Text” (Must match Google Sheet Headers)
values=”Static Text”|XMLTagName (Must match XML Tags)
updateLookupFields=”Column Header Name”>”Value in Column” (Can be a string or fieldname from data file. Pipe-delimited if multiple)
dynamicallyGenerateNewWorksheets=”Y”
numberRetries=”insert number of retries in whole numbers here”
as an example:
processingType=”Update”
spreadsheetKey= “1TmxybIJ_f9_kAKiWXAIa0dxX7Z_EuM7FnuiwTlmLJ90” or GoogleSheetKey
worksheetName=”Adobe Systems Inc.” or Accounts
columnHeaders=”Invoices”|”Attachments
values=”PTA Invoice 8168 Adobe Training “|Training
updateLookupFields=”First Name”>”Andy”|”Last Name”>”Roth”
dynamicallyGenerateNewWorksheets=”Y”
numberRetries=”10″
Additional Notes:
IMPORTANT: JTGooglePushToGoogleSheets will fail if there are any modifications one of the corresponding/matching field references. This includes being case sensitive.
eg. Change the Header name in the Google Sheet on its own will fail. You can remedy this by matching the names either way.