Interactive spreadsheets 2: checkboxes

Interactive spreadsheets 2: checkboxes

Right, so in part one of this series we covered buttons, so not it’s time to tackle the next interactive element: checkboxes!

I think it’s safe to assume that we’ve all used a checkbox before, digitally or physically (on paper that is). But, when you use dashdash in combination with checkboxes you can achieve some very powerful results in your spreadsheets - something you probably have already experienced pretty much any time you’ve used one of our spreadsheet templates.

In this tutorial, we’re going to cover:

  • adding checkboxes
  • checkbox logic
  • how checkboxes, formulas, and buttons can interact
  • creating an interactive spreadsheet with buttons, checkboxes, and formulas.

Let’s get started :sunglasses:!

Adding checkboxes

To add a checkbox to a cell:

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

Done!

Adding a checkbox 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 Checkbox , and press Enter .

Checkbox logic

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.

In the example below, we want to display True! Checkbox checked whenever the checkbox is checked, and False. Checkbox not checked whenever it’s not. All we need to do is use a simple IF() function:

=If(B2=TRUE,"True! Checkbox checked","False. Checkbox not checked")

Now that we’ve covered the basic logic of checkboxes, let’s have a look at how to combine them with a few different functions :muscle: .

Combining checkboxes with functions

A simple implementation of using functions with checkboxes is to count how many are checked. All we need to do is count how many of the cells are TRUE.

Cell Content
B2:E2 Checkboxes
C4 No. Checked:
C5 % Checked:
D4 =COUNTIF(B2:E2,TRUE)
D5 =COUNTIF(B2:E2,TRUE)/COUNTA(B2:E2)

And of course, we combine our checkboxes with buttons and some Boolean function. For example, let’s say we only want to trigger the contents of a cell only after two checkboxes and a button is clicked. We just need to use an AND() function to say: “If the button is clicked and checkbox one is checked and checkbox two is checked, then do this, otherwise, don’t do anything”.

The formula in cell F2 in the example above is:
=IF(AND(C2=TRUE,D2=TRUE,E2="Yes, log it already!"),INSERT(A1:B1,A2:B2,A6:B6),"Not logging anything")

:bulb: If you’ve ever used the Send SMS with Infobip template, or pretty much any other dashdash template, you’ll know that using checkboxes with buttons or formulas are a winning combination - you can introduce some very complicated logic into your spreadsheets.

Right - and now that you’ve digested all of that, let’s create an interactive spreadsheet with checkboxes!

Interactive spreadsheet example

So in this example, we’ll make use of the same Crunchbase function that we used in part one of this series. But, we’re going to add some more logic into the mix just to make things a bit more interesting.

Set up a new view with the following content:

Cell Content
A1 Last Funding Date
B1 Last Funding Type
C1 Last Capital
D1 Total funding
E1 Country
F1 Industry
G2 Get Data button
F4 Schedule Function
G4 Checkbox
F6 Auto Log Top 10
G6 Checkbox

It should look a little like this:

Now, the logic we want to use in this example is as follows:

:one: If someone clicks the button, we’ll use the SEARCH_COMPANIES_BY_FUNDING_CRUNCHBASE() to get all the companies matching the criteria. However, if someone checks the Schedule Function checkbox, then it’ll execute the function automatically every hour - the user won’t be able to manually execute the function (and a message will appear explaining why)

:two: If the user wants, they can automatically log the top 10 results each time the function is executed.
:memo: You’ll need to create a Log view for this part.

To get the logic for part one:

In cell H2, paste in the following formula:

=IF(AND(G2="Get Data",NOT(G4=TRUE)),OVERWRITE_DATA(PARSE(SEARCH_COMPANIES_BY_FUNDING_CRUNCHBASE(A2,B2,C2,D2,E2,F2),"organizations"),A9:9),IF(G4=NOT(TRUE),"","You have scheduled the search."))

Now in cell H4, this one:

=IF(G4=TRUE,SCHEDULE(OVERWRITE_DATA(PARSE(SEARCH_COMPANIES_BY_FUNDING_CRUNCHBASE(A2,B2,C2,D2,E2,F2),"organizations"),A9:9),"every hour"),"Function not scheduled")

And now the logic for part two:

In cell H6, paste in the following formula:

=IF(G6=TRUE,INSERT(A9:G9,A10:G20,'Log'!A1:G1),"Not logging information")

If you’re not sure how these functions work, check out our JsonPath, Boolean, as well as Automation part two and three articles. If you’re still a little lost, just leave a comment below :wink:.

Great, now if you’ve entered all that correctly when you enter some search parameters and press the Get Data button, you’ll get a whole list of companies. And then if you check the Schedule Function checkbox and try to click the Get data button - we should see that it won’t let us.

Get data and schedule example"

Lastly, if we check the Auto Log Top 10 checkbox, we should see that our data is automatically inserted into our Log view.

Auto Log checkbox example

Pretty cool right!? But of course, there’s plenty more to do (as you may have seen in the last animation - there’s a couple of extra checkboxes), so as always, we have some challenges for you.

Challenge: automatic emails and log clearing

Today’s challenge is pretty tricky as it uses three functions we haven’t covered in depth yet: SEND_EMAIL(), RANGE2HTML, and RANGE2JSON(). But don’t worry, we’ll include a helpful hint or two :hugs:.

:one: If a checkbox is checked, we want to completely clear the Log view every day at 09:00. Here, you’ll need to use a little “hack” to clear the table: OVERWRITE_DATA(RANGE2JSON($K$1:$Q$1,$K$2:$Q$2),$A$1:$G$1

Challenge 1 answer

=IF(I2=TRUE,(SCHEDULE(OVERWRITE_DATA(RANGE2JSON($K$1:$Q$1,$K$2:$Q$2),$A$1:$G$1),"every day 09:00")),"Not clearing log")

:two: We want to send emails every day at 08:59 of the logs to ourselves if a checkbox is checked and only if the user is also clearing the log every day. If they’re not clearing the logs, then the user should see a message explaining why they can’t. You’ll need to use SEND_EMAIL() and in the body parameter RANGE2HTML(A:G).

Challenge 2 answer

=IF(AND(I1=TRUE,I2=TRUE),SCHEDULE(SEND_EMAIL("an_email_address","an_email_address","Logs",RANGE2HTML(A:G)),"every day 08:59"),"Select auto clear to send logs")

In the end, it should look a little like this:

This might be a difficult one, but you have all the tools you need to get it done! We’ll post the answer on 2020-08-13T23:00:00Z just before our final part of the series :checkered_flag:.

Summary

OK, so now you can add checkboxes to your arsenal of dashdash knowledge, and as we said before - combined with buttons and formulas, you can create some powerful and logically complex spreadsheets. If you don’t believe me, just have a look at these templates:

Or, if you want to get a bit more creative, you can check out Sourabh’s article on how to transform checkboxes into radio buttons… Yes, that’s right - you can!

Right, that’s all for today! In the final part of the series we’ll cover input boxes and how to use them in your published spreadsheets. Until then folks, build to your heart’s content :rocket:!

2 Likes