Use Scriptability to export and import Worksheets, Views, Tables, Pinboards, SpotIQ results, and Answers in a human-readable format.

ThoughtSpot developed its own scriptable approach for exporting, enhancing, and migrating Worksheets, Views, Tables, Pinboards, SpotIQ results, and Answers.

You can model your data and build out sophisticated dashboards in your test environment, before deploying to all users.

The Scriptability feature supports several scenarios that you may encounter:

  • Migrating from a development environment to a production environment by downloading files from the development cluster and uploading the same files into the production cluster
  • Implementing metadata changes outside ThoughtSpot UI, such as replacing the underlying tables for an object, or replacing a single column from one table with a column in another table
  • Making bulk changes, such as mass renaming of objects defined by Worksheets, and managing duplicates
  • Reusing existing objects to build new objects, such as building two very similar objects based on a similar, pre-existing object.

How to use Scriptability

Depending on how you want to use Scriptability, there are several workflows you can follow:

  1. Edit and update an existing object in the same cluster: You can either
  2. Migrate an existing object from one cluster to a new cluster: export the object(s) and import the updated file(s) to the new cluster.
  3. Edit and migrate an existing object from one cluster to a new cluster: You can either
    • export the object(s), edit the object(s) by modifying its ThoughtSpot Modeling Language (TML) representation, and import the updated file(s) to the new cluster or
    • edit the object(s) using the in-app TML editor, publish the updated file(s), export the object(s), and import the updated file(s) to the new cluster. Note that this workflow changes the object(s) in both clusters.

Prerequisites

Refer to the following tables for required permissions for importing and exporting Pinboards, SpotIQ results, Answers, Worksheets, Tables, and Views.

Import

Import and create a new object without importing its dependents Import and create a new object and its dependents Import and update an existing object without dependents Import and update an existing object with dependents
The dependents must already exist in the cluster. You must have view permissions for the first-level dependent. For example, if you import a Pinboard that is built on a Worksheet that is built on a table, you must have view permission for the Worksheet. When importing a new Worksheet or View, you must have the can manage data permission. Can manage data, if the object or any of its dependents is a Worksheet or View. Edit permission on the existing object. The dependents must already exist in the cluster. You must have view permissions for the first-level dependent. When importing a Worksheet or View, you must have the can manage data permission. Edit permission on the existing object(s). Can manage data, if the object or any of its dependents is a Worksheet or View.

Export

Export with dependents Export without dependents
View permission on the object and all dependents. View permission on the object and its first-level dependents.

Export an object

You can export one object at a time, or export more than one object as a zip file, or SpotApp. The SpotApp contains a document called the Manifest file, which defines the objects you exported, and their underlying data sources.

Export one object

To export one object:

  1. Navigate to the Pinboard, SpotIQ result, Answer, View, Table, or Worksheet you want to export. Note that SpotIQ results are in the form of Pinboards, but you can only find them in the SpotIQ tab.

  2. Click the More menu icon more options menu icon, and select Export as TML.

    Export a Pinboard

Export multiple objects

To export multiple objects at a time, follow these steps:

  1. Navigate to the Answers, Pinboards, SpotIQ, or Data page from the top navigation bar, depending on the objects you want to export.

  2. Hover over the objects you want to export, and click the empty checkboxes that appear.

  3. Select the Export button.

    Export multiple objects

  4. Choose whether to export only the objects, or the objects and their underlying data sources (Worksheets, Tables, and Views). If you export a table, you do not see this modal, since tables do not have any dependents.

    Choose what to export

  5. Click Export.

  6. Open the downloaded TML zip file. The SpotApp zip file contains a document called the Manifest file, which defines the objects you exported, their underlying data sources, and any export errors. If an individual export fails, you can find an error message in the Manifest file. The zip file still exports, even if an individual object’s export fails.

Edit the TML file

You can edit the TML file in one of two ways. You can export the object(s) and edit the file(s) in any text editor, before you import it. Or, you can use the in-app TML editor to edit, validate, and publish the object(s). Refer to ThoughtSpot Modeling Language for information on syntax in the TML files.

Edit, validate, and publish objects using the TML editor

You can access the TML editor from the object list page, or from the object itself. To edit and update multiple objects using the TML editor, access it from the object list page.

To use the TML editor, follow these steps:

  1. Navigate to the Answers, Pinboards, SpotIQ, or Data page from the top navigation bar, depending on the object you want to update.

  2. Click the name of the object you want to edit, or select multiple objects by clicking on the checkboxes that appear when you hover over an object name.

  3. From the object list page, select the Edit TML button. From the object itself, select the More menu icon more options menu in the upper-right side of the screen, and select Edit TML.

    Edit TML - object list page

    Edit TML from object

  4. The TML editor opens. Edit the TML file(s), using the syntax specified in ThoughtSpot Modeling Language. Note that SpotIQ results are in the form of Pinboards. Refer to Pinboard TML to edit a SpotIQ TML file.

    The TML editor has the following functions under the top menu:

    • File: Validate, Publish, and Exit editor. You can also validate and publish using the validate and publish buttons at the top right of the editor. You can also exit the editor using the X button at the top right corner. The system warns you if you try to exit with unsaved changes.
    • Edit: Undo, Redo, Cut, Copy, Select all, Fold, Fold all, Unfold, Unfold all, and Go to line. The Fold option compresses the lines in the file so you only see the first line of a section. Go to line opens a dialog box, where you can type in the number of the line you would like to go to. This is useful for long TML files.
    • Find: Find and Find and replace. This functionality allows you to easily find words or parameters in the TML file. You can also click on a word or parameter in the TML editor, and the editor highlights all instances of that word.
    • View: Show/Hide errors, Show line numbers, and Hide line numbers. Show/Hide errors toggles the Errors sidebar on and off. The Errors sidebar does not appear until after you Validate a file, if there are errors in it.
    • Help: Documentation. This links to the ThoughtSpot Modeling Language documentation.
  5. When you finish editing the TML file(s), select Validate in the top right corner. You must validate each file individually. A blue dot appears next to any file that contains changes.

    Validate the file

  6. If you constructed the file(s) correctly, a green check mark appears next to the name of the file. If you did not construct the file correctly, a red bar appears near the top of the screen, telling you that ThoughtSpot found errors in one or more files. Click Show errors to see the errors listed in the Errors sidebar.

    Review errors

  7. After validating, select Publish in the top right corner, next to Validate. You must publish each file individually.

  8. The system displays a Publish status dialog box. You can select Open [object] to open the object you just published in a new tab, or click Close to return to the TML editor.

    Open the object or return to the TML editor

