Google Sheets

Google Sheets is a cloud-based spreadsheet solution by Google that’s being used by millions of people all over the world. Although Google Sheets is not as powerful as Airtable (which was the first data source we introduced for Softr) in terms of data management, it’s a really popular software that you may already be using and is also free. So, let’s see how you can connect Google Sheets as a Softr data source and also discuss some of its advantages and limitations that you should be aware of before getting started.

To start with Google Sheets, you need to go to your Application Settings and navigate to Data Sources. Next, click Connect a Data Source and select Google Sheets from the list.

Further on, you’ll need to log into the Google Account that you want to connect or just select it if you’re already logged in.

In the next step, you need to authorize Softr to make changes in your associated account’s Google Sheets and Google Drive files. So, make sure to check the checkboxes highlighted below. Click Continue as soon as you’re done.

Now, you can select your connected Google Sheets account on any of the dynamic blocks (the ones that work with a data source) such as the List block and choose any of the sheets from your library.

In your Google Sheets database that you’re going to use with Softr, each record or row is supposed to have one or more properties, which are stored under respective columns. So, below, we have a list of project tasks for different employees, and each task has its Assignee, Description, Status, and so on. Thus, “Assignee,” for instance, is the name of the column or field in your database. To use a Google sheet as a data source for your Softr application, you need to organize your database this way. On the very first row, you need to have the names of all your fields (i.e. properties) and then, in the rows below, you can store your database records along with their values under each column.

Further on, when you’ve selected a Google sheet as a data source for your Softr block, you need map some or all fields from your sheet to Softr to specify which fields you want to display and in what way. The next image shows how we’ve mapped the fields from the sheet above in Softr, displaying the Assignee, Description, and other fields for each record in a List block.

As soon as you link a particular sheet to a block in Softr, you’ll notice that a new column will be created automatically in your Google sheet named Softr Record ID. This way, each row (i.e. record) is assigned a unique ID to allow Softr refer to it (e.g. when linking a List to its List Details).

The blocks that work in combination with data sources normally offer a variety of field types for displaying your data on Softr. For example, images can be displayed through an Image field type, while PDF files can be made available for downloading through a File field type. In Google Sheets, there are no field types per se, and each column is characterized by the type of values it contains. As a result, there are a few cases when you need to organize your data in a specific way for Softr to display it correctly. Let’s discuss those cases one by one.

For these types of fields found in List, Table, and other blocks, provide the file URL. Ensure you're using a file hosting service that allows easy file sharing.

Let’s look into this on a specific example. In our Task List sheet we considered previously, we’ve added an Image column with URLs to profile pics. The following image shows what it looks like in the sheet.

Next, we’ve mapped it from a List block connected to our Google Sheets data source, and you can see that the images are displayed within the list as expected.

List, Table, and other dynamic (i.e. working with data sources) blocks have a Tag field type, which can display multiple values at once. In Google Sheets, if you want to map your column to a Tag field, you need to separate the values with commas in case you have more than one value.

Let’s see how this works in practice. In our task list sheet, we’ve created a new column named “Departments” to indicate which departments within the company a given task is part of. As you can see, there are multiple values in each cell separated by a comma.

Now, let’s see how this is displayed in Softr when we map this column to a tag field.

This field type is used to indicate a rating from 1 to 5 stars. So, in order to use it with List, Table, or other blocks, you need to have integer values from 1 to 5 in Google Sheets as shown below.

The next image shows what it looks like when you map this field on Softr inside a Table block.

You can create Linked Record fields by using the Softr-generated record ID for a given record. For instance, if you wanted to tell Softr which user record in a sheet was the manager of another. You could set the Manager ID to be the Softr ID of another User. Notice in the screenshot below how some of the Softr IDs of some users are listed as the Manager ID of others. This works across Sheets since Softr Record IDs are unique values.

The Checkbox field type can be found in List, Table, and similar blocks as well as within the Customizable Form. Let’s consider each of those cases.

So, let’s say we have an “On Vacation” column in our task list in Google Sheets (indicating if the task assignee is currently on vacation) and want to display the values through checkboxes in a Table block in Softr. To do that, we need to use “true” or “false” values. “True” is for the checked state, while “false” corresponds to the unchecked state of the checkbox. The image below shows the values we have in Google Sheets.

Let’s map the field on Softr and see what we got.

Similarly, when you submit a checkbox field through the Form that’s linked to Google Sheets, you will get either TRUE (checkbox checked) or FALSE (checkbox unchecked) values in your sheet. Below, you can see how we add a checkbox field to the form and map it to our “On Vacation” field in Google Sheets, doing a test submission at the end.

The Customizable Form block has a Dropdown field type which shows multiple values within a dropdown, one of which can be selected by the user.