Google Sheets

Using Google Sheets as a data source for your Softr app.

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.

Connecting Google Sheets

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.

Selecting a data source
Selecting a data source

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.

Selecting a Google account
Selecting a Google account

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.

Providing Softr with the required permissions
Providing Softr with the required permissions

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.

Linking a sheet from a List block
Linking a sheet from a List block

Mapping fields from Google Sheets to Softr

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.

Field names and values on Google Sheets
Field names and values on Google Sheets

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.

Mapping fields from Google Sheets to Softr
Mapping fields from Google Sheets to Softr

Record ID

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 Record ID column created inside the sheet
The Record ID column created inside the sheet
💡
Make sure to have the required edit permissions for your Google sheet so that Softr can add the Record ID column and add new values as your database grows. Also, make sure not to delete the Record ID column from your database.

Field types

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.

Image, File, Video/Audio

For these field types, which are included in List, Table, and other blocks, you need to provide a URL of the file instead of actually uploading it, as you can’t upload a file in Google Sheets.

💡
If you need to add multiple files (e.g. multiple images for the Image Gallery field type), you should separate the URLs with a comma.

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.

The Image field on Google Sheets
The Image field on Google Sheets

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.

Image field mapped from Softr
Image field mapped from Softr

Google Drive images

If you want to use images from Google Drive, you need to modify the sharing URL to be able to use it with Softr (or any other site). By default, your sharing URL will have the following format:

https://drive.google.com/file/d/0B8wwyaryznl-OGQ3MUo0Z2thdmc/view

What you need to do is copy the ID from the original URL (the characters between the /d/ and /view), and use it in a different format:

https://drive.google.com/uc?export=view&id=0B8wwyaryznl-OGQ3MUo0Z2thdmc

Tag

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.

“Departments” column in Google Sheets
“Departments” column in Google Sheets

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

Tag field displayed on Softr
Tag field displayed on Softr

Rating

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.

Rating column in Google Sheets
Rating column in Google Sheets

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

Displaying the rating field on Softr
Displaying the rating field on Softr

Linked Records

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 Manager IDs are set to the Softr Record IDs of other users.
The Manager IDs are set to the Softr Record IDs of other users.

Checkbox

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.

“On Vacation” column in Google Sheets
“On Vacation” column in Google Sheets
💡
Please, note that the “true” and “false” values are not case sensitive, as Google Sheets will automatically convert them to all caps in any case.

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

Mapping the “On Vacation” column on a Table block in Softr
Mapping the “On Vacation” column on a Table block in Softr

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.

Submitting a form with a checkbox to Google Sheets
Submitting a form with a checkbox to Google Sheets

Dropdown field in a form

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.

💡
As of now, the Dropdown form field is not supported when submitting data to Google Sheets. However, support will be added as part of the upcoming product releases.
 
Did this answer your question?
😞
😐
🤩

Last updated on December 6, 2022