Google sheets is a cloud-based spreadsheet solution that is free to use; you just need a Google account for this. Google sheets provides APIs that let you read, write, and update your spreadsheets programmatically.

Let's dive into Google sheets and create one for our own use:

1. Log in to your Google account, or create one if you don't have an account already
2. Open the Google sheet by clicking on the icon that says sheets
3. This will redirect you to the Google sheets home page. Click on the plus icon to create a new/blank spreadsheet for our project
4. Once open, edit the name of the spreadsheet and change it to Weather Station
5. Since it autosaves, we don't need to worry about saving the spreadsheet after edits.

Now that we have created our spreadsheet, before we write the code and demonstrate the process of updating the spreadsheet using an API, we need to enable the API first.

Perform the following steps to enable it:

1. Enable the Google sheets API.
2. Navigate to https://console.developers.google.com/flows/enableapi?apiid=sheets.googleapis.com.

3. Select the Create a project option from the dropdown and click on Agree and continue:

4. If the API is successfully enabled, you'll be presented with a success screen. Click on Go to credentials on the same screen:

5. Add credentials to your project page will appear; click on Cancel.
6. At the top of the page, select the OAuth consent screen tab. Provide your Email address, give the product name as WeatherStation, and click on Save:

7. You will be routed to the credentials tab. Click on Create credentials and select OAuth client ID.
8. See the application type as Other and enter the name as Google Sheet API, as shown here:

9. A popup will appear with the client ID and a secret key. Save it for later use. Click on OK.
10. Click on the download icon on the right to download the JSON file that will be used to access Google sheets programmatically:

We have discussed all the components of our project in detail, now let's integrate all the pieces and build the weather station.

Let's interface the sensors first.

For temperature sensor DS18B20, we are using the waterproof version of it. The following circuit diagram shows how to connect the sensors to Raspberry Pi:

Let's review the circuit diagram. Raspberry Pi uses GPIO 4 to communicate with temperature sensors over the one-wire protocol, so GPIO 4 acts as a data line. We created a communication bus with the data line (GPIO 4) in yellow, the power line (3.3V) in red, and ground (GND) in black. Using this bus, we can interface multiple sensors simultaneously, as shown. A 4.7 k Ohm resistor is used between the data and power lines, which acts as pull-up resistor for the data line to keep the data transfer stable.

Now, we will interface the DHT11 humidity sensor with Raspberry Pi, as shown in the following diagram. Here, a 10 K Ohm resistor is added between the power line (3.3V) and data line (GPIO 27), which acts as a pull-up resistor:

Since we have completed the circuit design part, now we will write the code for it. Our code will be divided into three major parts:

• Read data from the weather API
• Read data from sensors interfaced to Raspberry Pi
• Push all the data into Google Sheets

Let's write the code step by step.

Log in to Raspberry Pi and create and open a file with the name weatherStation.js, then include module request and request-promise:

var request = require('request');
var requestPromise = require('request-promise');

var options = { uri: 'http://api.openweathermap.org/data/2.5/weather?id=1273293&APPID=YOUR APP ID/ APP KEY', };

The options contain the Uniform Resource Identifier (URI), which is the API endpoint to get data from the openweathermap website. Two parameters are passed to the API endpoint: one is id, which indicates the city/state for which we want the weather data, and the other, APPID, is a key that is provided when we create an account on the openweathermap website, as described in an earlier section of this chapter.

var sensorData=require('./sensor');
var googleSheetAPI=require('./googlesheet'); 
 var sendInterval = setInterval(function () {
  var dataPacket= new Array(), arrayCounter=0;
  requestPromise(options)
    .then(function (response) {
      var APIresult=JSON.parse(response)
        dataPacket[arrayCounter]=APIresult.wind.speed;
        dataPacket[++arrayCounter]=APIresult.main.pressur;
        dataPacket[++arrayCounter]=APIresult.sys.sunrise;
        dataPacket[++arrayCounter]=APIresult.sys.sunset ;  
    })
    .then(function(){
        sensorData.getSensorData(function(callback){
          var sensorDataResult=JSON.parse(callback);
  dataPacket[++arrayCounter]=sensorDataResult.temp_sens_1
  dataPacket[++arrayCounter]=sensorDataResult.temp_sens_2
  dataPacket[++arrayCounter] =sensorDataResult.humidity ;
  
console.log(dataPacket) ;

          })
    })
    .then(function(){
        googleSheetAPI.updateGoogleSheet(dataPacket)
      })
    .catch(function (err) {
       console.log(err);
  });
},5000)

Here, we have included the sensor and googlesheet modules at the top, which will give us access to the functions to get the data from the sensors and upload all the data to Google sheets.