Update an object

You can overwrite an existing Worksheet, View, Table, Answer, Pinboard, or SpotIQ result, by downloading the TML file, making any necessary changes, and then re-uploading the TML file. To update SpotApps, or collections of objects packaged together as a zip file, refer to SpotApps.

You can also update an object using the TML editor.

To update an existing object by downloading the TML file and modifying it, follow these steps. In this case, we are updating a single Worksheet. You can update multiple objects at once by uploading them in .zip file format.

  1. Export the object you want to update, as in steps 1 to 5 of the Export an Object section above.

  2. Edit the file in a text editor.

  3. Navigate to the Answers, Pinboards, SpotIQ, or Data page from the top navigation bar, depending on the object you want to update.

  4. Click the name of the object you want to edit.

  5. Click the More menu icon more options menu in the upper-right side of the screen.

  6. Select Update from TML.

    Here, we are uploading the edited TCPH WS worksheet.

    Start Worksheet update from file

  7. In the Import interface, click Select .TML or .zip files to upload.

    Find the Worksheet TML file

  8. In your file system, find and select the TML file you edited.

  9. If you constructed the file correctly, the Import interface displays a Validation successful message. You can now import the file.

  10. If you uploaded a .zip file with multiple objects, you can unselect any files in the .zip file you do not want to upload.

  11. The Import interface recognizes that an object with this GUID already exists in the system, and asks if you would like to create a new object, or update the existing one. Select Update existing [object].

  12. Click Import selected files.

  13. The Import Status screen displays the status of the objects you imported. You can open the object(s) that you imported, or click Done to return to the main object page.

Migrate an object

To migrate an Answer, Pinboard, SpotIQ result, View, or Worksheet from one cluster to another, follow these steps. To migrate SpotApps, or collections of objects packaged together as a zip file, refer to SpotApps. Note that you cannot create a new Table using Scriptability. You can only update existing Tables.

  1. Export the object you want to move, as in steps 1 to 5 of the Export an Object section above.

    The object remains on the original cluster as well, unless you delete it.

  2. Navigate to the cluster you want to add the object to.

  3. Click Answers, Pinboards, SpotIQ, or Data on the top navigation bar, depending on the objects you want to migrate.

  4. To upload a Worksheet or View, click the More icon more options menu in the upper-right side of the screen. Then, select Import TML.

    Import Worksheet or View TML

  5. To upload a Pinboard or Answer, click the Import TML button in the upper-right side of the screen.

    Import a Pinboard or Answer

  6. In the Import interface, click Select .TML or .zip files to upload.

    Select a file

  7. In your file system, find and select the TML file. The file uploads automatically.

  8. If you constructed the file correctly, the Import interface displays a Validation successful message. You can now import the file.

  9. If you uploaded a .zip file with multiple objects, you can unselect any files in the .zip file you do not want to upload. Here, we only want to import Brand Revenue and Average Revenue by Part, not Basic Answer 1.

  10. Click Import selected files.

  11. The Import Status screen displays the status of the objects you imported. You can open the object(s) that you imported, or click Done to return to the main object page.

Limitations of working with TML files

There are certain limitations to the changes you can apply by editing a Worksheet, Answer, Table, View, Pinboard, or SpotIQ result through TML.

  • Formulas and columns can either have a new name, or a new expression. You cannot change both, unless migrating or updating the worksheet two times.

  • It is not possible to reverse the join direction in the TML script.

  • You cannot create new tables using Scriptability. You can only update existing tables.

  • You can only change logical tables using Scriptability. You cannot change the physical version of the table that exists in a database. When you change the column_name, for example, the name changes in the application, but not in the physical table in the database.

  • You cannot import manually compressed .zip files. You can only import .zip files that you exported from ThoughtSpot: a custom SpotApp, an object and its associated data sources, or multiple objects of the same type that you exported from the object list page.

  • You cannot create Scriptable representations of R- or Python-powered visualizations.

  • You can only view and modify joins at the table level in the source table TML. You cannot view or modify table-level joins from the destination table’s TML file.

  • You cannot modify joins at the table level from the Worksheet or View TML file. You can only change the joins for that specific Worksheet or View. To modify table-level joins, you must edit the source table’s TML file.

  • You cannot use TML to remove columns or tables from an Embrace connection. You can only add them.