GoogleNestInSheetHistory

sheet_scipt.js

/*
  Create google sheet file with 'Data', 'Weather', 'Settings' sheets
  Settings: //https://developers.google.com/nest/device-access/get-started  
    SDMUrl	https://smartdevicemanagement.googleapis.com/v1
    projectId	...
    nestDeviceId	...
    oauthClientId	...
    oauthClientSecret	...
    oauthRefreshToken	...
    oauthAccessToken	...
    openWeatherKey	xyz
    openWeatherLat	51.0
    openWeatherLon	0.0
    openWeatherUnits	metric  
    
  edit script 
  const SpreadsheetId = "use the spread sheet ID what is going to be used for the data"
  
  and add a minute trigger to call main()
  - optionally set up openweather account and add a x-hour/day trigger for weatherMain()
*/

//https://developers.google.com/nest/device-access/authorize
//https://www.youtube.com/watch?v=4Rkzf9g1LVo&t=384s
//https://console.nest.google.com/device-access/project/a99d8c32-77e1-429c-a81a-5c167514ecc3/information

const SpreadsheetId = "11E8gcmLrmhxhWB4aJre47uaQYBNo3ujU77kiArayES4"
const SheetFile = SpreadsheetApp.openById(SpreadsheetId)
const Settings = getSettings()

function main() {
    //Logger.log(JSON.stringify(Settings))
    //let allDevices = getAllDevices()
    //Logger.log(JSON.stringify(allDevices))

    let devInfo = getDeviceInfo(Settings.nestDeviceId)
    // Logger.log(JSON.stringify(devInfo))

    let nowStatus = devInfo["traits"]["sdm.devices.traits.ThermostatHvac"]["status"]
    let now = new Date()
    let row = [
        Utilities.formatDate(now, SheetFile.getSpreadsheetTimeZone(), "yyyy-MM-dd HH:m:s"),        
        devInfo["parentRelations"][0]["displayName"],
        nowStatus,
        "",
        Utilities.formatDate(now, SheetFile.getSpreadsheetTimeZone(), "yyyy-MM-dd"),
        Utilities.formatDate(now, SheetFile.getSpreadsheetTimeZone(), "yyyy-MM"),
        Utilities.formatDate(now, SheetFile.getSpreadsheetTimeZone(), "yyyy"),
    ]
    //Logger.log(row)

    let sheetData = SheetFile.getSheetByName("Data")
    let lastRowNum = sheetData.getLastRow()
    if (lastRowNum == 0) {
        sheetData.appendRow(["Timestamp", "DisplayName", "Status", "YearMonthDay", "YearMonth", "Year"])
        sheetData.appendRow(row)
    } else {
        let lastRowData = sheetData.getRange(lastRowNum, 1, 1, 4).getValues()
        let lastStatus = lastRowData[0][2]      
        if (lastStatus != nowStatus) {    
          if(lastStatus == "HEATING") {
              let heatingStart = lastRowData[0][0]              
              let daysOfSeconds = (now.getTime() - heatingStart.getTime()) / 1000.0 / 86400.0
              //will end up as simple time      
              row[3] = daysOfSeconds
          }                
          sheetData.appendRow(row)
        }
    }

    /*
    Settings["lastSync"] = Utilities.formatDate(now, SheetFile.getSpreadsheetTimeZone(), "yyyy-MM-dd HH:m:ss")
    updateSettings(Settings)
    */
}

function weatherMain() {
    let sheetData = SheetFile.getSheetByName("Weather")
    let url = `https://api.openweathermap.org/data/2.5/weather?lat=${Settings.openWeatherLat}&lon=${Settings.openWeatherLon}&units=${Settings.openWeatherUnits}&appid=${Settings.openWeatherKey}`
    let rawData = fetch(url, {
        "method": "get",
        "contentType": "application/json",
    })
    let data = {
        "Timestamp": Utilities.formatDate(new Date(), SheetFile.getSpreadsheetTimeZone(), "yyyy-MM-dd HH:m:ss"),
        "Location": rawData["name"],
        "Temp": rawData["main"]["temp"],
        "TempMax": rawData["main"]["temp_max"],
        "TempMin": rawData["main"]["temp_min"],
        "TempFeelsLike": rawData["main"]["feels_like"],
        "Humidity": rawData["main"]["humidity"],
        "WindDeg": rawData["wind"]["deg"],
        "WindSpeed": rawData["wind"]["speed"],
        "WindGust": rawData["wind"]["gust"]
    }
    if (sheetData.getLastRow() == 0) {
        let labels = []
        for (const key in data) {
            labels.push(key)
        }
        sheetData.appendRow(labels)
    }

    let rowData = []
    for (const key in data) {
        rowData.push(data[key])
    }
    sheetData.appendRow(rowData)
    //Logger.log(rowData)
}

//--- Functions ---
function getSettings() {
    let sheet = SheetFile.getSheetByName("Settings")
    var data = sheet.getRange(`A1:B${sheet.getLastRow()}`).getValues();
    var result = {}
    data.forEach(row => {
        if (row[0] != "") {
            result[row[0]] = row[1]
        }
    })
    return result
}

function updateSettings(settings) {
    let sheet = SheetFile.getSheetByName("Settings");    
    var rows = sheet.getRange(`A1:B${sheet.getLastRow()}`)
    //TODO fix deleteRows, can't delete all
    //sheet.deleteRows(1, sheet.getLastRow())
    let table = []
    for (const key in settings) {
        table.push([key, settings[key]])      
        //sheet.appendRow([key, settings[key]])
    }
    rows.setValues(table)
    //sheet.appendRow(["", ""])
}

function fetch(url, options) {
    options = options || {
        "method": "get",
        "contentType": "application/json",
        "muteHttpExceptions": true,
        "headers": {
            "Authorization": "Bearer " + Settings.oauthAccessToken
        }
    }

    options.muteHttpExceptions == options.muteHttpExceptions || true

    let fetchResult = UrlFetchApp.fetch(url, options)
    let code = fetchResult.getResponseCode()
    if (code == 200) {
        return JSON.parse(fetchResult.getContentText())
    } else if (code == 401) {
        let refreshTokenUrl = `https://www.googleapis.com/oauth2/v4/token?client_id=${Settings.oauthClientId}&client_secret=${Settings.oauthClientSecret}&refresh_token=${Settings.oauthRefreshToken}&grant_type=refresh_token`
        //Logger.log(refreshTokenUrl)
        let result = fetch(refreshTokenUrl, {
            "method": "post",
            "muteHttpExceptions": false,
        })
        Settings.oauthAccessToken = result["access_token"]
        updateSettings(Settings)
        options.muteHttpExceptions = false
        options.headers["Authorization"] = "Bearer " + Settings.oauthAccessToken
        return JSON.parse(UrlFetchApp.fetch(url, options).getContentText())
    } else {
        throw fetchResult.getContentText()
    }
}


function getAllDevices() {
    //https://developers.google.com/nest/device-access/authorize
    //"name": "enterprises/project-id/devices/device-id",
    let url = `${Settings.SDMUrl}/enterprises/${Settings.projectId}/devices`
    return fetch(url);
}

function getDeviceInfo(deviceId) {
    let url = `${Settings.SDMUrl}/enterprises/${Settings.projectId}/devices/${deviceId}`
    return fetch(url);
}