Smart Chore List

a group of people standing next to each other

on September 1, 2023

Sorting Out the Chores

I’ve decided that with my kids, getting them to do half their chores is a notable victory. They’re also not stupid. Apparently, chores should be rewarded, since they now know what an allowance is. No allowance…no chores. That being said, I figured there had to be a way to geek this out. Turns out…there is.

So, what we’re gonna do is turn a Amazon tablet, of any flavor, into a wall-mounted chore list. This takes a few different integrations between Google, Dakboard, IFTTT, and Todoist, but once done its a handy thing. I can track daily to-do lists for each kid, they can see they’re progress for the week, and they can see a total amount they’ve earned.

What You'll Need

Normally I stay away from product that require a subscription, but for this to work you’ll need a basic Dakboard subscription to unlock some of the customization features. I’ll explain Dakboard in a minute. Here’s what you’ll need:

You’ll need to be comfortable with Google sheets (or some knowledge of Excel), and have some comfort with the developer’s console. If you don’t, that’s okay, I’ll walk you through it. This guide was compiled from several different walk-throughs, so everything will be in one handy place.

Set up a list

First things first. Let’s get a list created. Todoist is a handy way to keep track of projects and tasks in addition to chores. It’s got a mobile app and desktop app, so you can check off tasks in the moment, wherever you may be. However, in this case we’re gonna use it to focus some wayward children. You only need a free account for this to work. Once you have an account, start by creating a new project.

Start by creating a new project. Make sure the name of the project is the name of the kid you’re tracking. Additionally, the type of project will be a list. Once you have your project created, we’ll start by adding tasks.

Add a task by clicking the “Add Task” button to bring up the task dialog.

Next, give your task a name, e.g. “Put away toys” or “make the bed”. Include the frequency in the description.

Cool Tip Todoist can translate frequency from your task. So, if you enter "make your bed every day" Todoist will create a repeating task that reoccurs every day.

Keep it Simple Don't overcomplicate task names. You'll be importing these names into Google Sheets later, and the equations will match the task name in order to count successful completions. Keep it simple, i.e. "make your bed" or "wash the dishes".

Wash, rinse, and repeat until all tasks are entered for that child. If you have more than one child, create a new project for each child.

Create a Filter

The next step is to add a filter. This is important, as when we start pulling things into Dakboard we’ll use filters to display each child’s list for that day. To create a filter, click the “Filters & Labels” option in the menu. This step is actually pretty easy, since we’ll use the Filter Assist function to create our filter. Tap the “Try it” option in the add filter popup and type in something like this: “tasks due today and overdue in project xxxx“. Replace xxxx with the name of the child assigned to that project. Create a filter for each child project.

Link Todoist and IFTTT

The next step is to link Todoist with IFTTT. IFTTT stands for “IF This Then That” and is an online automation platform designed to integrate various different services in order to complete specific actions. Similar to Node-RED, IFTTT takes different apps, programs, or automations and connects them to perform specific actions. It is cloud-based, so no internet means no automation.

Start by creating an account. Once you’ve got an account, hit “Create” to make your applet. Applets always start with a trigger, or “IF”. Search for “Todoist” and the trigger will be “New completed task”. Follow the prompts to link the accounts, and for projects to watch select “Any Project”.

The next part of this automation is to add a row to a spreadsheet, or in this case a Google Sheets spreadsheet. So, for the “Then” action, search for “Google Sheets”. The action is to “Add row to spreadsheet”. Link your accounts if not already done so, and create a spreadsheet name for your Todoist tasks.

Now, the next part is important. It tells IFTTT exactly how to split up the data it receives from Todoist. Enter the following into the formatted row field:

{{TaskContent}}|||{{Project}}|||{{CompletedAt}}|||{{Labels}}|||{{Priority}}|||{{LinkToTask}}

Finally, if you want this placed in a specific folder define the path. I placed my in a folder specifically for Todoist. Save the applet and you’re done with IFTTT.

Setup Google Sheets

