Update & delete data in Google Sheets with the Adalo-created app
This piece shows how to use PATCH and DELETE methods on Google Sheets data in a no-code mobile app built in Adalo
We in SheetDB.io constantly work on new integrations and features of our app in order to make it useful for as many people and businesses as possible. What SheetDB does is turning a Google Spreadsheet into a JSON API which then can work with different services, tools and programming languages.
This is a second part of the article, cited and linked below which showed how to create a mobile application connected to your data stored in Google Sheets. I recommend reading the first chapter before going through this one.
Adalo is an online application creator, and by combining its forces with SheetDB app, it enables you to make applications on iOS or Android, as well as desktop web apps, which are connected to your data stored in Google Sheets. In this way it’s much easier to add, delete and make changes in records at will. Moreover, all of those actions have an immediate impact on the application.
Distinguishing users from admins
I decided that in the next step of building my sample application, I would divide my users into two groups with different permissions granted, that were: admins and regular users. That was necessary because I wanted to show how an admin would make changes in the spreadsheet using the app’s features working on the basis of HTTP methods PATCH and DELETE.
In order to do that, I’ve added the Login screen at the beginning of the app’s storyboard, and also the Signup screen, just to make it more realistic. They are available as template screens, so implementing them is swift and easy. Between the Login screen and the first screen visible for either a regular user or admin, I’ve put a blank screen with no element included and called it the Redirect screen. What’s important, it has to be linked from the Login screen and switched to Home screen in its settings, so that it’s the first thing running after logging in - without any elements added it will be invisible, but also have an important role.
I already had three users created before so I’ve assigned them different roles by adding a new property of a True or False type, called IsAdmin, to the Users Database Collection. I’ve also had to tell Adalo who is who by setting correct values for each record in that Collection. To make the Redirect screen recognize a user’s status, I’ve added two links: one to the screen a user should see and one directing to the admin’s screens. The trick is to change the advanced setting of both of those actions, from happening Always to Sometimes and specify that it’s done when the user’s IsAdmin property is false or true - depending on how you set the initial values on users.
Refining the admin’s storyboard
My Admin screens are supposed to enable editing some of the items’ details and deleting albums from the database at will. Thus, they include EDIT and DELETE icons and buttons, as well as the Update form which I’ve later narrowed down to three editable details: album’s price, genre and description. Also there are two Success screens to be shown after fulfilled actions.
To make the existing Collection work not only for reading or creating data in the spreadsheet but also for updating and deleting them, I had to make small changes in my Library Collection’s endpoints. While editing the Collection’s settings, the second step enables you to define proper endpoints for each of the possible requests. I needed to change the pre-generated requests for Update and Delete by putting the actual column name for my ID before the automatically given value in curly brackets. That’s how they were changed:
For the Update, from:PUT https://sheetdb.io/api/v1/1zop8nk7x074o/{{id}}to:PUT https://sheetdb.io/api/v1/1zop8nk7x074o/ID/{{id}}
And for the Delete, from:DELETE https://sheetdb.io/api/v1/1zop8nk7x074o/{{id}}to:DELETE https://sheetdb.io/api/v1/1zop8nk7x074o/ID/{{id}}
After sending a test request, I was able to implement proper actions correctly, however Adalo resets requests so you can’t see your changes any more.
Now I’ve been able to link my Admin screens and their icons and buttons with one another back and forth, into a logical path. Also the form has had to be applied to the Library Collection and set to the Update Current Library option, so that it makes changes in the spreadsheet, where the data are stored. Only after specifying the Collection, I could pick which columns were to be editable.
The last thing to be done was the DELETE icon and button. To both of them I’ve assigned the Delete Current Library action. After that the Adalo’s Preview shows the app’s newly installed features work correctly: the DELETE options do erase records from the spreadsheet, whereas the UPDATE form does change the editable details. A full success!
Summing up
This time again, I hope that this sample project will help you create your own applications with the use of Adalo while empowering them with the SheetDB’s API and thus, integrating with Google Sheets. I’ve also described this project in the YouTube video titled: Use UPDATE & DELETE requests on Google Sheets data in Adalo-created app, so you may go through this with more detailed visuals.
If you’ve enjoyed this piece, read my other posts on SheetDB’s blog, revolving around our application. Also, check out SheetDB’s YouTube channel where a new video on the app’s features is posted once in a while. If you have any questions, email us at support@sheetdb.io or reach out to us on Twitter @SheetDB_io. We’d love to hear your opinion on our work and/or ideas as to what else you’d like to use SheetDB for, because your voice matters to us greatly.
