The purpose of this is to create a spreadsheet that records your pain in a way that most doctors recommend. Specifically, I made this to track my chronic migraines the way my neurologist wants based on the "Detailed Management Diary" from the American Headache Society.
It tracks 4 things, severity, duration, symptoms, and medication. It doesn't quite match the official printed form my doctor gave me (and maybe the same one you use), but it does track all that data in a format you can either email to your doctor or print out and take to your next appointment.
Let's get started.
You need a few things:
Google Home (someone may be able to adapt this for Amazon Echo, but I have a home, so it's based on that)
Since you have a Google Home, you have a google account. As such you have access to Google Docs and google sheets in particular.
And finally, you need an IFTTT.com account. If you don't know what that is, don't worry, I'll walk you through the things you'll need there. For now, make an account there and we'll get to the other stuff in a bit.
Step 1. Create a new spreadsheet in Google Docs
I named mine "Headache Timer".
If you haven't used any spreadsheets before, don't worry. You won't have to do a whole lot here, but you will have to cut and paste a script in a little bit. (Don't worry, it won't hurt).
In your web browser go to https://docs.google.com/spreadsheets, click on the "+".
In the new spreadsheet, at the top, you can change the name. Just click on "Untitled Spreadsheet" and type in whatever name you like. Remember the name, though, because you'll need it later.
In my sheet, I put 'titles' in the first row for the columns. You don't have to, but it makes it more obvious to others what information is stored in the columns. If you click on the first 'cell' in the fist column (right under the A), you can type in what you need. My column titles look like this:
That's really all the editing you'll have to do on the sheet itself (unless you choose to later), but the next part is going to be really technical, but we'll tackle it a little at a time.
Click on the menu option "Tools" then on the drop-down click "Script editor". That should open a new tab in your browser.
This looks a little scary, it's a code editor. You can't really break anything, but there are a few things to do.
1. Just like in the spreadsheet screen, if you click the title you can rename it. In this case, name it "addStartDate".
2. Copy this code:
function addStartDate(e) {
var lr = SpreadsheetApp.getActiveSheet().getLastRow();
SpreadsheetApp.getActiveSheet().getRange(lr, 1).setValue(new Date());
}
3.Then in the main window you'll want to select everything. From the start of the word "function" to the close parenthesis ')' and hit "ctrl+v" to paste the code in it's place.
It should look like this after everything:
4. Click "Edit" and from the dropdown choose "All your triggers".
If you didn't name your project, you will be prompted to do so, put in the name and continue.
This will bring up the triggers screen. If you have other projects in sheets you might see a lot of things here. Ignore all of them. Click "Add a new trigger".
This will add a new line with your new project.
If it's not in the first box, click the dropdown and choose "addStartDate".
In the next box to the right click the dropdown and choose "From spreadsheet".
In the next box click the dropdown and choose "On Change".
Click Save.
5. Now is the part where it all gets really messy. Your experience at this point will probably vary, but we're going to need to give this project permission to run.
I don't really have an easy way to make this happen, but here's the steps I took.
When I clicked "Save" it popped up a notice that looked like this:
Click "Review Permissions".
This pops up a log-in prompt for Google. Choose your ID.
This brings up this notice:
Click "Advanced". (in the small print)
This should add more to the window with more scary language. It's OK, you created the thing it's worried about, so if needed scroll down and then click "Go to addStartDate (unsafe)".
That should take you back to the sign-in looking pop-up. Click the "Allow" button.
At the end of all that you sould be looking at the 'code' screen again.
If it all went well, your spreadsheet should be ready. You can close this tab.
You can also close your spreadsheet if you want, but I recommend leaving it open so you can see what happens to it later.
So now you want to go to IFTTT.com. If you haven't already, create an account.
Ifttt is really cool. It can do a lot of things. In order to do those things, you need connections to other stuff.
If you used Google to create your ID, then you have all the connections to Services you need for this project. If not, you'll need to connect Google Assistant and Google Sheets. (I won't walk you through this, just explore the site and bit - trust me, you'll want to make Applets of your own.)
After you've connected those, you need applets that will connect your Google Home to the spreadsheet you created.
My set up uses three applets. Maybe someone will find a way to streamline the process, but I found this works really well.
IFTTT used to let you just use someone else's recipes, but I can't seem to find that ability anymore, so instead you'll have to make new ones. Here are the links to my three appletts for this project If you see a way to use them, feel free. If not, I will explain them a little below:
https://ifttt.com/applets/69599940d
https://ifttt.com/applets/69601214d
https://ifttt.com/applets/69602326d
What they do, working together, is let me say "Hey Google, Start Headache Timer" to have a starting time recorded on the spreadsheet.
When the headache is over I can say "Hey google, end headache timer, 7 [for severity], aura throbbing nausea [for symptoms of that headache]"
And after I've done that I can say "hey google, add medication Tylenol advil 1000 miligrams each" (for medication)
This creates 3 lines in the spreadsheet. The code we put in earlier adds the time stamp in the first cell in each row. The timestamp in the row for medication can be ignored, but the ones for 'start' and 'end' will give the duration fo your headache.
Mine looks like this:
So how to get that?
On the IFTTT page, choose "New Applet". (If you don't see it, click on your name and choose it from the drop down menu.)
Click "+this".
In the search box start typing "google assistant". When you see it in one of the boxes below, click on it.
For the "start headache timer" recipe you'll click on the "Say A Simple Phrase" option.
The options on the next screen should be pretty self explainatory. Choose phrases you can easily remember and put in slight variations so you Google Home understands you even if you say it slightly different each time.
Put in any phrase you like for how you want Google Home to repsond. I've found that a simple phrase like "Ok, starting timer" is best.
Click "Create Trigger".
Now click "+that".
Find and click on "Google Sheets"
Click "Add a row to sheet"
For "Spreadsheet Name", put in the name you used for your spreadsheet back in step 1.
In the "Formatted Row" box, enter "|||Start"
What this does is put the word "Start" in the second cell, while the first cell is filled with the timestamp automatically by the script we created.
In the Drive Folder Path put "/"
THIS IS IMPORTANT, the path must match where the spreadsheet is Otherwise IFTTT will create a new spreadsheet with the same name in your google account. If your spreadsheet isn't working, this is probably why. If you didn't change the path when you made the sheet, "/" should work.
Click "Create Action"
On the next screen click "Finish"
If you did everything right, you should be able to say to your Google Home "Hey Google, start headache timer" and it will record the time in the spreadsheet.
The next two applets are similar, except for "end headache timer" you'll want to choose the Google Assistant option that includes a number and text. Follow the directions on the 'that' screen for what you want to say. Take note of teh "#" and "$" characters that represent the number and text you will say to your Google Home.
Go on through and choose google sheets for 'that'. In the "Formatted Row" box, enter this text:
|||End|||{{NumberField}}|||{{TextField}}
The number field and text field will correspond to what you said to your Google Home.
Create action and finish.
Repeat for the final applet, choosing the google assistant option for just a text ingredient.
Same steps above, with just a "$" as your phrase.
When you get to google sheets, use "|||||||||||| {{TextField}}" in Formatted Row.
Create Action and save.
As I said, I encourage you to explore IFTTT and figure out how it works. You can customize the applets in your own way. IFTTT will let you know what works and what doesn't.
And that should do it. Here's a video showing how it works.
And here's the result in my spreadsheet:
