Send data from Hubitat to Google Sheets?

🛎️ Get Help Integrations
20

Need some help from those who understand Google Scripts, etc.
Decided to try to get back on this. I really know NOTHING about Google Forms or Google Scripts, and the posts above assume some familiarity- each time I tried, I ran into an issue.

Based on some other posts here, I decided to see if ChatGPT could walk me through the details. I initially had a simple query, but kept running into issues, asked ChatGPT follow up questions, and used the responses to refine the request. I ended up with this request to Chat GPT:

"Create an app for hubitat to export data to a google sheet using a google forms plus apps script trigger combo."

I got the below instructions, and implemented them but it does not work.
Nothing writes to the Google Sheets spreadsheet, even thought the device logs show it is logging data. What am I doing wrong (ie what did ChatGPT get wrong....)

ChatGPT Instructions:(my comments in italics)
Export Hubitat Data to Google Sheets Using Google Forms + Apps Script (No OAuth Required)

Overview:
Send Hubitat device event data (e.g. temperature, switch status) into Google Sheets using:

  • Google Form (to collect data)
  • Apps Script (to extract field entry IDs)
  • Hubitat custom app (to send HTTP POSTs)

Step 1: Create and Publish a Google Form

  1. Go to https://forms.google.com
  2. Create a new form with these Short Answer fields:
    • Device Name
    • Attribute
    • Value
    • Unit (optional)
  3. Click the eye icon (Preview) to publish the form
  4. Click “Responses” → Sheets icon to link it to a Google Sheet

Step 2: Get entry.X Field IDs via Apps Script

  1. In the Form’s Preview tab, copy the Form ID from the URL:
    Example: https://docs.google.com/forms/d/1abcXYZ1234567890/preview
    → Form ID: 1abcXYZ1234567890

  2. In the linked Google Sheet, go to: Extensions → Apps Script

  3. Paste this script:

function getFormEntryIDs() {
const form = FormApp.openById('1abcXYZ1234567890'); // use single quotes
*I replaced the above portion in single quotes with the actual Form ID from the url in 2.1 above
const items = form.getItems();
items.forEach(item => {
const title = item.getTitle();
const entry = item.asTextItem().getId();
Logger.log(${title}: entry.${entry});
});
}

  1. Save the script (File → Save), name it something like “GetFormEntryIDs”
  2. Click inside the function, then click Run
  3. View the execution log at the bottom for results like:
    Device Name: entry.1234567890
    Attribute: entry.2345678901
    Value: entry.3456789012
    Unit: entry.4567890123

Step 3: Create Hubitat App

  1. In Hubitat, go to Apps Code → Add App
  2. Paste the provided Groovy code (Google Form Exporter app)
    definition(
    name: "Google Form Exporter",
    namespace: "custom",
    author: "Your Name",
    description: "Send device events to Google Form",
    category: "Convenience"
    )

preferences {
section("Devices to monitor") {
input "devices", "capability.*", title: "Choose devices", multiple: true
}
section("Google Form Info") {
input "formUrl", "text", title: "Paste the Google Form URL (ends in /preview)"
input "entryDevice", "text", title: "Entry ID for Device Name"
input "entryAttribute", "text", title: "Entry ID for Attribute"
input "entryValue", "text", title: "Entry ID for Value"
input "entryUnit", "text", title: "Entry ID for Unit"
}
}

def installed() {
initialize()
}

def updated() {
unsubscribe()
initialize()
}

def initialize() {
devices.each {
subscribe(it, "*", eventHandler)
}
}

def eventHandler(evt) {
def payload = [
"entry.${entryDevice}" : evt.displayName,
"entry.${entryAttribute}": evt.name,
"entry.${entryValue}" : evt.value,
"entry.${entryUnit}" : evt.unit ?: ""
]

def postParams = [
    uri: formUrl.replace("/preview", "/formResponse"),
    body: payload,
    headers: [
        'Content-Type': 'application/x-www-form-urlencoded'
    ]
]

try {
    httpPost(postParams) { resp ->
        log.debug "Form submitted. Status: ${resp.status}"
    }
} catch (e) {
    log.error "Error sending data to Google Form: $e"
}

}

  1. Click Save (no need to publish)
    *actually you click "Done"
  2. Go to Apps → Add User App → Select Google Form Exporter
  3. Select devices, enter the Google Form URL (ending in /preview), and paste the entry.X IDs
  4. Click Done, then click Save to store the settings

Step 4: Test

  • Trigger a device event (e.g., switch on)
  • Wait a few seconds
  • Check the linked Google Sheet → new row should appear

Tips:

  • Each attribute event creates a separate row
  • Modify the app to filter specific attributes if needed
    """