Documentation

About websheet.js

Websheet.js is a lightweight standalone JavaScript library for querying, retrieving, and displaying data from Google Spreadsheet. Load entire worksheets or leverage SQL-like queries to sort, group, and filter data. All you need is the URL of a public Google Spreadsheet.

This is useful to allow other people than you to manage easily some pages content without CMS (restaurant menu, events list, ...).

Get Started

1. Create & Share your spreadsheet

First, create a new Google Spreadsheet including a header row (that will be used as your column names), and your data. You can use any cell format (dates, currencies, checkboxes, ...), even with URLs (links or photos), that will be rendered in your HTML.

After that, click on Share, and in the Get link section, click on Change to anyone with the link to allow public read access to your spreadsheet. Then, copy the spreadsheet link.

2. Import websheet.js

Import websheet.js on your website before </body>, give a name to the data source and provide the spreadsheet link and tab name. You can declare as many data sources as you want, using websheet function multiple times.

You can use the CDN (recommended), or download it here.

<script src="//cdn.jsdelivr.net/gh/pierreavn/websheetjs/dist/websheet.min.js"></script>
<script>
  websheet('products', {  // <-- data source name
              
    // URL of the spreadsheet
    url: 'https://docs.google.com/spreadsheets/d/.../edit',
              
    // Spreadsheet tab name
    sheet: 'Products',
              
  })
</script>

• Aliases to create programatically new columns
• Querying to filter, group and order rows
• Caching to enable/disable client-side caching
• Callbacks to get raw data when they are loaded

3. Render data

Use data sources anywhere in the HTML page to render spreadsheets rows. And that's it!

<!-- Block below is duplicated for each row of data source "products" -->
<div data-websheet="products">
              
  <!-- Display only if checkbox is True -->
  <div data-websheet-if="Display ?">
              
    <!-- Display "Product" column value -->
    <div data-websheet-text="Product"></div>
              
    <!-- Bind "Picture" column value to src attribute -->
    <img data-websheet-bind:src="Picture" />
              
  </div>
              
</div>

• Conditions to display or not elements according to data
• Binding to use data in HTML attributes (images, links, ...)
• States to display or not elements according to state (loader, ...)
• Templates to reuse similar structures in your page

Full Example

To see a full example in action, using all the features of websheet.js, check the demo below:
Open Full Demonstration

Conditions

Use conditions to display or not elements according to data. Provide directly the column name to check as boolean (you can use checkboxes in your spreadsheet).

<div data-websheet="products">
              
  <!-- Display only if "Display?" column is checked -->
  <div data-websheet-if="Display?">
    <h1>Yes !</h1>
  </div>

  <!-- Display only if "Display?" column is not checked -->
  <div data-websheet-unless="Display?">
    <h1>No</h1>
  </div>
              
</div>

If you need more complex conditions (string comparisions, ranges, ...), use Aliases:

<div data-websheet="products">
              
  <!-- Display only if "Category" column is equal to "Breads" -->
  <div data-websheet-if="Is Bread?">
    <h1>It's Bread! !</h1>
  </div>
              
</div>

...

<script>
  websheet('products', {
    ...

    // Define "Is Bread?" alias
    aliases: {
      'Is Bread?': row => row['Category'].value === 'Breads',
    }     
  })
</script>

Read Aliases section for more information.

Binding

Binding allows you to use data in HTML attributes, useful for example in image.src, a.href, ...

<div data-websheet="products">
              
  <!-- Display an image with src equal to "Picture" column -->
  <img data-websheet-bind:src="Picture" />

  <!-- Display a link with href equal to "Link" column -->
  <!-- and name equal to "Product" column -->
  <a data-websheet-bind:href="Link">
    <span data-websheet-text="Product"></span>
  </a>
              
</div>

States

States allows you to display or not elements according to state (loader, ...).

<div data-websheet="products">
              
  <!-- Displayed when data are not loaded yet -->
  <div data-websheet-on:loading>
    Loading...
  </div>

  <!-- Displayed when data are loaded -->
  <div data-websheet-on:loaded>
    <h1 data-websheet-text="Product"></h1>
  </div>
              
</div>

Templates

Templates allows you to reuse similar structures in your page, for example if you wants to display at different locations of you website the same data.

<!-- Product Template -->
<div data-websheet-template="my-product">
  <h1 data-websheet-text="Product"></h1>
  <h2 data-websheet-text="Category"></h2>
</div>

<!-- Display Products List -->
<div data-websheet="products">
  <div data-websheet-render="myproduct"></div>
</div>

...

<!-- Display another location, but with pictures -->
<div data-websheet="products">
  <div data-websheet-render="myproduct"></div>
  <img data-websheet-bind:src="Picture" />
</div>

Aliases

You can easily create new column aliases programatically, in order to use them in your templates. It's very useful for conditional checks, or to aggregate data from multiple columns.

Simply declare new column names, with value a function providing row data as parameter. Those will be triggered for each row of your spreadsheet.

websheet('products', {
  ...

  aliases: {

    // Conditional Check
    'Is Bread?': row => row['Category'].value === 'Breads',

    // Aggregation
    'Full Name': row => row['First Name'] + " " + row['Last Name'],

    // More Complex
    'Discount?': row => {
        if (row['Discounted Price'].value && row['Discount End'].value) {
            return row['Discount End'].value > new Date();
        } else {
            return false;
        }
    }

  }     
})

After that, you can use them in your templates as if it were an existing column:

<div data-websheet="products">
              
  <!-- Display "Full Name" alias value -->
  <div data-websheet-text="Full Name"></div>

  <!-- Display only if category is breads (alias) -->
  <div data-websheet-if="Is Bread?"> It's bread! </div>
              
</div>

Querying

Websheet.js supports Google Visualization API Query Language, which is similar to SQL syntax. It provides the ability to initialize the data source format, by filtering, grouping or sorting rows.

NOTE: For now, only column indexes (A, B, ...) are supported in query language.

For example, the code below will initialize the data source with first 100 rows, ordered by B (second) column.

websheet('products', {
  ...
  query: `select * order by B limit 100`,
})

For more information about how to write queries, read the detailed documentation here.

Caching

In order to render and display the data faster, websheet.js is using client-side caching. By default, caching is enabled. Thus, the timing is the following:

1. If cached version of the data exists in the client browser, data are loaded instantly.
2. At the same time, a query to the Google Spreadsheet is always sent.
3. When Google servers has responded (generally after ~400ms), if websheet.js detects modifications between the cached version and the new one, the displayed data are replaced.
4. Received data are stored in client browser cache, for the next time.

With this system, spreadsheets data are always loaded instantly to the user.

To disable caching, simply mention it:

websheet('products', {
  ...
  caching: false,  // disable caching
})

Callbacks

For now, websheet.js handle a onLoaded callback, triggered when data are loaded from Google Spreadsheet.

websheet('products', {
  ...
  onLoaded: data => {
    /**
     * `data` format:
     *  [
     *     {"Display?": true, "Product": "Baguette", ...},
     *     {"Display?": true, "Product": "Olive Bread", ...}
     *     ...
     *  ]
     */
  }
})