Create a SaaS using Retool, Google Sheets, and SaaSBox

Introduction

This tutorial covers how to create and launch a SaaS business using Google Sheets, Retool (a nocode tool) and SaaSBox (your SaaS storefront that handles landing page, login and payments)

This tutorial is aimed at non-programmers / non-technical people who have some exposure to nocode tools and want to sell data that they curate in spreadsheets.

The final solution looks as follows:

Your data SaaS application

Retool replaces your backend logic by allowing you to build online applications by drag & drop.

Google sheets in this tutorial becomes your database so you can edit and improve your data.

SaaSBox handles payments, user login and sharing user information with Retool, as well as an administrative dashboard for your service, landing page, and the application dashboard.

Getting Started

Getting the Google Sheet

Let's start with the Google Sheets. Copy over the Google Sheet that is shared here to your own google spreadsheets folder: https://docs.google.com/spreadsheets/d/1ebGZ6-ivf_3woBGC4Kz_3217DhjGsefoRu_5iP3nuFQ/edit#gid=0

This is a spreadsheet of the VC funds that have raised less than 200M since 2011. It has real data curated by Shai Goldman.

Creating the Retool Application

Retool is our application component. It serves the purpose of binding data to user interface widgets and creating an application with them.

Sign up to for Retool here: https://retool.com. The lowest paid account allows embedding the application so it makes sense to start a trial on it ($10/month)

Setting up the Google Sheet Access

Inside Retool:

1. Select Resources > Create New > Google Sheets

2. Name the resource as VC_Funds_Data

3. Authorize Retool to access to your Google Sheets. This will register Google Sheets as a new data source

4. Save the resource. That's it.

Creating the Retool Application and Binding the Google Sheet to a Table widget

1. Go to Apps > Create New, and name it as Retool_SaaSBox_App

2. Inside the app, drag and drop a Table widget from the side pane.

3. When the table is selected, under the "General Tab" at the bottom of the page, go to the Resource: field.

4. Select VC_Funds_Data that we have created earlier as the resource. Now let's configure the spreadsheet information in the rest of the form fields:

   1. For the Spreadsheeet, copy and paste the spreadsheet link you have from spreadsheets.google.com when the spreadsheet is open, in our case this is the link to "your" copy of the spreadsheet we covered in the first section.

   2. Sheet Name: Go to your Spreadsheet and at the bottom left corner find the tab that displays the VC funds data and rename it as VC_Funds_Data_Sheet

   3. Add VC_Funds_Data_Sheet or the matching name you used, as the Sheet Name inside Retool.

   4. For "Data Range" select "Use A1 notation". What this means is, you would provide the top left corner cell coordinates (row, col) and bottom right corner cell coordinates (again row, col identifiers) for where your table is located inside the sheet.

      Go back to your sheet to identify the top left, and lower right corners, and add it in the format, e.g. in the case of this table as of this writing, add: VC_Funds_Data_Sheet!A8:H40. Check out the video below to confirm your understanding of how table information is obtained.

   5. Rename your query in the General tab as FetchGS

   6. Click "Save & Run"

5. This won't populate your table with data just yet, as we need to now use the query inside the Data Section of the table.

   1. Select the table again

   2. On the right side under the BASIC title, find the Data section that has some mock up JSON data in it to populate the table.

   3. Delete the text inside the Data section. Add {{ FetchGS.data }}

6. That's it. Your table should now show all the table data fetched from our spreadsheet, in the case of our example, the VC Funds data.

Creating a Retool app and binding Google Spreadsheet data to a Retool table widget

Setting up SaaSBox

SaaSBox serves as the SaaS storefront. It serves the landing page, signs in users, provides subscriptions, and the dashboard where we embed the Retool application.

1. Sign up for one of the plans at https://saasbox.net/pricing and follow the steps to launch an instance.

2. Click on domains > subdomain tab in the admin dashboard and set up a subdomain. We use my-retool-app.saasbox.net in our example

3. Click on Inbound API Key section > Click on Generate API Key

4. Click on Outbound JWT secret > Click on Generate JWT Secret

Set up the authenticated API call to SaaSBox from Retool to fetch the user

This is the final part of our tutorial where we embed the Retool application inside SaaSBox user dashboard area, where your users would go to after they pay for your SaaS.

SaaSBox can bundle user data and their subscription information in one structure, and securely share it with your Retool application.

This way, your Retool application can serve data back to the user according to who they are and what subscription plan they are on. For example, if they purchased an add-on from you, you can find out about this inside Retool and display extra information to the authenticated SaaSBox user inside the Retool application.

Let's set this up.

Set up Retool API Resource to connect to SaaSBox

1. Inside Retool, go to Resources tab > Create New > REST API

2. Under Configure REST API, name the API as: Fetch SaaSBox User

3. For the Base URL field, go back to your SaaSBox application, and copy its address from the browser url bar, and paste it. In our example, Base URL: https://my-retool-app.saasbox.net

4. This is an authenticated API so scroll down further to find the Authentication dropdown menu, and change its setting from None to Basic Auth.

5. This will reveal 2 additional fields, Basic auth username and password.

   1. Go back to your SaaSBox application > Inbound API Keys

   2. Copy the Application ID and paste it in the Basic auth username field.

   3. Click on Generate API key if you have not done so already.

   4. Copy the API Key, and paste it in the Basic auth password field.

   5. Make sure to copy the exact IDs. (See below video guide for the example).

