How do you create an online data entry form that can perform CRUD operations in Google spreadsheet?

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:

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.

• In cell A1 we have an array formula that automatically fills column A. This enables us to follow the same list principle as in AI2, whereby if you delete a record all the indexes move by one from below. Leave this formula here, and do not enter any data directly into column A, otherwise the indexing will break.

• This array formula relies on there being content in every row of column B. Make sure this is so, or edit the formula where “B2:B” to a column that has entries.

• Keep your heading labels short, abbreviate if necessary, working to a max of 6 characters. this will help with layout and alignment in the mobile app. Also best to have no spaces.

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.

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:

• These scripts are dynamic so should work with any number of columns and rows in the google sheet.

• Remember to always re-publish your script to a new version if you make any changes.

• When publishing, you need to run the script as “you” but allow access to “Anyone, even anonymous“.

• The script includes a “lock” which should prevent concurrent changes. You may need to enable additional API’s with Google for this to work. (Google should tell you what to do!)

So how does it all work ?

• We first create the doGet and do Post functions (I use both in case for some reason one doesn’t work, doGet generally works in a browser, doPost works for AI2).

• We then have a responseHandler function – what to do when we receive the http request

• The web app is activated when the url to the webapp is called with parameters. Each action available in the web app is summoned with the “func” parameter. There a six func parameters to choose from:

  • CREATE – to create a new record

  • READALL – to call all the records

  • READRECORD (not used)

  • READQUERY – to query (with SQL) a subset of records

  • UPDATE – change a record

  • DELETE – remove a record

• The last two activities make use of the “id”, hence its importance

• After each activity a response is returned, this is either data or a message. If a valid func request is not made, then an error message is returned.

• The job at the AI2 end is to build the url required by the web app. Here is an example:

  • We want to create a new record

  • We have our script url: https://<scripturl>

  • We have our first parameter: ?func=CREATE (note, no quotes!)

  • The AI2 app generates the remaining parameters:

    • &name=Bob%20Simmons&phone=891%202340%207745

  • We end up with:

    • https://<scripturl>?func=CREATE&name=Bob%20Simmons&phone=891%202340%207745

  • Note the app needs to html encode the parameter if there are spaces in the content with %20

  • For testing this can be pasted into your PC browser address bar

  • The CREATE part of the web app script does the rest:

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:

• The app was developed on version n174 native MIT Ai2 framework, using Companion App 2.50, and a genyMotion Emulator.

• I used responsive sizing and the Device Default theme.

• The app was also tested on an HTC One 8 and Google Nexus 7 real devices.

• The example project is dynamic so can accept and work with a dataset of any shape and size (device memory willing!)

• For specific, unchanging needs, the blocks can be re-written for a set number of columns

• The app will always send an entire record with each header and field for creating and updating. Always ensure that they are present in the url.

• The app only sends the record id for deleting

• The dataset is only ever temporarily stored on the app, the root/master dataset is on the google sheet (google sheets has an excellent version history too)

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.

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

SCRIPT

INSTRUCTIONS

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

How do I create a Web input Form in Google Sheets?

How do I create a Form in Google Sheets?

How do I create a data entry Form with Google HTML service and submit data to Google Sheets?