26. Automation with JSON data

You can use data in JSON to duplicate charts first created as a template and supply a new datasheet for them. You can control the order in which specific templates are used to construct a new presentation. Templates can also be used multiple times.

Both the PowerPoint templates and the JSON data can be stored either locally or remotely. The JSON data may even be generated on the fly by a web service.

26.1 Structure of JSON data for think-cell

An example of JSON data for think-cell is shown below and can be found in the file sample.ppttc in the subfolder ppttc of the think-cell installation directory. The JSON specifies a presentation to be created from a concatenation of templates. It conforms to the schema at ppttc/ppttc-schema.json.

example JSON file for think-cell automation.

At the root level is an array of items. Each item specifies a template file and data for its charts. Different templates can be concatenated and a single template can be used more than once. In the simplest case, a single template is used a single time to fill all charts contained in that template with data.

Each item in the array of concatenated templates contains two keys: image.  template and image.  data. The value for image.  template is the path to a PowerPoint file with think-cell charts for which a name has been set as an identifier (see 24. Introduction to automation). The template file can also be retrieved from a remote location as described in 26.3 Providing the JSON data remotely.

The value for the key image.  data is a list of data tables in JSON format for the charts contained in the template. Each item in the list has two properties: image.  name and image.  table. image.  name specifies a chart using its name and image.  table holds the data table.

The structure of the value for image.  table corresponds directly to an untransposed datasheet with rows representing series and columns representing categories. For a default chart that means the following order of rows will be represented:

  1. A row with an empty cell first (null) and then cells with category names.
  2. A row of 100% values. If no 100% values are used, the empty row is specified as image.  [].
  3. Multiple rows with a first cell giving the series name and subsequent cells containing numerical values.

Any chart that can be given a name can be referenced in JSON. For their specific datasheet layout, see their respective chapter or simply open the chart’s internal datasheet for reference.

Note: Any cell within the think-cell datasheet range can hold any kind of text. If desired, the first cell in the first row can also be filled.

The data composing the value for a table key must comply to certain rules set in the schema. The value for the table key itself is an array. Sub-arrays represent the rows of the datasheet. Empty rows can be specified by using an empty array []. The content of a cell is described by the elements listed in such a sub-array. The order of the elements corresponds to the order of the columns in a datasheet. An empty cell must be explicitly described using the null element. Cells with content have to be described using another key-value pair. In this case, the key describes the type of data and the value holds the actual data to be shown in the presentation. Three different data types are supported:

image.  string for any kind of text. All printable unicode (UTF-8) characters are supported. Example: {"string":"Echo"}

image.  number for any kind of numbers. The decimal separator used has to be a point. Example: {"number":"5"}

image.  date for dates. The required format is YYYY-MM-DD. Example: {"date":"2016-09-04"}

Any formatting of the contents of the cells (e.g., specifying the date format) has to be done in the PowerPoint template.

For named text fields, the table element consists of just one "cell" in one "row", so you need to use a JSON array like in the following fragment, which would appear once for each named text field inside a data element:

{ 
  "name": "Title", 
  "table": [[{"string":"A slide title"}]] 
},

26.2 Using the JSON data to create a presentation

The JSON data file must have a file type of .ppttc. When opening such a file:

  1. think-cell reads the file and checks its structural integrity.
  2. A new presentation is created. For each item at the top level of the JSON file, the new presentation contains a copy of the specified template.
  3. In the template copies, the datasheets of the charts identified by the name values are replaced with the data in the table values. Any total labels, label placements and difference arrow values or other chart decorations are updated.

Afterwards, the new presentation is displayed. The user can further edit its contents and save it or use it in any other way.

You can also create a presentation from JSON data on the command line:

ppttc input.ppttc -o output.pptx

The executable ppttc.exe is located in think-cell’s installation folder. Calling ppttc.exe can be integrated easily in automation workflows.

26.3 Providing the JSON data remotely

The JSON data in a .ppttc file can also be generated remotely by a web service and offered to the user for download in a web browser. Please see the sample provided below and in sample.html in the subfolder ppttc of the think-cell installation directory.

<!DOCTYPE html> 
<html> 
<body> 
  
<h1>ppttc test</h1> 
  
