Google Sheets Overview

Google Sheets can serve as a simple database that allows you the ability to edit the data quickly and straightforwardly. After creating your Google Sheet and setting the read/write access, you'll need to generate an API key.

Accessing the Sheet from the Application

To access the Google Sheet from our application, we'll first need to generate an API key. API keys for Google applications can be generated at the Google API Console.

Now that access to the sheet has been set and the API key has been generated, we can start using the data in our application.

First, we'll need to import the GoogleSheet class:

import { GoogleSheet } from '@grapecity/wijmo.cloud';

Then, we'll create a GoogleSheet object connected to Google Sheets by using our API key:

const API_KEY = 'AIzaSyCvuXEzP57I5CQ9ifZDG2_K8M3nDa1LOPE';
const SHEET_ID_NW = '1qnf-FCONZj_AmOlyNkpIA3mKvP8FQtVOr7K8Awpo360';
let gsNWind = new GoogleSheet(SHEET_ID_NW, API_KEY, {
    sheets: [ 'Products', 'Categories', 'Suppliers' ]
});

We use the constructor to specify which sheets we want to load. If none are specified, it would load all of the sheets.

Finally, we'll add a ComboBox to allow users to see and select product catagories, a FlexGrid to display the data of the catagory that the user selected, and a FlexChart to show the unit price for the products in the database.

ComboBox

let categoryCombo = new ComboBox('#categoryCombo', {
    placeholder: '(All Categories)',
    isRequired: false,
    displayMemberPath: 'CategoryName',
    itemsSource: gsNWind.getSheet('Categories'),
    selectedIndexChanged: (s, e) => {
        let cat = s.selectedItem;
        gsNWind.getSheet('Products').filter = (item => {
            return cat == null || cat.CategoryID == item.CategoryID;
        });
    }
});

FlexGrid

let supplierMap = new DataMap(gsNWind.getSheet('Suppliers'), 'SupplierID', 'CompanyName');
let productGrid = new FlexGrid('#productGrid', {
    ...gridOptions,
    autoGenerateColumns: false,
    columns: [
        { binding: 'ProductName', header: 'Product Name' },
        { binding: 'UnitPrice', header: 'Unit Price', format: 'n2' },
        { binding: 'QuantityPerUnit', header: 'Quantity Per Unit' },
        { binding: 'SupplierID', header: 'Supplier', dataMap: supplierMap },
        { binding: 'UnitsInStock', header: 'In Stock', format: 'n0' },
        { binding: 'UnitsOnOrder', header: 'On Order', format: 'n0' },
    ],
    itemsSource: gsNWind.getSheet('Products')
});

FlexChart

let productChart = new FlexChart('#productChart', {
    chartType: 'Bar',
    axisX: { majorGrid: true, axisLine: false },
    axisY: { majorGrid: false, axisLine: false, reversed: true },
    legend: { position: 'Bottom' },
    bindingX: 'ProductName',
    series: [
        { binding: 'UnitPrice', name: 'Product Unit Prices (US$)' }
    ],
    itemsSource: gsNWind.getSheet('Products')
});

When the user selects a category or sorts the data, the grid and the chart update automatically.