23. 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.

23.1
Structure of JSON data for think-cell
23.2
Using the JSON data to create a presentation
23.3
Providing the JSON data remotely
23.4
Providing the template remotely

23.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 Introduction to automation). The template file can also be retrieved from a remote location as described in Providing the template 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.

23.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.

23.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/template.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>

Figure 23..1: Remote JSON example

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 Using the JSON data to create a presentation.

23.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.