Integrating SQLite with Google Sheets

INTRO

Having just done a load of work on SQLite, you won't be surprised to find this guide, about how you can integrate SQLite with Google Sheets from AI2! You will be happy that you are able to work with a local database on your device, but you may wish to extract and/or back up the data to an online resource, or import a list or table to your local database, or even use google sheets functions to work with a relational database, on google sheets.

If you have arrived at this page before doing anything with SQLite on AI2, then you should first visit: Working with SQLite in order to get up to speed. This guide will assume you have already done this.

The aims of this guide are:

to demonstrate how to extract a column of data, or an entire table from an sqlite database
how to then upload that data to a google sheet
how to download data from a google sheet and prepare it for SQLite
how to insert your data as a column or table to an SQLite database
finally, and slightly unrelated to AI2, a demo on how to work with relational data on Google Sheets

Throughout, I will work with a simple database that contains a 3 column table, called peopleCodes.db

Extracting Data from SQLite

We already have the tools to do this, we use a SELECT SQL command to extract some or all the data we need:

To extract a column of data

SELECT full_name FROM data WHERE full_name LIKE 'B%' LIMIT 30

This will return 30 names that start with B as a flat AI2 list:

In order to return all the data in the column just use:

SELECT full_name FROM data

Now, we have a problem here. If we send this up to our google apps script using postText, it will create one row, with many columns. We want a column, so we will have to convert this flat list into a list of lists before sending to google. This is simply done:

To extract an entire table of data

SELECT * FROM data WHERE full_name LIKE 'D%' LIMIT 10

This will return the id, the full_name and the code columns where the full_name starts with D as an AI2 list of lists.

This list of lists is all ready to be sent to google.

In order to return the entire table, just use:

SELECT * FROM data

If you have many tables in your database, you will need to extract each one individually.

You will notice the use of the ListFixer block (now incorporated in the SimpleSQLite extension [V3]), because we are getting back a list of lists from the query, and it needs conversion to an AI2 list. ( I could have used it for the first query as well, but this was not needed as the query was returning a flat list).

You might also want to get the column names, and add these to the top of your list:

SELECT name FROM PRAGMA_TABLE_INFO('data');

which returns:

["id","full_name","code"]

Send column or table to google sheets from AI2

We use the web component with the url set to a google apps script web app (I'll get to this in a minute...), and the list data set to PostText. Notice that we set the name of the sheet in the url section.This ensures the data gets sent to the correct sheet in the spreadsheet

Setup a spreadsheet and web app

On google drive, create a new google sheet in your preferred location
We are going to create a web app project bound to the spreadsheet
On the spreadsheet menu bar, click Extensions, then Apps Script
A new project should open, give it a name.
The script used is very similar to one I used here but I have added a few things:

Copy this, and replace all the existing code with it.
Publish as a web app, executing as your google account and accessible by "Anyone" (new editor) / "Anyone, even anonymous"(legacy editor)
Back to the spreadsheet, add a new sheet, and name it Column. Add another sheet and name it DataTable.
We now have everything we need, time to test the upload of data from AI2 to the spreadsheet.

It works..... :)

Download data from google sheet to your App

We need to add another section to the web app for this, we could use a gviz query or export?format=csv, but it is better to return the data as a stringified JSON, which will make it easier to carry out the bracket substitution later....

Add this to the web app (after the last curly brace):

function doGet(e) {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName(e.parameter.sheet);
var data = JSON.stringify(sh.getDataRange().getDisplayValues());
return ContentService.createTextOutput(data);
}

Now republish the web app to a new version.
We can now use the web component "Get" to return the data

Now then, the column data is a list of lists, we do not want this for putting the data back as VALUES in a column in a table in SQLite, so we have to remove all the square brackets, and add back a square bracket at the beginning and end

For the Table Data, we need to do something similar, but this time replacing all the square brackets with rounded brackets, and removing the outer brackets completely

Data is now all prepared for inserting to a table in an sqlite database

Inserting a Column to an existing table

There does not appear to be a method to "insert" a list of column values. For this example I will use UPDATE and the list index number to set the id. This will place the new values from the top of the table. There also does not appear to be a method to UPDATE with multiple values, so we will have to iterate singly.

We need to first have a database loaded and OPEN
Next we create a new column:

ALTER TABLE data ADD COLUMN sheetData TEXT

(read here for more about ALTER TABLE - there are some limitations)
The table called data in peopleCodes.db should now have a new column called sheetData
We can now insert (update) our prepared data:
- The column data is fetched from the google sheet
- the square brackets are removed
- the string is converted to a list
- Iterate over each value of the list, "UPDATING" the column sheetData, where the id matches the item index in the list
- display the updated table

Insert a new Table and add the data from google sheet

Make sure we have the database OPEN
Create a new table called sheetData, with columns for id,full_name,code
For this example, we won't set a primary key, given the id numbers, although sorted, are not consecutive. Neither do we set any constraints - e.g. NOT NULL

CREATE TABLE IF NOT EXISTS sheetData(id INTEGER,full_name TEXT,code TEXT)

Check the table is there with the following two commands:

SELECT name FROM sqlite_master WHERE type='table'

result: ["data", "android_metadata", "sheetData"]

and

SELECT * FROM pragma_table_info('sheetData')

result: [[0, id, INTEGER, 0, null, 0], [1, full_name, TEXT, 0, null, 0], [2, code, TEXT, 0, null, 0]]

Now we can call down the data from the google sheet and apply it to the new table in one hit:
- Fetch data from google sheet
- Replace the square brackets with rounded brackets (note: we do not have to make an AI2 list)
- Insert all the data to the table
- Display the table data

SUMMARY

That more or less concludes the main part of this guide, which hopefully demonstrates how to exchange SQLite data with a google sheet and vice-versa. There are probably many other methods that can be used to achieve the same thing, and I have only really scratched the surface of what may be possible. As I indicated at the top, this is all about backup, modification or maintaining an online reflection of your on-device resource. If your SQLite database is static, or you prefer to use a tool like DB Browser for SQLite then you may not need to use the above. I do hope this has been useful and not too long or confusing ;)

BONUS SECTION

Using a Google Sheet with Relational Data

As promised, i intend to demonstrate how you can use google sheets to carry out queries on relational data. Why? Well, one of the main purposes of a database is to work with many tables and to relate one table's data to another, and to then bring the data together with queries. Google Sheets tends to operate with flat files, because it is not as easy to set up the relationships between tables. However, if you are going to consider using google sheets as a location to store your sqlite data, you may as well be able to query it, right ?

To demonstrate, I have created two sheets one called Bands, which holds a list of rock bands from the '70's, the other called Albums, which holds a list of albums by those bands, but indicates which Band created the album by way of an ID. Here are the two tables:

Bands

Albums

This is how you might set things up in an SQLite database. In SQLite, you might then generate a query on these two tables using a (LEFT)JOIN to return a listing of bands and their albums. The basic query would look something like this:

SELECT Band FROM Bands LEFT JOIN Albums ON (Bands.id = Albums.BandId) ORDER BY Bands.Band

and this would return an output like this:

AC/DC Let There be Rock
Deep Purple Machine Head
Deep Purple Fireball
Fleetwood Mac Rumours
Fleetwood Mac Tusk
Led Zeppelin IV
Wishbone Ash Argus
Wishbone Ash Pilgrimage

To do the same thing in google sheets requires some out of the box thinking, and in fact the main grunt work to return the data does not use the google query language at all! We start off by using a VLOOKUP function in an ARRAYFORMULA - {....} - to return all the Bands related to the Albums. This is then passed through a FILTER function to create a listing of Bands and Albums. Finally use the QUERY function to display the Band first then the Album for each record, and to order them alphabetically. The final function looks like this:

=query(FILTER({Albums!B$2:C,VLOOKUP(Albums!C$2:C,{Bands!A$2:A,Bands!B$2:B},{2},false)},Albums!B$2:B<>""),"SELECT Col3,Col1 ORDER BY Col3")

Once this is done, it is then possible to set up a drop down selector (containing all the band names), and to use the query to return just the albums by a particular band:

=if(K24="","",query(FILTER({Albums!B$2:C,VLOOKUP(Albums!C$2:C,{Bands!A$2:A,Bands!B$2:B},{2},false)},Albums!B$2:B<>""),"SELECT Col1 WHERE Col3 = '"&K24&"' ORDER BY Col1"))