Next up is to create a spreadsheet in Google Drive that will handle all of the information. We’ll keep this sheet separate from the sheet created by IFTTT, but you can combine them if you want to. Start by making a new sheet and giving it a name (“Chore tracker summary” for example). You’ll need to create 4 sheets in this one:

In Cell A1 in the Raw Data sheet, enter the following:

=(IMPORTRANGE("https://docs.google.com/spreadsheets/d/todoistsheetID/edit#gid=0","sheet1!A1:E13000"))

Sheet ID To get the Todoist sheet ID, in a new window open the sheet created by IFTTT. If you don't have a sheet yet, you'll need to complete a task in one of the projects to force an update. The sheet ID is the string of numbers and letters between the "/d/" and "/edit" parameters of the html address. As an example, look at this address:

https://docs.google.com/spreadsheets/d/xouoin%oi1234/edit#gid=0

The part you're interested in (and note that this is a totally fictitious string for a sheet ID) is:

xouoin%oi1234

That section identifies the specific sheet you'll need to access.

This formula imports data directly from the Todoist sheet as it is entered. In cell F1 enter “1” and drag this value down as far as possible. This assigns a numerical value that will be used later (in other words, it “counts’ the task). In cell G1, enter:

=split(C1,",")

This splits the date in column C up. Drag this formula down as far as you can. Finally, we need a week number to help with calculations later. To do this, in I1 enter:

=if(iserror(weeknum(G1))=true,"",weeknum(G1))

Drag this formula down column G as well. It inserts the current week number of the year based on the date in column G. If column G is not yet populated, the ” ” parameter displays a blank cell. You’ll have a tablet setup as below (note that column D will be blank, since the date and time in column C will span both columns):

A	B	C	D	E	F	G	H	I
Chore	Kid	Date and Time		Priority	1	Date	Year and Time	Week

Figure up the money

Head over to the money amounts page. This page will tally up the chores for the specific week and total the money for each unique task. In cell A1 enter “Today’s Date” and in cell A2 enter “Today’s Week”. In cell B1 enter:

=today()

This populates today’s date, which will be used to determine the week. So, in cell B2 enter:

=weeknum(b1)

Now we need to determine monetary amounts for each chore. Skip a row, and in row 4 use the following as headers for a chart, going across:

Project (or child name)
Per Chore
This Week Count
This Week Money
Last Week Count
Last Week Money

In column A, under your child’s name, start listing each chore entered into Todoist. Note that the chore name entered here must exactly match the chore name imported from the Todoist sheet into the Raw Data sheet. If you’re unsure, complete a task in Todoist to see how it looks in Google sheets. Add each chore to column A.

Now, in column B, beside each chore, add the monetary amount you want to reward for each completion. Do this next to each chore.

Absolute Versus Relative References If you're not comfortable with, or haven't worked much with spreadsheets, you need to be introduced to absolute and relative references. Note the following:

$A$1
$A1
A$1
A1

Each of these is distinctly different. If included in a formula, that you then drag across multiple cells, each of these will behave differently. The "$" directive locks the cell reference and tells the calculation "don't move this cell or column". So, putting "$" before both the column and cell number locks the reference to 1 cell and 1 cell only, regardless of where the formula is placed. Likewise, placing the "$" before only the column locks only the column, not the row. If no "$" is present, nothing is locked. So if you have the following formula in cell C1:

=A1*B1

Now, if you drag that formula down 1 row to C2 you will end up with:

=A2*B2

On the other hand, if you change the formula to:

=A$1*B1

And then you drag the formula again from C1 to C2, you'll have:

=A$1*B2

