node package manager
Easy sharing. Manage teams and permissions with one click. Create a free org »

mozaik-ext-sheets

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.

preview

  • 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