SmartThings Google Sheets Logging

Create the Spreadsheet

1. Create a new spreadsheet: http://docs.google.com/spreadsheets/create

2. Name the spreadsheet file whatever you want

3. Get your new spreadsheet ID from the URL. Example: https://docs.google.com/spreadsheets/d/169v40OsFOaGHO6uQwuuMx2hlWK-wvYCzrr93FAWivHk/edit#gid=0
   example id is "169v40OsFOaGHO6uQwuuMx2hlWK-wvYCzrr93FAWivHk"

4. Add the single value "Time" in cell A1. You might consider selecting View -> Freeze -> 1 Row

5. Create the helper script

6. Open script: Click Extensions -> Apps Scripts. Optionally change the name of the project (default "Untitiled project")
   

7. Copy the contents of Code.gs to replace the stub "function myFunction"

8. On line 4, Replace "REPLACE ME WITH SPREADSHEET ID" with sheet id from step 3
   

9. If you changed the name of the sheet (the name in the tab at the bottom of the spreadsheet, not the name of the file), update it on line 5. (defaults to "Sheet1").

10. Deploy helper script as webapp

11. Deploy webapp: Click Publish -> Deploy as web app
    

12. Change Who has access to the webapp to "Anyone". Please note, if any one gets a hold of your published endpoint, they will be able to send data to your spreadsheet, but they will not be able to view any of it.
    
    Also: If you revise or fix your webapp code, be sure to select New as the version on the webapp publishing popup's Version drop-down. Otherwise you may continue to run the older version. Version management must be explicitly performed sometimes.

13. Approve the access to the app
    

14. Copy the URL on the confirmation page.
    E.g.: https://script.google.com/macros/s/AKfycbzY2jj4l7RSpFYfN62xra0HmcXPQXAUI17z6KKHWiT3OYyhUC4/exec
    

15. Extract URL key for your new webapp, it is between /s/ and /exec. AKfycbzY2jj4l7RSpFYfN62xra0HmcXPQXAUI17z6KKHWiT3OYyhUC4
    You will need to enter this into the SmartApp below.

16. (Optional): Test out your new webapp, add this to the end of the URL from step 6: ?Temp1=15&Temp2=30
    E.g., https://script.google.com/macros/s/AKfycbzY2jj4l7RSpFYfN62xra0HmcXPQXAUI17z6KKHWiT3OYyhUC4/exec?Temp1=15&Temp2=30
    A successful test will return the message The script completed but did not return anything.

    If you do test it, make sure you delete the test data from the spreadsheet. Just delete any rows added after row 1 and any test columns

Create the SmartApp

1. Login to the the SmartThings IDE at https://account.smartthings.com/ (Make an account if you haven't already)

2. Go to "My SmartApps"

3. Use either Github Integration (step 4) or Manual (step 5)

4. Github Integration
   (See this link if this is your first time using GitHub Integration: http://docs.smartthings.com/en/latest/tools-and-ide/github-integration.html)

   1. Click Settings
   2. Add this repo:
      Owner: loverso-smartthings
      Name: googleDocsLogging
      Branch: master
   3. Click Save
   4. hit "Update from Repo" and select googleDocsLogging (master)
   5. Under "New (Only in GitHub)" select "google-sheets-logging"
   6. Select the checkbox next to "Publish"
   7. Click "Execute Update"

5. Manual creation (If you didn't follow step 4)

   1. hit "+ New SmartApp" on the right, then select the "From Code" tab. 2, In another window, open the source code located in this repo at smartapps/cschwer/google-sheets-logging.src/google-sheets-logging.groovy https://github.com/loverso-smartthings/googleDocsLogging/blob/master/smartapps/cschwer/google-sheets-logging.src/google-sheets-logging.groovy
   2. Click "Raw" and copy all the code
   3. Paste the code into the New SmartApp "From Code" box and click Create

Install the SmartApp

1. In the SmartThings App, tap on the Automations tab, click on + in the upper right of the screen, select Add Routine and choose the Discover tab at the top. Scroll down to find the Google Sheets Logging smartapp.

   Alternately, you can install via the beta SmartThings web interface at https://my.smartthings.com/. Login with your SmartThings account, click the plus in upper right corner, select Add Groovy SmartApp and then select Google Sheets Logging.

2. Select events you want to log under "Log devices..."

3. Enter URL key from step 15 under "URL key"

4. Enter a name of the smart app

5. Click Done!