What is a Template
Templates, like Reports, serve as ways for Excel and Google Sheets to interact with your data. The main function of a Template is to upload data back to your database or data warehouse.
Creating a Template
Log in to the Kloudio Portal and select Templates and then New Template from the upper right button.
New Templates will default to the Builder tab. Select your connection, schema, table, and the columns that will be updated by the template. If your table has a non-null column that doesn’t auto-generate a value, then be sure to select it.
The Properties tab performs validations, enforces requirements, and rearranges the data presentation.
The Validations tab enables setup and activation of validation rules and custom messages.
The Source tab is used for pre-loading a template with data. The columns returned by the source must either be the same or a subset of the table which the template updates.
NOTE: Source works for the Google Sheet and Office 365 add-on, but is not currently supported by the Excel add-in.
Select Source has the following options:
Report - A previously-created report in Kloudio. Valid Reports can be single tables, subsets of tables, or joined tables. Single tables that do not have an associated report can be used by selecting Table.
SQL - A SQL statement which uses a defined source and connection.
Table - A table from your selected Source.
The Settings tab contains a quick way to disable validations and select Key Columns, which are required for non-null fields.
Using a Key Column
Select one or more non-null columns. Any column selected as a key column MUST contain data in order for the pre-processing validation to work and the upload to complete.
In the example below, the column
ind which contains a numeric key has been selected.
Trying to upload with a null value will result in the following error:
Note: If your non-null field is auto-generated, do not include it as part of the template.
Running a Template
To run a template, go to the Kloudio add-on and select My Templates. The side bar will appear, then select the desired template and Insert Template. If the template has a source, then it will pre-populate the columns with data. This can then be appended and uploaded to the destination table.
To upload data to the source database, go to the Kloudio add-on and select Upload.
Upload contains the following options:
All - Uploads the entire sheet.
Note: If an error occurs, then none of the rows will be committed to the database and changes will not be reflected.
Selected - Uploads only the selected rows.
Note: Currently only supported with Google Sheets.
After performing an Upload, the Status column will contain the status. If an error occurs, it will be displayed in the message column.
Templates allow users to upload data back to their original source, and can contain pre-populated data such as a report. Like reports, they can be shared. However, this should only be done with users who are expected to update the source database; in other cases a Report should be used.
If you have further questions about the Template Upload function, please contact us at firstname.lastname@example.org.