Using Google Sheets with Wijmo

If you need a simple database and the ability to edit the data quickly and straightforwardly, Google Sheets might be an excellent alternative to a full-blown database.

They make it easy to:

• Create and edit the data using the Google Spreadsheets application
• Share the sheets with other people
• Access the sheets from any device
• Get backups and version history
• Change the database easily by adding new columns on the go

But remember they are not real databases and are limited in:

• Size (tables should not have more than a few thousand rows)
• Functionality (no built-in queries, joins, or consistency checks for example)

In the next few sections, we will illustrate all this with a simple example based on the traditional Northwind database.

Create the Sheet

The first step was to create the Northwind spreadsheet in Google Sheets.

The spreadsheet (database) contains the following eight sheets (tables):

We chose Northwind because it is a well-known simple database with all typical data types (strings, numbers, dates, and Booleans), and small size (the biggest table has a little over two thousand items).

Notice that:

1. The sheets are named after the tables they represent, and
2. The first row on each sheet contains the column names.

Share the Sheet

Once the spreadsheet is ready, click the "Share" button to make it accessible to the application.

We will allow anyone to view the data, and a few specific users to edit the sheet:

Get an API Key to Access the Sheet Programmatically

Our app will need an API key to access the sheet. API keys are unique codes passed to the APIs to identify the calling application. They are used by Google Sheets to track and control how the API is being used, for example, to prevent malicious use or abuse of the API.

API keys for Google applications can be generated at the Google API Console:

You may copy the API key to the clipboard by clicking the button highlighted in the image above. We will need that value in the next step.

When you create an API key, you may restrict it by specifying what types of applications may use it (e.g. specific web sites) and what APIs they can be used with (e.g. the Google Sheets API). This will prevent anyone from using your key from their applications.

Access the Sheet from the Application

Now that the sheet is accessible and we have an API key, we are ready to start using it in our app.

Let us start with a UI that allows users to select product categories from a list. When a category is selected, the app will show a grid with the product information and a chart with product unit prices.

We will start by creating a GoogleSheet object connected to Google Sheets:

 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' ]
 });

The constructor specifies which sheets we want to load. If we didn't specify anything, it would load all sheets.

Next, let's add a ComboBox so users can see and select product categories:

 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;
         });
     }
 });

This is 100% standard Wijmo code, the data could have been loaded from any data source.

When the user selects a category, the combo applies a filter to the "Products" sheet to show only products in that category.

This is the code used to show the products in a 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')
 });

As stated before, this is 100% standard Wijmo code.

We apply a DataMap to the "SupplierID" column so the grid will show company names instead of supplier IDs. The DataMap is based on the "Suppliers" sheet.

Finally, we add a FlexChart to show the unit price for the products:

 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')
 });

And the app is ready:

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

This is a pretty nice start for our app.

Allow Specific Users to Edit the Sheet

At this point, the app can only show the data. To allow editing, we need to add a user authorization scheme.

Recall that when we shared the sheet, we added edit permissions to a few users. To grant those permissions, the server needs to know who the user is. That's where OAuth comes in. OAuth is an open protocol for user authentication. There are several OAuth implementations, and you can use any of them. The only requirement is you must at some point obtain an accessToken which will be included in our requests.

We will use the OAuth2 class to add OAuth support to our sample. First, we create the OAuth2 object:

const SCOPES = [ 'https://www.googleapis.com/auth/userinfo.email' ];
 const API_KEY = 'AIzaSyCvuXEzP57I5CQ9ifZDG2_K8M3nDa1LOPE';
 const CLIENT_ID = '….apps.googleusercontent.com';
 let auth = new OAuth2(API_KEY, CLIENT_ID, SCOPES);

The SCOPES variable tells the server which services we want to use. In this case, we are only requesting the "email" scope, since that is all we are interested in. The information we will be accessing belongs to the app, not to the user.

The API_KEY and CLIENT_ID allow OAuth to identify our application.

Now that we have an OAuth2 object, we can use it to provide a button so users can log in or out:

 // button to log in/out
 let oAuthBtn = document.getElementById('auth_btn');

 // click button to log user in or out
 oAuthBtn.addEventListener('click', () => {
     if (auth.user) {
         auth.signOut();
     } else {
         auth.signIn();
     }
 });

Now that users can log in and out, we must listen to the userChanged event to update the button and the Spreadsheet's accessToken:

 // update button/sheet state when user changes
 auth.userChanged.addHandler(s => {
     let user = s.user;

     // update button caption
     oAuthBtn.textContent = user ? 'Sign Out' : 'Sign In';

     // update Spreadsheet access token
     gsNWind.accessToken = user ? s.accessToken : null;
 });

And that is it. Now users can click the button to log in. If the login succeeds, the server will use the accessToken to identify the user and grant or deny permissions so the users we selected when we shared the sheet will be able to edit the data.

Authorized users can edit the data on the grid and the changes will be applied to the Google Sheets object. The GoogleSheet object supports editing, adding, and deleting items as usual.

Try the Live Demos

• Google Sheet JavaScript Demo
• Google Sheet Angular Demo
• Google Sheet React Demo
• Google Sheet Vue Demo