You can make your spreadsheet interactive for the user by adding action elements (form fields).
Rows supports the following field types:
- Input fields
- Date Pickers
You’ll find all of these items in the Insert menu:
Buttons allow you to execute one or more actions in your spreadsheet. To learn how to use them, let’s build an app that will generate a list of companies that attended an event.
To build this app, we will use the
SEARCH_COMPANIES_AT_EVENT_CRUNCHBASE() function and execute it when someone clicks a button on the spreadsheet.
- Create a new spreadsheet and add the headers to A1, B1 and C1 as you see below, then select cell D2.
- In the editor bar, open the Insert menu and select Button.
- Enter “Find” as the label.
- Select Execute custom formula as the action.
- In the Formula field, type the following:
- Lastly, select or type the Result destination as E2, and click Create button. ❇️ And that's it! Let's try it out.
- In cell A2, type in Web Summit 2017.
- In cell B2, type in >50
- Click the Find button you just created. You'll see that E2 now has a
Now, if you change A2 to Web Summit 2018, you'll see that nothing happens. That’s because the function will only execute when you click the button.
Input fields let users enter data themselves into a live spreadsheet. In our case, we want users to be able to enter a price range. To do that, all we need to do is:
- Select cells B1, and B2.
- In the editor bar, go to More action elements and select Input field.
✳️ Done! Now in the live spreadsheet, users will be able to enter data into your spreadsheet :).
To add a checkbox:
- Select the cell/range where you want the button to appear.
- In the editor bar, open the Insert menu and select Checkbox.
A checkbox can have two “states”:
So, just like with buttons, you can use checkboxes in conditional functions to trigger some kind of action.
You can also disable your checkboexes by manually adding the states as part of a formula — ex:
To keep dates consistent, it helps to use a date picker instead of having people type the values in manually.
To add a date picker, follow these steps:
- Select the cell/range where you want the date picker to appear.
- In the editor bar, open the Insert menu, hover over the Date Picker item and select Single Date or Date Range.
As you would expect, the Single Date field allows you to enter a single date into a cell.
The Date Range field inserts allows you to insert date range into a cell, in the format [yyyy-mm-dd;yyyy-mm-dd;]
What can you do with a date range cell you ask? Great question!
There are several functions that accept a date range as one of their parameters such as the
If you want to make the date range parameter depend on another cell, it’s important that the range in the reference cell has the required format (‘[yyyy-mm-dd;yyyy-mm-dd]’).
That’s why it’s handy to put a date range picker in the reference cell. It removes the risk that a typo breaks any formulas that expect this format.