CRUD with Google Sheets, Web App and AI2

See also an updated guide: Google Sheet CRUDQ II 

See also a simplified crud guide: Simple CRUD with Google Sheets and Apps Script

CRUD

That is create / read / update / delete to the uninitiated and the base requirement for the management of the data in a database.  There are many solutions on AI2 already that access remote data and database systems, and I have already explored some of the possibilities for connecting with Google Sheets, but never really put it all together. This effort aims to address that and to provide a single web app, built from google apps script as an interface between AI2 and a Google Sheet. This means you can keep your google sheet private yet provide access to the data via the mobile app.

Working this thing up will require some knowledge of google sheets, google apps script / javascript, and building apps in Appinventor 2.

I was partially driven to this by the lack of usable / functional  / (free) online data storage solutions that everyone can easily use, the upcoming demise of fusion tables and loss of firebase access from Ai2, and as said above, the need to pull various previous efforts together in one place.

We will start with the google sheet, then move on to the web app, and finally the AI2 app. Some rigour will be needed in order for all this to work, so I have set out some requirements and indicators along the way: 

SHEET

Here is my google sheet with the data, simply an id, name and phone number:

Nothing special so far, but let us take a closer look:

={"id";ArrayFormula(if(B2:B<>"";row(A2:A)-1;""))}


A couple of things going on here.


Linked in with the array formula above, you will see that there are no empty rows at the end of the data listing. This is because the array formula “takes over” the entire column. If you programmatically add an entry with “appendRow()”, the entry will be added at the bottom of the sheet / out of view. Remove any empty rows after the last entry. Google adds a new row when there is a new entry.

You can manually add records to the spreadsheet directly, just remember you do not need to enter an id, this will happen automatically.

WEB APP

I created the script bound to the google sheet (makes it easier to find again), you can publish the script as a web app in just the same way as a standalone google apps script. If you prefer, everything will work in the same way if you create a standalone script, but you will need to call the google sheet ID instead of “getActive()”. A few things that will need bearing in mind:

SCRIPT

So how does it all work ?

AI2 APP

Believe it or not, the above was the easy part, structuring the AI2 app to handle all of the above was much harder!

Some things we need to know or be aware of when creating the AI2 app:

SCREENS

BLOCKS

SUMMARY

So there you have it, a CRUD method for google sheets from AI2 using a google web app. Admittedly, the google sheet is only a flat file database, but this is much more accessible than mySQL, and most people on the AI2 forums seem to use flat file of some sort for their work. This collection of tools can be used for just me, or shared with a wide community of users. 

DOWNLOADS

AIA  (my thanks to Danya Solncev for pointing out a problem with international characters)

SCRIPT

INSTRUCTIONS

EXTRAS!!

One user, Amit, asked for an addition to the script to change the content of a single known cell. We added this code to the script:

//CHANGE SINGLE CELL

}else if (e.parameter.func == "ChangeCell") {

var ss = SpreadsheetApp.getActive();

var sh = ss.getSheets()[0];

sh.getRange(e.parameter.A1NotRange).setValue(e.parameter.cellValue);

return ContentService.createTextOutput("Cell Changed").setMimeType(ContentService.MimeType.TEXT);


.....


The two parameters:

A1NotRange would be, for example: "F4"  (column 6 row 4)

cellValue would be, for example: "sometext"


The url would look something like:

https://<YOUR SCRIPT URL>/exec?func=ChangeCell&A1NotRange=F4&cellValue=sometext