6. Click on "Create Resource"

7. That's it, your authenticated SaaSBox API connection is ready.

Set up Retool user Avatar component and enable it to fetch SaaSBox User Data

The "Avatar" component normally populates with the user information already visible to Retool as the logged in user. What we will do is replace the data here with the SaaSBox user data, so you don't have to deal with any authentication related work, just log in users to SaaSBox.

What's more, the user data shared with Retool will include the plan data, so you can handle users on different plans, or have add-ons separately inside Retool.

Setting up the Avatar component and creating the FetchUser query

1. Go to your Retool app > Select Edit App

2. Drag the table we created earlier down in the application area to make room for another widget (See video below)

3. On the right pane, select "Components" scroll down to select the "Avatar" widget, drag and drop to the left inside your application area, above the table. (See video below).

4. While the avatar component is selected, go to the left bottom pane where it says + New > Resource Query and click on it. This will add a new resource Query2.

5. Rename it as FetchUser by clicking on the name "Query2"

6. On the right under the General tab, find the "Edit Resource" gray button and click the down caret 'v' to view the option list and select a different resource.

7. Select the option Fetch SaaSBox User (restapi)

8. Now under the General tab you will see REST API options to update. Change the action type to POST

9. Edit the API path to api/userByOTC. The complete url of the API should read: https://my-retool-app.saasbox.net/api/userByOTC

10. Scroll down to the Body option. Select JSON if not selected.

    1. For the key field enter: otc

    2. For the value field enter: {{ urlparams.otc }}

11. You can now click Run, but the API won't work just yet.

12. Click on the avatar again, and on the right pane where it says BASIC replace fields as follows:

    The expression to get the fullname is as follows:

    {{ FetchUser.data.user.firstname + " " + FetchUser.data.user.lastname }}

13. Image URL: {{ FetchUser.data.user.avatarUrl }}

14. Fallback text, use Fullname, i.e. the expression:

    {{ FetchUser.data.user.firstname + " " + FetchUser.data.user.lastname }}

15. Label, also use fullname:

    {{ FetchUser.data.user.firstname + " " + FetchUser.data.user.lastname }}

16. Caption, use email:

    {{ FetchUser.data.user.email }}

We are all set for fetching user data from SaaSBox!

Embedding the Retool application in SaaSBox user dashboard

Inside Retool application:

1. Click on the blue "Share" button in the top right.

2. From the modal that shows up, select the "Public" tab

3. Click on the "Enable Public link" option

4. Copy the public link for the application you have created.

Embedding in SaaSBox

1. Go to SaaSBox > Page Templates > Default > Edit > app folder > main > edit

2. Replace the text where it says "Welcome" with "Welcome to your Retool Application inside SaaSBox"

3. Delete the line "Your application goes in here"

4. Add an iframe element as follows: iframe(src="<link to your retool application>", width="100%", height="100%")

5. In our application this looks like: iframe(src=" https://saasbox.retool.com/embedded/public/1a05fb84-a32e-46e3-b8f9-d0b8b7dc0409" width="100%", height="100%")

Setting up passing of "One Time Code" to Retool:

When loading the application in an iframe like above, we also need to pass a one time code to Retool that identifies the user currently logged in to SaaSBox. The REST API we set up in Retool will use this to retrieve the user information.

A quick recap on query strings: You can pass parameters in a URL using query strings. The start of the query string parameters is identified by the '?' symbol, followed by symbolname=value patterns, seperated by an ampersand '&' character between each parameter.

1. Go to Page Templates > Default Template > Edit > App > Main > Edit

2. Modify the iframe code to add the following code at the end of the iframe:

   ?otc="+otc

3. Save. That's it. The final iframe will look like as follows:

   iframe(src=" https://saasbox.retool.com/embedded/public/1a05fb84-a32e-46e3-b8f9-d0b8b7dc0409?otc="+ otc, width="100%", height="100%")

   Notice the ?otc=" + otc part at the end of the iframe src.

Viewing the final application

This is the end of the tutorial, if you now click on "Go to App" inside SaaSBox, your Retool application should load with both your user information, avatar, and the data fetched from Google Sheets into the example table.

Retool Application inside SaaSBox, showing SaaSBox user data, and data from Google Sheets

Troubleshooting:

Table data shows, but the user avatar is not populated.

If SaaSBox user data does not show as in the above picture, make sure that the Fetch User Query in Retool has the setting "Run Query Automatically when Inputs Change" as follows:

Check out the video below for the entire guidance on this section of the tutorial, along with this troubleshooting tip.

That's it! In this tutorial we covered how to build a basic datasheet application in Retool and share user data from SaaSBox with Retool. You can now build upon this application with form data, and user-specific data shown for each user.

Next Steps:

Check out the guide on "Frontend Templates" and Frontend CLI sections of SaaSBox on how to completely change or customize your user dashboard to blend it better with the Retool Application - note this might require you to get minor help from a Frontend designer. Contact us in case you need further help or have questions!

Retool has higher levels of plans with better Theming support that would let you seamlessly configure the look and feel of your Retool application and match it with SaaSBox user dashboard.