[Guide] RecyclerList - Google Spreadsheet - Custom Search - How to Add/Delete/Update records

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 .

image

1. On Screen Initialize first we call data from spreadsheet and second we Initialize RecyclerList inside a layout

blocks - 2022-06-05T101958.300

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

blocks - 2022-06-05T102116.787

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

blocks - 2022-06-05T113551.805

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

blocks - 2022-06-05T120707.368

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

blocks - 2022-06-05T124943.207

7. Update row

8. Add new record

Every time we add / delete or update a record in google spreadsheet our RecyclerList automatically updates

Test aia/apk

Recycler_gsheet_edit_delete.aia (475.1 KB)

Recycler_gsheet_edit_delete.apk (5.6 MB)

You can see how it works at RecyclerList extension and Google Spreadsheet - How to Add/Update/Delete Records in Kodular

15 Likes

I tried so many ways to edit google sheet. did not get any solution. This is what I want. Thank you so much dora_paz for this nice guide.

1 Like

Excellent guide, Thanks

1 Like

Nice information…:+1:

1 Like

Thank you so much, @dora_paz, for such an informative guide.

1 Like

I was waiting too

Thanks @dora_paz

1 Like

could make this guide with firebase?

Airtable is one way structure but Firebase can define in any structure. So post screenshot of your FB ,

1 Like

Structure of firebase ? If you don’t use rules then it is easy to use web component and get response as json

Hi good morning, I tried to follow your guide but I can’t connect the app to my google sheet.
I followed the guide

but i can’t see the data.
Can you give me a hand please?

Did you publish script ? Did you set Show Lists as Json ?

yes, I did

i think the problem could be SheetID, i don’t know where to find it

this is my sheet

Test your script, do you get data ?

For example my gsheet

Testing script

https://script.google.com/macros/s/AKfycbzshg3NMTgED7wYe17TliNtUeUIdAcJpVFN_6Bb5ZTBQiBfB0Y/exec?FN=readSheet&ID=1C4h7rs7Z9gmW5G5HaeSOTeo3bFtO58FMN1jWAC5Tmuk&SH=Sheet1

But where can I find the SheetID?

1 Like

(Post deleted)…

Now everything works my friend, thanks you are always very kind and helpful

1 Like