Interactive spreadsheets 1: buttons!

Interactive spreadsheets: buttons

You’ve probably used buttons before - they let you add some conditionality that’s only executed after you interact with it.

In this tutorial we’ll cover:

  1. Adding and renaming buttons
  2. Using buttons
  3. Creating an interactive spreadsheet

Adding and renaming buttons

Add a button

To add a button, all you need to do is:

  1. Select the cell where you want the button to appear.

  2. In the editor bar, go to More action elements and select Button.

  3. Enter a button label, and click Create.

Add button example

Alternatively, you can also use our Quick Commands:

  1. Select the cell where you want the button to appear.

  2. Press CMD + K (or Ctrl + K), type in Button, and press Enter.

  3. Click on the button settings Cog icon.

  4. Enter a button label and click Create.

Rename a button

To rename a button, just:

  1. Click on the button settings Cog icon.

  2. Enter a new button label and click Create.

Rename button example

:memo: Note: For now, there’s no way to automatically create buttons based on a formula. If you need to create more buttons (even with the same label), just copy the cell that contains the button, and paste it into your destination cell.

Using buttons

OK, so now you have a button - but you probably want something to happen when that button is clicked, right? For example, say you have a button called Click me, and when that button is clicked, you want to display the text “You clicked me!” in the cell beside it.

Cell Contents
C3 Click me button
D3 =IF(C3="Click me", "You clicked me!", "You didn't click me.")
One function button example

Anything you can do with an IF() function, can be triggered with a button :slight_smile:

Also, you can have multiple actions linked to a button, in different views as well.

Cell Contents
C3 Click me button
D3 =IF(C3="Click me", "You clicked me!", "You didn't click me."
E3 =IF(C3="Click me", 20*3 , "You didn't click me.")
Two function button example

:memo: Two things you need to know:

:one: An inactive button (that is, a button that hasn’t been pressed yet) must always be set to FALSE. If you try to change the value of the cell to TRUE, then you’ll get an error.
:two: When a button is clicked, that cell’s content is equal to the button’s name (and yes, it is case sensitive).

Interactive spreadsheet example

Let’s create a simple spreadsheet that will return companies based on their funding. First, create the following content in a view.

Cell Contents
A1 Last funding date
B1 Last funding type
C1 Last capital
D1 Total funding
E1 Country
F2 Get data button
Spreadsheet view example

We’ll use the SEARCH_COMPANIES_BY_FUNDING_CRUNCHBASE() function, combined with OVERWRITE_DATA to get details about a company into a table whenever the button is pressed. So, add the following formula to your Input view:

Cell Contents
G2 =IF(F2="Get data",OVERWRITE_DATA(PARSE(SEARCH_COMPANIES_BY_FUNDING_CRUNCHBASE(A2,B2,C2,D2,E2),"organizations"),A5:5),"")

Now, let’s see if it works - let’s enter the following search parameters:

Cell Contents
A2 2016
B2 angel
C2 >20000
D2 >30000
E2 Denmark

And now click Get data. You should get something similar to the screenshot below.

However, if we delete the contents in cell C2 - you’ll notice that we get a #VALUE. That’s because SEARCH_COMPANIES_BY_FUNDING_CRUNCHBASE() requires all the parameters (except for country) before it can be executed.

How do we solve this? Well, we can just use AND() to make sure that we only call this function when cells A2, B2, C2, and D2 are actually filled in. And yes, that’s part of the challenge for this week.

Challenge: continue building the spreadsheet

For this tutorial, the challenge is to keep building the interactive spreadsheet according to these tasks:

  1. Prevent an error from popping up when the user clicks the button before all the required parameters are filled in. You’ll need to use the AND() function. Check out this article for more info on how to use it.

=IF(AND(G2="Get data",A2<>"",B2<>"",C2<>"",D2<>""),OVERWRITE_DATA(PARSE(SEARCH_COMPANIES_BY_FUNDING_CRUNCHBASE(A2,B2,C2,D2,E2),"organizations"),A5:5),"Provide all required fields")

  1. Add another optional parameter to the request. For example, Industry.
  2. Using GET_COMPANY_DETAIL_CRUNCHBASE and INSERT_DATA, log the first entry of the results to a different view each time. Hint #1: Use the domain.
  3. With the formula from Challenge 3, use JsonPath make sure you only log: name, city, foundedOn, employeeCount, categoryGroupList, and linkedinUrl.

=IF(G3="Log", INSERT_DATA(PARSE(GET_COMPANY_DETAIL_CRUNCHBASE(B6),"['organizations'][0].['name', 'city', 'foundedOn', 'employeeCount', 'categoryGroupList', 'linkedinUrl']"),'Log'!A3:3), "")

And just to make things a little bit more difficult, we’ll only post the answers to the challenges on 2020-08-11T23:00:00Z before our next tutorial on making your spreadsheets more interactive. But here’s an example of what it could look like:

Summary

Right - so now you know a bit more about buttons, and if you did the challenges, you’ve put into to practice our _DATA and boolean logic functions as well as some JsonPath. With just these simple elements, you can really build some powerful spreadsheets.

Next up in our tutorial series: Checkboxes! Till then, build away :rocket:!

2 Likes