Today I will show you how to get data from gsheet, how to use a custom search and also how to add, delete or update records with RecyclerList extension by @zainulhassan . For this I used 🟩 [FREE] Google Sheets for AppInventor (replicant) using Google Apps Script - Extensions - MIT App Inventor Community by @TimAi2 and a google app script bound to my spreadsheet. I used Web App Script by @TimAi2
For those who do not know how to to bound a script please reffer to METRIC RAT AI2 - HOWTO: Create a Google Apps Script Web App bound to a Spreadsheet
I’m using a test google spreadsheet with 5 colums and 1200 rows bound to my script as shown below
Other extension used in project
Image used in project - People icons created by Freepik - Flaticon
Lets start, go to creator and create a new project. Note in order GSAI2 extension to work properly you must set Show Lists As Json in Project Settings
My UI is quite simple, a CV with a textbox used for custom search, a VA used as List Container, a VA used when data is loaded to show Circular Progress and 3 hidden CViews containing buttons and textboxes used as custom dialogs when add/update or delete a record needed
Now let’s go to blocks part
First of all we create three global variables, one will be populated with data from gsheet and two that we will need later in order to get row number and row’s data in order to update it or delete it .
1. On Screen Initialize first we call data from spreadsheet and second we Initialize RecyclerList inside a layout
2. We use when GSAI2 GetFunctionOutput to add data to our global variable. The first if … then condition is used when we received our data and second if … then is used when we later delete, update or add a record to call data again from gsheet in order to refresh our RecyclerList
3. Once we have our data we use on CreateView block to create UI and on BindView to bind data to our list. In order to reduce significantly my blocks I used a schema template
With all the above blocks we managed to get data from spreadsheet create our RecyclerList.
4. Now for the custom search part we need on text changed block and in order to to filter our data a procedure to use with ListUtils. Mine procedure searches column 2 of my database
5. In my example I have two click events, onCardView Click and on CardView Long Click
onCardView Click we simply pass all data from specific cardview to next screen. Using select list RecyclerList Data at index position we always get the right data, filtered or not
onCardView Long Click here we deside if we will delete or update or record in google sheet. In order to do that we have to find the row number in our table
Pay attension on how we set row number. In my case I use +1 because my google spreadsheet contains headers, otherwise you do not need it. I also save row’s data in a global variabel
6. Delete row, all we need to know is row number which is saved from previous blocks
7. Update row
8. Add new record
Every time we add / delete or update a record in google spreadsheet our RecyclerList automatically updates
You can see how it works at RecyclerList extension and Google Spreadsheet - How to Add/Update/Delete Records in Kodular
