HOWTO Add rows directly to Google Sheets

My use case is to share a record of when people unlock our front door with the rest of my family. Here's how I configured Rule Machine to log directly to Google Sheets without any other services involved. Hope it's useful, the other solutions I found involved third party services like IFFT and such, this is direct and fast.

First, create a new sheet and create a form with short answer texts.

Second, click the edit button when looking at the form, click the 3 dots in the top right and select, "Get pre-filled link"

Copy the link somewhere and extract out each of the "entry.NNNNNNNNN" items from the URL. Extract the sheet ID from the URL, the string of characters before the last /.

In Rule Machine, create a rule to set some local variables to the values you want to save as rows. I set name and action using the device properties of the lock.

Create a local variable named sheet and set it to the value of the sheet ID.

Finally make the final action a form encoded POST to the URL https://docs.google.com/forms/d/e/%sheet%/formResponse?usp=pp_url

Select content type: form encoded

Enter body for POST. Note the & separating each field.

entry.11111111111=%date%&entry.2222222222=%now%&entry.33333333333=%name%&entry.444444444=%action%

Save the rule and run it, and you should see rows added in the "Form Responses" worksheet

3 Likes

Download the Hubitat app