SlidesMerge add-on

Page Content hide
1 Important note on my add-ons
2 ChordSnip
3 Color Arranger
4 SankeySnip
5 Slides merge
6 Dicers
7 Here’s a demo video
8 And how to embed charts
9 Summary of template syntax
10 What can you do
11 Example
12 Options and settings
13 Data sheet
14 Global variable sheet
15 Source

Important note on my add-ons

I’ve (reluctantly) decided to stop supporting all my Apps Script add-ons, as I no longer had the time or inclination to put them through the various verification processes and upcoming add-on store process changes.

I realize that many of you may be using some of them. In order to provide some continuity, since they are open source, you are welcome to make your own copy and use them how you wish. Any developers who want to repackage and republish are also welcome to do so.

Here is the repository. You’ll also find the link to the scripts if you prefer to take a copy of already built scripts.

ChordSnip

Documentation here

Color Arranger

Documentation here

SankeySnip

Documentation here

Slides merge

Documentation here

Dicers

Documentation here

Slides Merge is a sheets add-on that allows you to create multiple sets of slides based on a Slides template and the data in a Sheet. Placeholders are inserted in the template at the places to substitute in the sheet data. A data placeholder is simply the name of the column, in this format {{column name}} Images can be inserted in the created deck by using a placeholder that looks like this {{{column name}}} where the column contains urls of external images. Each row in the data generates a set of slides. Each set can be in separate decks, or a combined deck. Global variables can be defined in a separate sheet to provide data for placeholders that are independent of the current data row – in others words applied to every set of slides. The same system of placeholder is used for both global and data variables, so {{global variable name}} will take its data from a sheet of name.value pairs held in a global variables sheet. Charts can be inserted by addressing them like this –  {{chart.nameofsheet.positiononsheet}} to create a linked chart (one that will be updated when the original spreadsheet data is updated) or with triple curly brackets  {{{chart.nameofsheet.positiononsheet}}} to insert an image of the current state of a chart. Charts can be specified both as data fields (to get a separate chart for each data row), or as a global item.   

Here’s a demo video

slidesmerge demo

 

And how to embed charts

 

Summary of template syntax

 Placeholders in your template, will be replaced with values either from each row of your data, or from a globals sheet if you have one. The Placeholder name should match the column name (for data substitutions), or the name (where you have a globals sheet).

  • Normally a placeholder is replaced by the value in the data or global sheet.
  • Where the value  is recognized as a chart reference, it’s used to pick up  chart from the spreadsheet. A chart id takes the format chart.sheetname.chartindex (where chartindex is its position in the sheet starting at 1 .. to deal with multiple charts on the same sheet)
  • Where the value  is recognized as a hosted image reference, it’s used to pick up an image at that url. An image reference is url starting with “http” or “https”.

  

 Placeholder Example  What happens
 {{columnname}}  the value in the given column replaces the placeholder for each set of slides created. If the value is a chart reference, a LINKED chart is inserted (which will automatically updates as the source chart is updated).
 {{globalname}}  the value from the globals sheet replaces the placeholder wherever it finds it. If the value is a chart reference, a LINKED chart is inserted (which will automatically updates as the source chart is updated).
 {{{columnname}}} For each set of slides created, if the value is a chart reference, an image of the chart is inserted (which will notautomatically updates as the source chart is updated). If the value is an image reference, the image is fetched an embedded.
  {{{globalname}}}  If the value is a chart reference, an image of the chart is inserted (which will not automatically updates as the source chart is updated). If the value is an image reference, the image is fetched an embedded. In this case the placeholder matches a global name and is not linked to any data values in the data input sheet.

 

What can you do

 

Example

 Here’s a template deck. It contains a master slide (not to be duplicated), and some other slides that are repeated for each row in the input data.  And here’s the result after merging

 Here’s the data that was used.

 

Options and settings

  

 option  values  purpose
 Output decks  single or multiple  Single will create on deck with sets of slides duplicated for each row in the sheet data. Multiple creates a separate deck for each row in the data.
 Output base name  a name  If single is selected, the deck created will have this name. If multiple, then this will be used as the base for each deck, with suffices appended to distinguish them from each other.
 Multi deck suffix  blank or a column name  Only relevant is multiple is selected. If blank each deck will have a sequential sequence starting at 1. I a column number, the value for that column for each row will be appended to the output base name.
 Master slide indices  a comma separated list starting at 1, or blank  Only relevant if single is selected. These refer to the index number of the slides in the template deck. Any slide numbers mention here will only appear once in the deck – they won’t be replicated for for each row of data.
 Data sheet  Any of the sheets in the active spreadsheet  The selected sheet will be used as input to the merge operation.
 Start row  Any number  The row in the data at which to start. The default is 1, the first row
 Finish row  Any number  The row in the data at which to finish. The default is 0, which means the last row
 Global variable sheet   Any of the sheets in the active spreadsheet or blank  If specified, the selected sheet of name/value pairs should contain columns called name & value. The names can be used as placeholders in the slide template and values from this sheet will be substituted in the same way as values from the data sheet.
 Skip if data field(s) are null  never,any,all  Controls whether to skip creating a duplicate slide if the placeholders fields have blank data.
never –  will always duplicate slides, regardless if placeholder data is empty
any – will skip the slide if it contains any placeholders with empty data
all – will skip the slide if it all placeholders have empty data
Note that any global placeholders are included in the assessment

Data sheet

This can be any format. Column headings should be used as placeholders, so in the example below, {{Name}} in the template will be replaced by the values in the Name column, and {{{Picture}}} will fetch the image in the Picture column and replace the placeholder with it. Note that placeholders and column names must exactly match in both case and spacing. Note that 

 

Global variable sheet

This should be a sheet that contains the name and value columns. It can contain other stuff too, but it will be ignored. 

A common mistake is to forget to specify the sheet containing the globals, as it’s an optional input, so don’t forget to specify

If you are using dates, you should format them as text, otherwise, you’ll get the default JavaScript date format – for example:

=text(now(),”dd-mmm-yy”)   

Source

As usual, the project is open source. Code is on Github.   
Subpages