References are directional, so since you drug the formula down (and not across), but locked the row in the formula to row 1, it will still use A1 but will change the second part to B2 (since you didn't lock the reference in column B). If you drag across instead of down, the column will change versus the row.

In column C we’re going to caculate the number of times each chore is completed for the current week. Enter this formula in C5:

=sumproduct(('Raw Data'!$B$1:$B$13000=$A$4)*('Raw Data'!$A$1:$A$13000=$A5)*('Raw Data'!$I$1:$I$13000=$B$2),('Raw Data'!$F$1:$F$13000))

Break this Down Let's break this formula down a little so you can see what each part is doing.

=sumproduct

This function is going to sum all the products of elements in an array of objects. The next set of elements defines the array of information that it will look at to get the summation.

('Raw Data'!$B$1:$B$13000=$A$4)*('Raw Data'!$A$1:$A$13000=$A5)*('Raw Data'!$I$1:$I$13000=$B$2)

This set of directives defines the array of info we're summing up. It looks at cell A4 in the Money Amounts sheet and compares it to the value in column B in the Raw data sheet. If the values match, it continues. Next, it looks to see if the value of cell A5 (notice the "$" only locks column A, not the row) matches the data in column A of the Raw Data sheet. Again, if the values match, it continues. Finally, it looks to make sure that the week number in B2 (note the absolute reference again) matches the week number in the Raw Data sheet. In other words, if the child in column B of raw data matches the child defined in cell A4 of the money amounts sheet, and the chore defined in column A matches the chore defined in column A of the raw data sheet, and if the week number in B2 matches the week number in column I of the raw data sheet, then the calculation continues.

('Raw Data'!$F$1:$F$13000)

Finally, this part of the formula simply adds up the "1s" in column F of the Raw Data sheet if the previous directives found a match. This formula allows for summation of repetitive tasks.

Drag this formula down column C so it populates beside each chore. Next, we’ll calculate the earnings for this week. In cell D5, enter this formula:

=C5*B5

This simply takes the number of occurrences calculated in column C and multiplies it by the reward per completion in column B. Drag this formula down to populate for all chores.

Now we’re going to do the same to retain and calculate the reward for the previous week. The formulas are the same, just that the formula in column E subtracts 1 from the current week function. In cell E5, enter:

=sumproduct(('Raw Data'!$B$1:$B$13000=$A$4)*('Raw Data'!$A$1:$A$13000=$A5)*('Raw Data'!$I$1:$I$13000=($B$2-1)),('Raw Data'!$F$1:$F$13000))

Note that this formal takes the week in B2 and subtracts 1, so it will match up anything found for the previous week. Now, in cell F5 calculate the total earnings per chore for last week:

=E5*B5

Don’t forget to drag both formulas down each column to populate for each chore. That wraps up the money totals. If you have more than one child, you’ll need to repeat each data set and modify the formulas for the corresponding cells in column A.

Sum up weekly totals

Next we’ll sum up the weekly totals. In the Summary tab create headers in row 1 called Name, This Week Allowance, and Last Week Allowance. In column A, starting with row 2, add the name of each child, going down. Finally, in cell B1 you’ll need to sum the totals for the current week from the Money Amounts tab

=sum('Money Amounts'!D5:D13)

Note that you’ll need to change the range based on how many chores you’re tracking (you may have more or less rows than D13). Next, in cell C2 figure up last week’s total, again adjusting the range appropriately:

=sum('Money Amounts'!F5:F13)

Repeat this for each child, placing each child on a new row.

Tally it up

Google Chrome You'll need to use Google Chrome for the next part if you're not already doing so.

Finally, we’ll tally up the earnings and keep a running total going. We’ll actually be using Google Apps Script to help with this one. In the Tally tab, in cell A1, type “Totals”. In cell B2, input the following formula:

=SUM(INDIRECT("B2:B"&MAX(FILTER(ROW(B:B),NOT(ISBLANK(B:B))))))

Break it Down We're going to be using the Apps Script to add new rows each week. We want to make sure that as rows are added, the formula to sum the column doesn't change and automatically adjust for new rows. Absolute references don't work, because as rows are added, Sheets will adjust the reference to make sure the original cell reference is retained. So, the "Indirect" function essentially tells Sheets to ignore any new added rows, so the "sum" function will just add up all values from cell B2 down.

Now, open the apps script. Find it under the Extensions menu. Essentially, we’re going to add a new row at 1:00am every Sunday morning that contains last week’s allowance total. The formula in row 1 will sum all the added weekly totals to keep a running tally of each child’s allowance. That way you don’t have to pay out every week and they can see a total of what they’ve earned. In the editor, enter the following code (I used the Microsoft Chatbot to help with this one):

function addNewRow() {

var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Summary”);

var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Tally”);

