mozaik-ext-sheets

List rows from Google Sheets in Mozaïk dashboard. The extension can be handy for various use cases where data is maintained in Google Sheets:

• List events (including dates etc)
• Show game results
• Show event participants, company vacation listing
• Etc.

• The widget tries to provide a generic but easy-to-use way to show content in dashboard.
• You can also utilise the Sheets functions as the outcome is shown in widget
• Use format functions to format some cell data
• Use filter function to leave out some results

Setup

• Install module

  npm install --save mozaik-ext-sheets

• Create a project and service account in Google Developer Console

• Enable API: Drive API

• Collect service email and .p12 file

• Convert .p12 file into .PEM

• Configure service key and .PEM file into dashboard config.js file and environment variables / .env file:

  api: {
    sheets: {
      googleServiceEmail: process.env.GOOGLE_SERVICE_EMAIL,
      googleServiceKeypath: process.env.GOOGLE_SERVICE_KEYPATH
    }
  }

• Done.

Widget: List

Show cell contents in a listing. Use dashboard theme to customize the outcome to match with your requirements.

parameters

key	required	description
documentId	yes	Sheets document id, taken form web UI
sheetNo	no	Sheet order number, starting from 0. Defaults to first: 0
range	no	Range from where to list data. Example: A2:C10 or A2:. Defaults to full sheet
style	no	Show results as bullet list or table-like. Valid values: list (default) or table
styles	no	Inline style defintions in React supported style format. Sub-elements: list, listItem`
fields	no	Columns to list, using advanced formatting
random	no	Pick and show random number entries, defined by the value. Zero means no random is used (Default). Example: random: 1
fontSize	no	Use default (``), fixed (12px) or auto scaled fontsize (auto)
format	no	Custom formating functions in object, where key is name of the formatter and used like {COLUMNLETTER!formatter}. See usage for examples
filter	no	Filter some rows out of the outcome by implementing the function. See usage for examples

usage

Imaginary example of Google Sheet document cells:

    |      A     |       B       |       C       |  D  |
 1  | 2015-01-28 | React.js Conf | Facebook HQ   |     |
 2  | 2015-07-02 | ReactEurope   | Paris, France |     |

One widget in dashboard config:

// widget in config.js
{
  type: 'sheets.list',
  // You can find the documentId in sheet URL
  documentId: 'abasdfsdfafsd123123',
  range: 'A1:D10',
  // Use either 'list' or 'table'
  // Table shows every other row with different bg color
  style: 'list',
  fontSize: 'auto', // Use default, fixed or auto-scaled font size
  // Customize the list or list item styles
  styles: {
    list: {
      listStyle: 'none'
    },
    listItem: {
      textAlign: 'center'
    }
  },
  // Values (cells) to show on each row. Use one or multiple column letters:
  // Uses https://www.npmjs.com/package/string-format for formatting
  // NOTE: The both `['{A} {B} {C}']` and `['{A}', '{B}', '{C}']` work, but
  // latter makes it possible to style each field / show the values in different
  // cells in table format
  fields: [
    'Event date: {A!date}',
    '{B!uppercase}',
    '{C} {D}'
  ],
  // Show two random entries (after optional filtering)
  // Handy for picking random quote of the day etc.
  random: 2,
  // Custom formatter functions, name must match with the usage: !method
  // NOTE: Call method.toString() to every function!
  // NOTE: `moment` is available
  format: {
    // !date formatter
    date: function(s){
      return new moment(s).format('YYYY-MM-DD');
    }.toString(),
    // !uppercase formatter
    uppercase: function(s) {
      return s.toUpperCase();
    }.toString()
  },
  // Custom function to filter some results rows
  // If defined, each row is processed through it.
  // Return `false` if you want to filter the row out and `true`
  // for inclusion.
  // NOTE: Only one variable is passed, containing all columns from current row
  // NOTE: Variable `columns` does not contain the columns out of the range
  // NOTE: Call method.toString() to every function!
  filter: function(columns, index){
    // Filter out first two lines
    if (index < 2) {
      return false;
    }
 
    // Filter out the results that are in past
    var eventStart = moment(columns.B, ['YYYY-MM-DD']);
    if (eventStart.isBefore(new moment())) {
      return false;
    }
 
    // Otherwise show the fields from that row
    return true;
  }.toString(),
  columns: 1, rows: 2,
  x: 0, y: 0
}

Changelog

Release 0.6.0

• Added support showing random number (0-n) of entries
• Added support for styling elements with widget arguments

Release 0.5.0

• Added support for table -styled listing

Release 0.4.1

• Fixed missing module: src/client

Release 0.4.0

• Replaced Gulp + Karma with Ava testing
• Started using Eslint
• Fixed dependencies
• Fixed format support

Release 0.3.0

• Updated support to Mozaik 1.4

Release 0.2.0

• Added support for filtering

Release 0.1.0

• Initial release

License

Module is MIT -licensed

Credit

Module is backed by: SC5