Forms,Sheets and Charts

Google Forms and Sheets can be used for entering and storing data in the cloud from Node Red applications. In this example a Form is used to register the Movement activity from a PIR attached to a Raspberry Pi. The data is kept in Google Sheets and accessed by Charts.

Creating the Form

Open Google Forms directly (or via Google Docs) from Google Apps in the Chrome browser and create a new form. Title it e.g. HA PIR then click on the Untitled Question box and change Option 1 from Multiple Choice to Short Answer using the drop down list. Change form name (top left ) to HA PIR by clicking on it. The Untitled Question is renamed Movement.

Creating the linked Sheet

Move from Questions tab to Responses. Click the Sheets icon to Create Spreadsheet. The form is now linked to the Spreadsheet HA PIR (Responses) which opens in a new Chrome tab and contains the Form Responses sheet. The sheet is renamed 'responses'.

A URL for the Node Red http request node

Return to the HA PIR tab in Chrome. Click the More icon next to Send,  and select Get pre-filled link.
Type in Your answer e.g. activity, then click Get link and Copy link to clipboard.
In Notepad change the text "viewForm" in the link to "formResponse" and replace "activity" with "{{payload}}".
The modified link can now be used as the URL in the GET http request node of NODE RED to return a UTF-8 string. 

Dot Timeline

The Google Timeline Chart in Sheets by default displays continuous lines through the recorded movement response events, however what is needed are just point markers at the times of the movement. The line can be interrupted by recording a timeline event with the absence of a movement value, and with small intervals between the recorded movement and non-movement the movement events appear as point markers.

The "activity" in the URL link for the http request node of Node Red is replaced with "{{payload}}". The payload to the http request node is a string format containing either "1" or an empty string (if a number format were used then an empty number would be interpreted as a 0,  which is not what is required).The Timeline Chart is set to a maximum value of 2 so that the points are displayed centrally. The timestamps for the recorded events is automatically provided by the Form response, and the data is recorded in the Sheet "responses". The data in the Sheet responses is to be displayed in the Timeline Chart.

LIFO the Spreadsheet

It's more useful to automatically sort the form responses so that last entry is shown at top of the Spreadsheet.

Add another Sheet to the Spreadsheet. Since we are only interested in the Timestamp of when the movement occurred, in cell A1 of the new Sheet , enter

 =sort(QUERY(responses!A:B,"select A,B where B=1",1),1,false) .

 so that the timestamps with no movement are removed. The sheet will be populated automatically with new form responses with the most recent at the top.

This could be further refined by limiting to just the most recent 15 by entering the following into cell A1

=sort(QUERY(responses!A:B,"where B=1 offset "&countIF(responses!B:B,1)-17),1,false) . 

Tabulation of Recent Movements

The objective is to list the most recent movements by Day of Month and Time, such that the Day of Month is not replicated in the listing and appears topmost in the list.

The format strings dd mmm ddd in cell D2 and hh:mm in cell D3 are used for formatting the Day of Month and Time , respectively. A table is created using text(TO_DATE((A2)),$E$2) and =text(TO_DATE((A2)),$D$2)  . The final table manipulates the previous table to remove the duplicate Day of Months and keep the last entry topmost in the list =if(countif($E$2:$E2,E2)>1,,E2)  . The data in the final table can be used for Table Chart for inclusion in the Node Red dashboard. While the data in the Responses sheet is used for the Timeline Chart.

Publishing the Charts for Node Red