var today = new Date();

if (today.getDay() == 0) {

targetSheet.insertRowBefore(2);

targetSheet.getRange(“A2”).setValue(today);

targetSheet.getRange(“B2”).setValue(sourceSheet.getRange(“C2”).getValue());

targetSheet.getRange(“C2”).setValue(sourceSheet.getRange(“C3”).getValue());

}

Break it Down Okay, so let's break some important parts down. If you've followed this guide, you actually won't have to change much.

var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Summary");

This line defines the sheet we're grabbing data from (the "Summary" tab.

var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tally");

This line defines where we're going to put the data.

    targetSheet.insertRowBefore(2);
    targetSheet.getRange("A2").setValue(today);
    targetSheet.getRange("B2").setValue(sourceSheet.getRange("C2").getValue());
    targetSheet.getRange("C2").setValue(sourceSheet.getRange("C3").getValue());

Now, this part tells the script to do a few things:

First insert a row before row 2

In this row, insert the current date

In the target sheet cell B2, enter the value found in the source sheet C2

I've got 2 kids, so I have two rows of information to enter and I've adjusted my target and source sheet cells appropriately.

Hit the save option to save the script. Now we need a trigger. Select the clock icon in the left-hand menu and set up a new trigger. Your function is “addNewRow” with a “Time-driven” event source. The time-based trigger is “Week timer”, on “Every Sunday” (or whichever day you prefer), at time “1am to 2am”. Save and close. Now every Sunday between 1am and 2am the script will fire and add a new row so that you can keep a running tally of earnings. Keep the window with Google Sheets open, as we’ll be coming back to it in a minute.

Enable the Google API

