3

Smarter hot water

TUTORIAL

PREPARING A GOOGLE SPREADSHEET

AND SETTING PERMISSIONS

The Python script is designed to upload data to

a Google Sheet called ‘Hot Water’. This can be

changed to anything you like, but it is important

that the spreadsheet is pre-existing in Google Drive

and that the correct access permissions have been

granted to allow data to be added.

Creating and granting permissions to the

spreadsheet is best done on a full-size computer,

not the Pi Zero.

Start by going to

Google Drive on the

web and create your

spreadsheet. As said

before, the Python

script expects this to

be called ‘Hot Water’,

but this can be changed

to anything you like, as

long as the code in the

Python script is changed to match.

UNLOCKING SECURITY

Now the security access permissions need to be

granted for this spreadsheet:

• Go to the Google Developers Console
  (https://console.developers.google.com)
  and create a new project. Call it anything you
  like (e.g. hotwater).


• From the list of options on the left-hand
  side, select ‘Library’ and then search for the
  Google Drive and Google Sheets APIs. Enable
  them both.
  
  
  • Now, from the list of options on the left-hand
    side, select ‘Credentials’.
  
  
  • Click the blue ‘Create credentials’ button
    and select ‘Service account key’ from the list
    of options.
  
  
  • In the new screen, ‘New service account’ will
    be preselected as the only option.
  
  
  • Enter a name in ‘Service account name’ – this
    can be anything you like (e.g. data_upload),
    and choose Project / Owner as the role.
  
  
  • An email address will be displayed in the
    window, ending in ‘gserviceaccount.com’ –
    make a note of this email address as you will
    need it later.
  
  
  • Selected ‘JSON’ as the key type and then
    click ‘create’.
  
  
  
  

A file will be downloaded to your PC. Rename

this to client_secret.json and save it somewhere

convenient on your computer. If you open the file

in a text editor, you will see the email address from

earlier embedded within the code.

This file needs to be copied onto the Raspberry

Pi and saved in the same folder as the Python

script, preferably in

a folder called

/home/pi/python.

If you create the

Python file in a

different folder,

the code should be

updated to reflect

the different folder

location. This JSON

file becomes the digital key to unlock the

spreadsheet. When Python attempts to write

data to the Google Sheet, it sends a copy of the

JSON file to the server. Google will confirm that

the Sheet has been shared with the embedded

email address. It’s therefore important not to share

this file with anybody else as it allows access to

your files.

Return to Google Drive and open your blank ‘Hot

Water’ spreadsheet.

In the top-right corner, click ‘Share’ and then

add the email address from earlier. You may get

an ‘address not found’ email from Google a few

minutes later, but this can be safely ignored.

Above

Pins 1, 7, and 9

protruding from the

back of the board

Creating and granting

permissions to the

spreadsheet is best done

on a full-size computer,

not the Pi Zero

”

”

A threshold

temperature is

calculated, which

is halfway between

the temp_max and

temp_min. If the

temperature is

above this threshold,

it is counted as 'hot'

when calculating

the percentage of

water remaining.

QUICK TIP