Sr. Developer Advocate
This tutorial covers the basics for creating a CRUD (Create, Read, Update, Delete) app using Google Sheets as the datasource.
An Appsmith account
A Google account
Google Sheets is actually our most used datasource in Appsmith! It's great for quickly prototyping an app, or building a PoC (proof of concept) or MVP (minimum viable product).
For this tutorial, we'll be using a mock dataset for an events app at a software company. Start out by copying the sample sheet to your Google account, and give it a unique name so it's easy to find from Appsmith.
Sample Events sheet <= Make a copy
Next, we'll create a Google Sheets datasource in Appsmith, and then add APIs and a table widget to display and edit the sheet data.
Start out by creating a new app, and then add a Google Sheets datasource.
Read / Write / Delete | Selected google sheets
Note: Best practice is to create a new datasource for each sheet, instead of using the Read / Write / Delete | ALL google sheets option.
Keep in mind, if you choose ALL Google Sheets, and you share the workspace with someone else as a developer or admin, they will be able to access all of your Google Sheets!
Next, we'll add an API to get data from the sheet. Make sure to create the new API under the Google Sheets datasource you just created. You can use the [+] New query button on the Google Sheets datasource, or the [+] Query/JS button, then select the Google Sheets datasource.
Pro Tip: You can also use the keyboard shortcut
cmd-shift-+ to add a new API. More keyboard shortcuts here.
You should get back a response with data from the spreadsheet. Now, on the right panel under Add new widget, click the Table binding shortcut. This will add a table widget to the canvas and automatically connect it to the getRows API. Alternatively, you can drag in a table widget, then set the Table Data property to
Notice the toast message about the getRows API running on page load now? Appsmith automatically sets a query to run on page load if it's connected to a widget. This way the widget will have data to display when the app first opens. You can also set a query to run on page load in the Settings tab for that query.
The table widget has an awesome new feature for adding a row directly from the table, instead of using a form widget. First, we'll enable adding a row, then we'll create the new API, and lastly, we'll connect the new API to the table widget.
To get started, select the table widget and find the Adding a row section at the bottom of the property pane:
Then go up to the column settings for the table widget:
Next, create a new API under the Google Sheets datasource.
You should see the data you entered for the new row in the evaluated value pane. Now, head back to the table widget properties to connect the API to the SAVE button. Once the addRow API runs, you'll want to run the getRows API again to update the data in the table widget. This can easily be done using the callback option in the action selector.
Now, head back to the new row you started to add on the table widget and click Save row. You should see the new row added to the sheet, and the table widget updated with the new data.
Next, we'll add a new API for updating a row and use the table widget's row editing feature. This will be similar to the addRow API, but now we must also specify the rowIndex of the row to update. Start out by creating the new API.
Notice how there's a new SAVE/DISCARD column in the table widget now? That gets added when you enable row editing, but you have to configure which action to run when the user clicks save.
Now you should be able to edit a row, then click SAVE to update the sheet and refresh the table widget.
Lastly, we'll add an API for deleting a row, then connect it to a new button in the table widget. Start out by adding a new button column to the table widget, then create an API and link it to the new button.
Now, add the deleteRow API.
Lastly, connect the API to the new delete button.
And that's it! You now have APIs for Creating, Reading, Updating, and Deleting data in Google Sheets; a complete CRUD app without writing a single line of code.
Google Sheets is a great choice for a backend when you need to quickly throw together an app, build a PoC or test out an idea. And Appsmith's native Google Sheets connector makes it easy to quickly build a full CRUD app on top of your sheets data.