Next we need to head to the Google Developers Console to create an API key so that Dakboard can grab information from you’re Google spreadsheet. An API is simply a string of numbers, letters, and characters that acts as a “passphrase” to allow limited information sharing. Start by heading to the console (https://console.could.google.com).

There’s a lot of information here, but we’ll just need to enable and grab an API key for Google Sheets. Start by hitting the “Create Project” button. Give your project a name (make sure it’s something you can identify later if you need to edit it) and you can leave the location blank (as “No organization”). Tap create. Next, hit “Enable APIs and Services”. In the search box, type “sheets” and hit Enter. Select the “Google Sheets API”.

On the left menu, hit the “Credentials” option to create the API. Now, hit “Create Credentials” and select “API key” as the type. Leave this open, or copy this key to a safe place.

API Security Treat an API key like a password. API keys allow direct access to a site or information. I actually recommend restricting API access, and you can do so by editing the created API key. Since it only needs to access Google Sheets, I recommend restricting it to the Google Sheets API only.

Set up DAKboard

DAKboard Account To continue you'll need to have a paid DAKboard account. The free account only allows you to use pre-defined screen layouts and limits integrations.

Almost there. The last step is to set up DAKboard and pull everything together. Log in to your account and create a new screen. If you’re new to DAKboard play around with the layout for a bit. DAKboard is pretty cool, since it allows you to create awesome dashboards with any layout and has a ton of integrations and features. Once your done playing, let’s add in the Todoist and Google items and create your chore list. Get started by hitting “Add a Block”. Search or find the “Todoist’ integration and then hit “Edit” when the block is dropped in.

In the edit menu you’ll need to link the Todoist account. Go ahead and link your accounts. Now, this is where those filters we created are important. You’re going to display the filter created for each child. In other words, the dashboard will only display chores due today and past due for each child. So, under “List Name” pick the appropriate filter.

Next make sure to check the option for “Allow Completing”. This allows you’re child to check off his or her tasks. You can also choose to show due dates if desired.

If you change to the formatting tab you can fine-tune how you want the text to appear. This is important if you need to increase or decrease text size to fit your tablet screen later.

At this point you should have your child’s chore list displayed. Now let’s add the running totals. Mine is set up to display what they’ve earned this week as well as their running total, but once you see how this is done, you’ll be able to tweak it to your preferences. Add another block choosing the “External Data/JSON” option under Graphs & External Data. We’ll do week to date first. Edit the block, choosing “none” for the authentication type. For the URL, enter:

https://sheets.googleapis.com/v4/spreadsheets/yoursheetID/values/Summary!A2:B4?key=yourAPIkey

Remember, you can get your sheet ID from the HTML address of the open sheet; in this case you want the Summary tab open to grab the sheet ID. Your API key is the key you copied from the developer’s console. Note that we also included in the address the specific cell range that contains the week to date totals for your child (or children). For the summary of the current week totals, this is “Summary!A2:B4”. This includes both child names and the associated weekly total. If you did everything right, DAKboard will import the information.

I’ve got two kids, so I imported both ranges. Under “values”, select the value you’re looking for. Go ahead and add a title, and I’ve chosen the dollar sign icon.

Keep importing information and arranging on the dashboard to your liking. For my board I imported the running tally of their allowance so they can see what they’ve earned.

Summarizing the Steps

That was a lot. Let’s put it together on what we just did, and see how it all works together.

In reality, not much is happening, but it takes a bit to set up and get all the different pieces working together.

Displaying your DAKboard

There’s no point to any of this if the list can’t be displayed. Now, this part is technically completely optional. The way this is set up, you could technically install the Todoist app on any computer or smart device and as things are checked off it will still work. You just won’t have a way to display earnings for each child without opening the spreadsheet. What we’re going to do in this last step is take a Fire tablet and turn it into a smart dashboard. I recommend watching for Amazon flash sales, or if you have an older Fire tablet that is not used you can always repurpose it.

The first step on the tablet is to enable Developer settings. Find the device’s “About” information, specifically the serial number. Tap the serial number 5 times. You’ll get a countdown of how many times you need to tap to enable developer mode. Once enabled, we’ll be able to install Fully Kiosk Browser.

Fully Kiosk allows you to override the basic tablet settings and only display a nice dashboard that, should you so choose, is always on. I use Fully for both the chore list and my smart home dashboards. You can install Fully Kiosk on just about any tablet, so if you don’t have a Fire tablet this will still work, the steps may just be a little different. Head over to the Fully Kiosk website using the Silk browser on the Fire tablet. You will want to download and open the Full Kiosk APK for Android/Fire tablets. Just tap the link to download and open. Importantly, if the file does not open, you will want to click the downloaded file on the tablet and then give permission for the file to be installed. This is where the developer mode comes into play because normally you cannot install APK files from the browser; in normal operating modes you have to install apps directly from the app store.

Once you’ve downloaded and installed fully, you’ll need to configure it to work with your tablet. Head back to DAKboard and open the screen editor for your dashboard. On the upper right corner click the share and export options. Note that you may find it easiest to open and do this using Silk browser on the tablet; you will need to copy and paste the URL given for your screen by DAKboard into the start URL for Fully Kiosk.

Once you’ve defined your start URL for Fully Kiosk, you’re done! You can play around with the Fully settings to override the screen lock, and paid apps have several more options you can enable (such as wake on motion), but the free version works just fine.

(Optional) Wall-mounting

You can always leave your tablet plugged into an outlet on a countertop, or you could mount it on the wall. I replaced both my security keypads with smart dashboards and have also mounted the chore list. You’ve got a couple options here:

Alarm Retrofits More likely than not, if you have an actively monitored alarm system you'll void the warranty or breach your contract if you add to the alarm board. Check your contract and check with your monitoring company before you make any modifications.

In either case, you’ll need a tablet frame. All of my frames are purchased from Konnected and are 3D-printed. Versus the options you find on Amazon, these are reasonable for the price and there are options for all the popular tablets. They’re also designed to fit over a junction box and outlet. If you go with the option to add directly to your alarm board, you’ll need a step-down converter that will take the 12V output from the board and drop it down to 5V. Make sure you buy the right termination, and get the right angle connector so it fits nicely in the frame.

Wrapping up

And that…is that. You may need to experiment with different layouts and sizing in DAKboard to get the right look on the tablet. I recommend the developer settings and emulation mode in the Edge browser. Emulation mode lets you get a pretty good idea of what the screen will look like on the tablet while you’re working on it at your computer. I also recommend experimenting with different filters in Todoist to figure out the best way to display the list in your lifestyle. So…have fun.

Author: Zack

Pharmacist, tech guy, pianist, lover of beer, gamer, beach bum. Probably missed something. Just assume I'm into a little bit of everything.

Categories:

Uncategorized

Tags:

No Tag

Cookie	Duration	Description
__stripe_mid	1 year	Stripe sets this cookie cookie to process payments.
__stripe_sid	30 minutes	Stripe sets this cookie cookie to process payments.
_GRECAPTCHA	5 months 27 days	This cookie is set by the Google recaptcha service to identify bots to protect the website against malicious spam attacks.
cookielawinfo-checkbox-advertisement	1 year	Set by the GDPR Cookie Consent plugin, this cookie is used to record the user consent for the cookies in the "Advertisement" category .
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
CookieLawInfoConsent	1 year	Records the default button state of the corresponding category & the status of CCPA. It works only in coordination with the primary cookie.
enforce_policy	1 year	PayPal sets this cookie for secure transactions.
ts	3 years	PayPal sets this cookie to enable secure transactions through PayPal.
ts_c	3 years	PayPal sets this cookie to make safe payments through PayPal.
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Cookie	Duration	Description
nsid	session	This cookie is set by the provider PayPal to enable the PayPal payment service in the website.
tsrce	3 days	PayPal sets this cookie to enable the PayPal payment service in the website.
x-pp-s	session	PayPal sets this cookie to process payments on the site.

Cookie	Duration	Description
NID	6 months	NID cookie, set by Google, is used for advertising purposes; to limit the number of times the user sees an ad, to mute unwanted ads, and to measure the effectiveness of ads.
VISITOR_INFO1_LIVE	5 months 27 days	A cookie set by YouTube to measure bandwidth that determines whether the user gets the new or old player interface.
YSC	session	YSC cookie is set by Youtube and is used to track the views of embedded videos on Youtube pages.
yt-remote-connected-devices	never	YouTube sets this cookie to store the video preferences of the user using embedded YouTube video.
yt-remote-device-id	never	YouTube sets this cookie to store the video preferences of the user using embedded YouTube video.
yt.innertube::nextId	never	This cookie, set by YouTube, registers a unique ID to store data on what videos from YouTube the user has seen.
yt.innertube::requests	never	This cookie, set by YouTube, registers a unique ID to store data on what videos from YouTube the user has seen.

Cookie	Duration	Description
__wpdm_client	session	No description
akamai_visit_id	2 years	No description
Akamai-Request-ID	session	No description
ASPSESSIONIDQWTQBRCS	session	No description
LANG	9 hours	No description
m	2 years	No description available.
s_vi_ebeaip	2 years	No description available.
TS016300fb	session	No description
TS0196e5be	session	No description
wpinv_session_7d6108ef3fd1b22097ccb955d0f85b0b	2 days	No description

Sorting Out the Chores

What You'll Need

Set up a list

Create a Filter

Link Todoist and IFTTT

Setup Google Sheets

Figure up the money

Sum up weekly totals

Tally it up

Enable the Google API

Set up DAKboard

Summarizing the Steps

Displaying your DAKboard

(Optional) Wall-mounting

Wrapping up

Author: Zack

No responses yet