Let's go through the preceding piece of code step by step and also write two other modules, sensor and googlesheet.

Using setInterval, we make sure that our code will get executed every defined interval of time; in our case, we have set the interval as 5 seconds, so the rest of the code is written inside the setInrerval function.

We declare an array with the name dataPacket, in which all the sensor and weather API data will be stored temporarily to be sent to Google sheets, and a counter, arrayCounter, is used to iterate over the array while inserting data into it.

Now, we make a call to the openweathermap API using requestPromise and passing option as the parameters that contain the uri/api endpoint. After a successful call, it returns a response, as shown in the following screenshot. Using the then functionality of the Node.js promise, we execute the next steps sequentially without getting into callback hell:

Since only a few data points are required, we parse the response as JSON and insert the data points into the array by iterating over it.

Again using the then function, we execute the next step to get the temperature and humidity sensor data interfaced to Raspberry Pi by calling the getSensorData function, itself provided by the sensor module, which has been included at the top. Now, let's create the sensor module and the getSensorData function in it.

Create a new file with the name sensor.js and include the following modules:

• onoff: It gives access to Raspberry Pi gpio
• fs: It enables us to read files
• rpi-dht-sensor: It enables us to read the humidity sensor data through Gpio:

var GPIO= require('onoff').Gpio,
fs= require('fs'),
rpiDhtSensor= require('rpi-dht-sensor');

For DS18B20 one-wire sensors, we need to configure Raspberry Pi by adding a few settings in the config.txt file under the /boot directory. For this, open the terminal and execute these commands:

cd /boot
sudo nano config.txt

This will open up the file, add the following lines to the file, and then close and save the file:

sudo modprobe w1-gpio
sudo modeprobe w1-therm
dtoverlay=w1-gpio

The DS18B20 temperature sensors are interacting with Raspberry Pi over one-wire protocol using the communication bus, as explained in the circuit diagram in an earlier section of this chapter. The data received over this bus from the one-wire sensors can be read from the file located at /sys/bus/w1/devices/. Under devices, each sensor will have a file with its unique serial number, starting with 28. Since the serial number, of one-wire sensors are factory-set, unchangeable, and unique, you can read them beforehand at /sys/bus/w1/devices/ by powering up Raspberry Pi after doing the preceding configuration and connecting the sensor to Raspberry Pi.

In our case, we have read the serial numbers of both sensors beforehand, so we are hardcoding their respective file paths to read the temperature data:

var device1_path='/sys/bus/w1/devices/28-0516a05186ff/';
var device2_path='/sys/bus/w1/devices/28-0517026efdff/'

The DHT11 humidity sensor is connected to GPIO 27, so declare this using the function provided by the rpi-dht-sensor module:

var dht=new rpiDhtSensor.DHT11(27); 

Now, create a getSensorData function in which sensor data will be read and sent back to the calling function in the weatherStation.js file:

