Adding action elements to spreadsheets

Learn how to add buttons and input fields as well as the difference between a Form and a Table with live spreadsheets.

Intro

So, you know how to publish your spreadsheet, but, it doesn’t really look like much. Let's see how to make it a bit more interactive for the user by adding some action elements. In this article, we'll learn how to use:

  • Buttons
  • Input fields
  • Forms and Tables

Let's go 🚀!

Buttons

Buttons allow you to execute one or more actions, only when you click them - a simple but very cool tool for any spreadsheet. Using buttons also helps ensure you don’t use up integration tasks accidentally by processing functions when you are not ready.

To show you how to use buttons in your spreadsheets, 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.

  1. Create a new spreadsheet and add the headers to A1, B1 and C1 as you see below, then select cell D2.
  2. In the editor bar, go to More action elements, click on the dropdown arrow and select Button. kb-action-elements-buttons-newUI
  3. Enter Find as the label.
  4. Select Execute custom formula as the action.
  5. In the Formula field, type the following: =SEARCH_COMPANIES_AT_EVENT_CRUNCHBASE(A2,B2,C2)
  6. Lastly, select or type the Result destination as E2, and click Create button. ❇️ And that's it! Let's try it out.

  7. In cell A2, type in Web Summit 2017.
  8. In cell B2, type in >50
  9. Click the Find button you just created. You'll see that E2 now has a {data} cell. Perfect!

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

Input fields let users enter data themselves into a live spreadsheet. In our case, we want users to be able to enter events, employee ranges, and locations. To do that, all we need to do is:

  1. Select cells A2, B2, and C2.
  2. In the editor bar, go to More action elements and select Input field. kb-action-elements-input-boxes-newUI

✳️ Done! Now in the live spreadsheet, users will be able to enter data into your spreadsheet :).

Checkboxes

To add a checkbox:

  1. Select the cell/range where you want the button to appear.
  2. In the editor bar, go to More action elements and select Checkbox.

adding-a-checkbox

A checkbox can have two “states”:

  • FALSE (not checked)
  • TRUE (checked)

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: =FALSE

disabled-checkboxes

Publishing options

Whenever you publish a spreadsheet, you can choose whether a table is a Form or a Table. What’s the difference?

Do you want your users to...Then choose...Good for...
See changes made by everyone?TableTeam reports
See only their own changes?FormCalculators and forms

In our case, we want each of our users to have a separate calculator (that is, we don't want them to see results of other users' searches). So what should we use, a Form or a Table?
📬 Answer: Form

To change whether your table is a Table or a Form, just click the drop-down menu next to the title of your table, and select which option you want.

kb-forms-and-tables-newUI

Form limits

There's just a few things you need to know about Forms:

  • You can’t send data from a Table to a Form.
  • You can’t reference cells in a Form in a Table.
  • Forms do not support REPEAT(), REFRESH(), and SCHEDULE() functions.

But don’t worry - we detect these limitations in real time and let you know if you need to change something.

Up next

So, we’ve got that dynamic interactivity down. Great!. But now, you probably want to know what to do with that data in cell E2. So, in the next part of the course, we'll show you how to manually parse the data you get back.