<button type="button" onclick="myFunction()"> 
Download .ppttc 
</button> 
  
<a id="downloader" style="display:none"/> 
  
<script> 
function myFunction() { 
  var obj = [ 
    { 
      template : "https://static.think-cell.com/ppttc/template3.pptx", 
      data: [ 
        { 
          name: "Chart1", 
          table: [ 
            [null, {string:"Alpha"},{string:"Bravo"},{string:"Charlie"}], 
            [], 
            [{string:"Delta"},{number:1},{number:2},{number:3}], 
            [{string:"Echo"},{number:4},{number:5},{number:6}] 
          ] 
        }, 
        { 
          name: "Chart2", 
          table: [ 
            [null, {date:"2016-09-03"},{date:"2016-09-04"},{date:"2016-09-05"}], 
            [], 
            [{string:"Foxtrot"},{number:7},{number:8},{number:9}], 
            [{string:"Gulf"},{number:10},{number:11},{number:12}] 
          ] 
        } 
      ] 
    } 
  ]; 
  var elemDownloader = document.getElementById("downloader"); 
  elemDownloader.setAttribute("href","data:application/vnd.think-cell.ppttc+json;charset=utf-8," 
    + encodeURIComponent(JSON.stringify(obj))); 
  elemDownloader.setAttribute("download", "sample.ppttc"); 
  elemDownloader.click(); 
} 
</script> 
  
</body> 
</html>

In our example, the JSON data is compiled on the fly when the user requests a download. While the example embeds static data, you may of course dynamically create JSON from other data sources using parameters given by the user on your website.

When a .ppttc file is downloaded, the user can choose to save the file or open it similarly to other downloads. When opening the file, the JSON data is used to create a new presentation as described in 26.2 Using the JSON data to create a presentation.

26.4 Providing the template remotely

Template files with think-cell charts can also be provided via a remote server. In this case, the value for the key template is a URL instead of a local path, as shown in the remote JSON example above. The URL can specify http as the protocol, https for a secure connection or any other protocol valid on the system where the .ppttc file is opened.

When processing a .ppttc file that refers to a remote template, PowerPoint retrieves the template file. Therefore, the user that opened the .ppttc file needs appropriate access to the remote template.

When the JSON data is generated remotely by a web service and the templates are also stored remotely, only a standard think-cell installation is necessary on the users machine to use dynamically generated PowerPoint presentations with think-cell charts.

26.5 Processing JSON data remotely

You can run think-cell’s processing of JSON data as a server. The merging of JSON data with templates to create PowerPoint presentations is done on a remote server in that case. The server accepts JSON fragments as input and delivers the merged PowerPoint presentations as output, both via HTTP.

To start the think-cell server, go to the installation folder and run tcserver.exe. The following dialog is opened:

think-cell server setup dialog.

To start the server:

  1. Specify the IP address and port the server should listen at in the UrlPrefix field in the form of a URL. The first part of the URL determines whether unencrypted HTTP or encrypted HTTPS is used.
  2. Click the Apply button.
  3. Confirm the configuration changes of your system in the User Account Control (UAC) dialog that appears.

In the Log field at the bottom of the window you will see a confirmation that the server has started and is now listening at a URL. You will also see all client requests and server responses in the Log field.

think-cell server dialog with log messages.

To stop the server, click the Remove button. To change the URL, enter a new URL in the UrlPrefix field and click Apply.

The server accepts JSON data as HTTP POST requests with MIME type application/vnd.think-cell.ppttc+json and responds with a PowerPoint file.

To start using the server, please copy the URL from the UrlPrefix field and open it in a browser. A sample page is opened. The server is self-documenting, with the HTML source code of the sample page showing its use and an elaborate example. The example contains the following elements:

  • HTML markup for a button that invokes a method using XMLHttpRequest to communicate with the server when clicked.
  • A static JSON fragment. In your use, you will typically generate the JSON data dynamically.
  • A JavaScript function to send the JSON data to the server. It generates the HTTP POST request with the static JSON data. The server response is presented to the browser as a file download.

Any other method of generating HTTP POST requests and saving the server’s response as a PowerPoint file also works. In your use, you are not limited to using JavaScript for that purpose.

Share