module.exports.getSensorData = function(callback){  
   var readout=dht.read()
   var dht11_data=JSON.parse(JSON.stringify(readout)),
     dht11_data_humidity=dht11_data.humidity;  
  var sensor1Data =fs.readFileSync(device1_path+'w1_slave');  sensor1Data=sensor1Data.toString().split('
');
 var s1_line1Array=sensor1Data[0].split(' '),
 s1_line2Array=sensor1Data[1].split('t='),
 s1_raw_Temp=s1_line2Array[1],
s1_temp_C=s1_raw_Temp/1000;
var sensor2Data =fs.readFileSync(device2_path+'w1_slave');
sensor2Data=sensor2Data.toString().split('
');
var s2_line1Array=sensor2Data[0].split(' '),
 s2_line2Array=sensor2Data[1].split('t='),
 s2_raw_Temp=s2_line2Array[1],
 s2_temp_C=s2_raw_Temp/1000;
var sensordataPacket={
                "temp_sens_1": s1_temp_C,
                "temp_sens_2": s2_temp_C,
                "humidity": dht11_data_humidity
 }
sensordataPacket= JSON.stringify(sensordataPacket)   callback(sensordataPacket)
}

We use modules.export to define this function in order to make it available in other modules. Inside the function, the DHT11 humidity sensor data is fetched using the read function provided by the rpi-dht-sensor module. After parsing, humidity data is stored in a variable for later use.

Then, we read out the temperature data for both the DS18B20 temperature sensor from its respective files located at their respective paths declared in the device_path1 and device_path2 variables, using the readFileSync function of the fs module.

The temperature data file looks as follows:

So to read the actual temperature data represented by t = XXXX in temperature data file, we do some string manipulation to convert it into an actual temperature reading in degrees Celsius. Repeat the same steps for the other temperature sensor.

A JSON object is created from the data of all three sensors and sends back to the calling function from the weatherStation.js module through callback. Once we receive the data, we insert each value into the dataPacket array by iterating over it as written in the weatherStation.js module.

Now we execute the final step, to update all the collected data in Google sheets. Make a call to the updateGoogleSheet function from the googlesheets module, which we included in weatherStation.js.

Let's write code for the googlesheets module. Create a new googlesheets.js file and include the required Google sheets modules:

var fs = require('fs');
var readline = require('readline');
var google = require('googleapis');
var googleAuth = require('google-auth-library');

Next we define the scope, which limits the kind of operation that we perform on Google sheets, such as read only and update only, but we keep our scope generic as we perform both read and write operations. Also, a security token will be generated and saved in the sheets.googleapis.com-nodejs-quickstart.json file when we execute the code for the first time. The path of this file will be stored in the TOKEN_PATH variable. This token will be used later to authenticate our read and write request:

var SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
var TOKEN_DIR = (process.env.HOME || process.env.HOMEPATH ||
            process.env.USERPROFILE) + '/.credentials/';
            console.log(TOKEN_DIR)
            var TOKEN_PATH = TOKEN_DIR + 'sheets.googleapis.com-nodejs-quickstart.json';

The storeToken function is used to store the token in the local disk:

function storeToken(token) {
  try {
    fs.mkdirSync(TOKEN_DIR);
  } catch (err) {
    if (err.code != 'EEXIST') {
      throw err;
         }
  }
 fs.writeFile(TOKEN_PATH, JSON.stringify(token));
  console.log('Token stored to ' + TOKEN_PATH);
}

When we execute the step to upload the data to Google sheets for the first time, a new token is generated by the getNewToken function and is stored on our local disk, using the storeToken function we just defined:

function getNewToken(oauth2Client, callback) {
  var authUrl = oauth2Client.generateAuthUrl({
    access_type: 'offline',
    scope: SCOPES
  });
  console.log('Authorize this app by visiting this url: ', authUrl);
  var rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout
  });
  rl.question('Enter the code from that page here: ', function(code) {
    rl.close();
    oauth2Client.getToken(code, function(err, token) {
      if (err) {
        console.log('Error while trying to retrieve access token', err);
        return;
      }
      oauth2Client.credentials = token;
      storeToken(token);
      callback(oauth2Client);
    });
  });
}

We authorize our access to update Google sheets using the token generated through the authorize function:

function authorize(credentials, callback) {
  var clientSecret = credentials.installed.client_secret;
  var clientId = credentials.installed.client_id;
  var redirectUrl = credentials.installed.redirect_uris[0];
  var auth = new googleAuth();
  var oauth2Client = new auth.OAuth2(clientId, clientSecret, redirectUrl);
  // Check if we have previously stored a token.
  fs.readFile(TOKEN_PATH, function(err, token) {
    if (err) {
      getNewToken(oauth2Client, callback);
    } else {
      oauth2Client.credentials = JSON.parse(token);
      callback(oauth2Client);
    }
  });
}

Now, we write the updateSheet function, which will update the Google sheet with the data:

function updateSheet(auth){
 var sheets = google.sheets('v4');
  sheets.spreadsheets.values.append({
    auth: auth,
    spreadsheetId: 'YOUR SPREADSHEET ID',
    range: 'Sheet1!A2:B',
    valueInputOption: "USER_ENTERED",
    resource: {
      values: [dataArray ]
    }
  }, (err, response) => {
    if (err) {
      console.log('The API returned an error: ' + err);
      return;
    } else {
        console.log("Appended");
    }
  });
} 

Finally, we create a updateGoogleSheet function, which will be accessed and called from the WeatherStation.js module. This function takes the client secret generated when the Google API was enabled, and uses it along with the token to complete the authorization, and then updates the Google sheet with the sensor and weather API data:

var dataArray= new Array();
module.exports.updateGoogleSheet= function(data){
    dataArray=data
    fs.readFile('client_secret.json', function processClientSecrets(err, content) {
      if (err) {
        console.log('Error loading client secret file: ' + err);
        return;
      }
        authorize(JSON.parse(content), updateSheet);
    });
}

An array is declared, which will store the data packet passed as a parameter, by calling the function from the weatherStation.js module.

This completes the code part, so now let's execute weatherStation.js. Open the terminal and execute the following command:

sudo node weatheStation.js

It will prompt you with the following message:

Copy the link into the browser and hit Enter. A page will come up and ask for permission; click on ALLOW, as shown in the following screenshot:

It will generate the code shown in the following screenshot:

Copy and paste it into the terminal and press Enter. On successful execution, you will see appended on the console, and the Google sheet will show the updated sensor and weather API data:

Here is a flow diagram that will help us to understand the flow of execution of our code: