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