step one: Google Sheets
Login to your Google account. Open up Google Sheets. Create a spreadsheet. I used the following headers:
You will need the spreadsheet ID. To find this, look at your address bar, it's the information between "/d/" and "/edit#":
I got ambitious, and I decided to track multiple storage areas (fridge, freezer, left cupboard, right cupboard). So, just duplicate your "Sheet1" tab and rename if you are as nerdy as me:
optional bonus round: I like everything pretty, and love colours. Sooo, I froze the top row (under view > freeze), centered most of the columns, and applied a conditional formatting to hightlight expired items in yellow.
important note: this entire function just updates this spreadsheet. Nothing stops you from updating or editing it on your own, either. Just take note, if you have more than one item with the same number and you run a remove command, all items with that number get removed.
step two: Google Apps Script
Login to your Google account. Open up Google Apps Script. Create a new project. I created a new project for each storage area I was creating a script to track (fridge, freezer, left cupboard, right cupboard).
Lets start with the errors I got in the process. Although they may have been impacted by the Siri Shortcut or the Google Sheet, they all were annoying in the Google Apps Script the most:
- date format:
if your date isn't in the format of "yyyy/M/d", sheets likely won't recognize it as a date, and as such, the calculation for age will not work. - variable being SLIGHTLY different:
anyone who knows about coding knows about variables being spelt wrong. If the data isn't being passed, check your spelling. - not changing to "anyone":
if you don't change your deployment's Who has access to Anyone, your script will come back with an error:
Β Drive Sorry, unable to open the file at this time. Please check the address and try again. Get stuff done with Google Drive Apps in Google Drive make it easy to create, store and share online documents, spreadsheets, presentations and more. Learn more at drive.google.com/start/apps.
- quantity:
while not actually an error, I thought I wanted to reduce my items by initally stocking 4 of something. Unfortunatly, the script/sheets integration doesn't allow for that, so this became more of a less-important variable. - URL as variable:
Jojo Monkey had the script ID passed as a variable, and then inserted into the code later. When I would run it, I kept getting a generic google docs error, and couldn't find support. Once I removed it, and just used the URL, it worked fine.
Google Docs encountered an error. Please try reloading this page, or coming back to it in a few minutes.
The code: as I write this on March 26, 2024, I already see Jojo Monkey has added an email function to his code, so his code is evolved differently than mine. Check his out for some great bits!
.txt file download: Copy + paste the code in this text file into your Code.gs file in your project.
Final steps:
- insert your Google Sheets ID, and remember to change the tab to whatever you named it
- save your code. Run your code - this activates the permissions required by Google.
- deploy your code - this activates even more permissions required by Google. Remember to set Who can access to Anyone. Take note of the URL that is generated (you can also view it in "Manage Deployments" later) -- you will need it for Siri Shortcuts.
step three: Siri Shortcut
I set this up on my Mac, but you can do it from your phone, too. The process is the same. If you've never created a Siri Shortcut (which I hadn't), you just create a new shortcut, and start building the parts you need. I've shown more where ever I could, and I set some default values to make it less repetitive, and added an if statement to make sure there was a product name. Just search for the parts like Set variable, Ask for, and text.
Note towards the end, the text input I have covered in yellow is where you put your deployment URL you got from your Google Apps Script. Every time you re-deploy (/change) your code, you will need to paste the new URL here again. Make sure you change the Get contents of to the POST method.
Once you've created one Siri Shortcut, you can just duplicate, and place the new deployment URL where needed.
step four: NFCs
Order some NFC stickers (I got mine from Amazon) -- make sure they're stickers, so they have the ability to be stuck onto your cupboard door, etc.
Create an Automation in Siri Shortcuts. You'll want to do this from your phone. Then scroll down and choose the NFC option.
I chose the Run Immedeiately and turned off the Notify When Run options. Then hit Scan, and scan your NFC device at the back top of your iPhone.
It will confirm the tag has been scanned. Then choose the Siri Shortcut you created in the last step.
Stick your tag somewhere, and you're good to start scanning.
optional bonus round: I stuck a couple of these tags around my apartment so I can silently run tasks, light turning on specific LIFX bulbs, etc. Try it out!
step five: run
scan your tags and populate your spreadsheet. I suggest using a sharpie to number the items, just write on the package.
