Hackster is hosting Hackster Holidays, Ep. 6: Livestream & Giveaway Drawing. Watch previous episodes or stream live on Monday!Stream Hackster Holidays, Ep. 6 on Monday!
Kutluhan Aktar
Published © CC BY

ORP Water Quality Detector Creating Charts in Google Sheets

Track the water quality of different sources by location via SIM808 to avert the detrimental effects of water pollution on the immune system

ExpertFull instructions provided4 hours6,644
ORP Water Quality Detector Creating Charts in Google Sheets

Things used in this project

Hardware components

Arduino UNO
Arduino UNO
×1
DFRobot Gravity: Analog ORP Sensor Meter
×1
SIM808 GPS/GPRS/GSM Shield For Arduino
DFRobot SIM808 GPS/GPRS/GSM Shield For Arduino
×1
DFRobot 3-wire Serial LCD Module
×1
Analog joystick (Generic)
×1
Battery (7V - 23V)
×1
SIM Card
×1
Jumper wires (generic)
Jumper wires (generic)
×1

Software apps and online services

Arduino IDE
Arduino IDE
Google Sheets
Google Sheets
Google Client Library for PHP

Hand tools and fabrication machines

Hot glue gun (generic)
Hot glue gun (generic)

Story

Read more

Custom parts and enclosures

Guidelines for Drinking-water Quality Fourth Edition

Schematics

SIM808

Analog ORP Sensor

* Connect to A0

3-Wire Serial LCD

Code

ORP_Water_Quality_Detector.ino

Arduino
          ///////////////////////////////////////////////////////  
         //       ORP Water Quality Detector Creating         //
        //           Data Charts in Google Sheets           //
       //                    w/ SIM808                      //
      //           -------------------------------         //
     //                     Arduino Uno                   //           
    //                   by Kutluhan Aktar               // 
   //                                                   //
  ///////////////////////////////////////////////////////

// Track the water quality of different sources by location via SIM808 to avert the detrimental effects of water pollution on the immune system.
// 
// For more information:
// https://www.theamplituhedron.com/projects/ORP-Water-Quality-Detector-Creating-Data-Charts-in-Google-Sheets-with-SIM808/
//
// Connections
// Arduino Uno:           
//                               SIM808 GPS/GPRS/GSM Shield For Arduino
// D0 --------------------------- RX
// D1 --------------------------- TX
// D12 -------------------------- POWER
//                               Gravity: Analog ORP Sensor Meter
// A0 --------------------------- A
// 5V --------------------------- +
// GND -------------------------- -
//                               3-wire Serial LCD Module
// D2 --------------------------- SCK
// D7 --------------------------- CS
// D10 -------------------------- SID
// 5V --------------------------- VCC
// GND -------------------------- GND
//                               JoyStick Module
// A1 --------------------------- UAX
// A2 --------------------------- UAY
// D3 --------------------------- SW
// 5V --------------------------- 5V
// GND -------------------------- GND


// Include required libraries:
#include <DFRobot_sim808.h>
#include "LCD12864RSPI.h"

// Define the sim808.
DFRobot_SIM808 sim808(&Serial);

// Define the JoyStick pins to control the interface.
#define JX A1
#define JY A2
#define J_KEY 3

// Define the default variables of the ORP sensor.
#define VOLTAGE 5.00    //system voltage
#define OFFSET 0        //zero drift voltage
#define ArrayLenth  40  //times of collection
#define orpPin A0       //orp meter output,connect to Arduino controller ADC pin
int orpArray[ArrayLenth];
int orpArrayIndex=0;
double orpValue;

// Define the data holders:
int X, Y, KEY;
String Date, Time, Latitude, Longitude, ORP;
unsigned char _Date[10], _Time[10], _Latitude[10], _Longitude[10], _ORP[16];
char web_hook[75];

// Define interface menu options.
volatile boolean GPS_Screen = false;
volatile boolean ORP_Screen = false;

void setup() {
  Serial.begin(9600);
  pinMode(J_KEY, INPUT);
  digitalWrite(J_KEY, HIGH);
  
  // Initiate the SPI LCD Screen with the given pins (2, 7, 10).
  LCDA.initDriverPin(2,7,10); 
  LCDA.Initialise(); // INIT SCREEN  
  delay(100);

  //******** Initialize sim808 module *************
  while(!sim808.init()) {
     delay(1000);
     Serial.print("Sim808 init error\r\n");
     LCDA.DisplayString(0,0,"Init Error",12);
  }
  delay(2000);
  LCDA.CLEAR();
  // Continue if the SIM808 Module is working accurately.
  Serial.println("Sim808 init success");
  LCDA.DisplayString(0,0,"Init Success",12);
  delay(5000);
  LCDA.CLEAR();
  
  //************* Turn on the GPS power************
  if(sim808.attachGPS()){
      // Display a notification message depending on the status of the GPS.
      Serial.println("Open the GPS power success");
      LCDA.DisplayString(0,0,"GPS Success",11);
  }else{ 
      Serial.println("Open the GPS power failure");
      LCDA.DisplayString(0,0,"GPS Failure",11);
  }
  delay(2000);
  LCDA.CLEAR();
}

void loop() {
  read_joystick();

  interface();

  if(GPS_Screen == true){
    LCDA.CLEAR();
    while(GPS_Screen == true){
        read_joystick();
        // GPS
        get_GPS_Data();
        // Print the data generated by the GPS.
        LCDA.DisplayString(0,0,_Date,10);
        LCDA.DisplayString(1,0,_Time,8);
        LCDA.DisplayString(2,0,_Latitude,9);
        LCDA.DisplayString(3,0,_Longitude,9);
        // Return to the home screen.
        if(Y <= 200){GPS_Screen = false; LCDA.CLEAR(); }
    }
  }

  if(ORP_Screen == true){
    LCDA.CLEAR();
    while(ORP_Screen){
      read_joystick();
      // ORP Sensor
      read_ORP_Sensor();
      // Print the ORP value.
      LCDA.DisplayString(0,0,"   ORP Value:  ",16);
      LCDA.DisplayString(1,0,_ORP,13);
      LCDA.DisplayString(3,0,"   Send [OK]   ",16);
      // Send the ORP value generated by the ORP sensor and the GPS location to the Google Sheets.
      // Inspect the project page to get more information about the webhook.
      if(KEY == 0){
        LCDA.CLEAR();
        // Execute this funtion to send data via the webhook.
        send_data_to_Google_Sheets();
      }
      // Return to the home screen.
      if(Y <= 200){ORP_Screen = false; LCDA.CLEAR(); }
    }
  }
  
}

void read_joystick(){
  // Get X-Axis, Y-Axis, and SW button values.
  X = analogRead(JX);
  Y = analogRead(JY);
  KEY = digitalRead(J_KEY);
}

void interface(){
  // Select menu options.
  if(X >= 1000){ GPS_Screen = true; ORP_Screen = false; }
  if(X <= 200){ GPS_Screen = false; ORP_Screen = true; }
  // Home Screen
  LCDA.DisplayString(0,0,"ORP Water", 9);
  LCDA.DisplayString(1,0,"Quality Detector", 16);
  LCDA.DisplayString(3,0,"<- ORP    GPS ->", 16);
}

void get_GPS_Data(){ 
   //************** Get GPS data *******************
   if(sim808.getGPS()){
    Date = String(sim808.GPSdata.month) + "/" + String(sim808.GPSdata.day) + "/" + String(sim808.GPSdata.year);
    Time = String(sim808.GPSdata.hour) + ":" + String(sim808.GPSdata.minute) + ":" + String(sim808.GPSdata.second);
    Latitude = sim808.GPSdata.lat;
    Longitude = sim808.GPSdata.lon;
    // Convert strings to unsigned char arrays to be able to print them on the SPI LCD Screen.
    String_to_UnChar(Date, 10, _Date);
    String_to_UnChar(Time, 8, _Time);
    String_to_UnChar(Latitude, 9, _Latitude);
    String_to_UnChar(Longitude, 9, _Longitude);
    //************* Turn off the GPS power ************
    sim808.detachGPS();
   }else{
    Date = "Error";
    Time = "Error";
    Latitude = "Error";
    Longitude = "Error";
    // Convert strings to unsigned char arrays to be able to print them on the SPI LCD Screen.
    String_to_UnChar(Date, 10, _Date);
    String_to_UnChar(Time, 8, _Time);
    String_to_UnChar(Latitude, 9, _Latitude);
    String_to_UnChar(Longitude, 9, _Longitude);
   }
}

void send_data_to_Google_Sheets(){
  //*********** Attempt DHCP *******************
  while(!sim808.join(F("cmnet"))) {
      Serial.println("Sim808 join network error");
      LCDA.DisplayString(0,0,"Network Error", 13);
      delay(2000);
  }
  //************ Successful DHCP ****************
  delay(5000);
  LCDA.CLEAR();
  LCDA.DisplayString(0,0,"Successful DHCP", 15);
  //*********** Establish a TCP connection ************
  if(!sim808.connect(TCP,"theamplituhedron.com", 80)) { // Change it with your server.
      LCDA.DisplayString(1,0,"Connection Error", 16);
  }else{
      LCDA.DisplayString(1,0,"Connection OK", 13);
  }
  delay(2000);
  //*********** Send a GET request *****************
  String line = "GET /sheets/webhook.php?location=" + Latitude + "," + Longitude + "&water_quality=" + ORP + " HTTP/1.0\r\n\r\n";
  char buffer[512];
  // Convert the webhook from string to char array to make an HTTP Get Request with the SIM808.
  String_to_Char(line, 75, web_hook);
  sim808.send(web_hook, sizeof(web_hook)-1);
  while (true) {
      int ret = sim808.recv(buffer, sizeof(buffer)-1);
      if (ret <= 0){
          LCDA.DisplayString(2,0,"Fetch Over...", 13);
          break; 
      }
      // Uncomment to view the response in the serial monitor.
      /*
      buffer[ret] = '\0';
      Serial.print("Recv: ");
      Serial.print(ret);
      Serial.print(" bytes: ");
      Serial.println(buffer);
      */
      LCDA.DisplayString(2,0,"Data Send", 9);
      break;
  }
  //************* Close TCP or UDP connections **********
  sim808.close();
  //*** Disconnect wireless connection, Close Moving Scene *******
  sim808.disconnect();
  // Exit.
  delay(5000);
  LCDA.CLEAR();
}

void read_ORP_Sensor(){
  static unsigned long orpTimer=millis();   //analog sampling interval
  static unsigned long printTime=millis();
    if(millis() >= orpTimer){
      orpTimer=millis()+20;
      orpArray[orpArrayIndex++]=analogRead(orpPin);    //read an analog value every 20ms
      if (orpArrayIndex==ArrayLenth) {
        orpArrayIndex=0;
      }   
      orpValue=((30*(double)VOLTAGE*1000)-(75*avergearray(orpArray, ArrayLenth)*VOLTAGE*1000/1024))/75-OFFSET;   //convert the analog value to orp according the circuit
    }
    if(millis() >= printTime){   //Every 800 milliseconds, print a numerical
      printTime=millis()+800;
      ORP = String((int)orpValue);
      String line = "      " + ORP + "     ";
      // Convert the data from string to unsingned char to be able to print it.
      String_to_UnChar(line, 16, _ORP);
  }

}

  double avergearray(int* arr, int number){
    int i;
    int max,min;
    double avg;
    long amount=0;
    if(number<=0){
      printf("Error number for the array to avraging!/n");
      return 0;
    }
    if(number<5){   //less than 5, calculated directly statistics
      for(i=0;i<number;i++){
        amount+=arr[i];
      }
      avg = amount/number;
      return avg;
    }else{
      if(arr[0]<arr[1]){
        min = arr[0];max=arr[1];
      }
      else{
        min=arr[1];max=arr[0];
      }
      for(i=2;i<number;i++){
        if(arr[i]<min){
          amount+=min;        //arr<min
          min=arr[i];
        }else {
          if(arr[i]>max){
            amount+=max;    //arr>max
            max=arr[i];
          }else{
            amount+=arr[i]; //min<=arr<=max
          }
        }//if
      }//for
      avg = (double)amount/(number-2);
    }//if
    return avg;
  }

void String_to_UnChar(String _String, int _size, unsigned char _convert[]){
  for(int i=0;i<_size;i++){
    _convert[i] = _String[i];
  }
}

void String_to_Char(String _String, int _size, char _convert[]){
  for(int i=0;i<_size;i++){
    _convert[i] = _String[i];
  }
}

webhook.php

PHP
<?php
require_once $_SERVER['DOCUMENT_ROOT']."google-api-php-client-2.4.1/vendor/autoload.php"; // Google Client API v2.0
require_once "account_verification_token.php"; // Get the verification code.

// Get the variables (location and water quality).
$variables_from_module;
if(isset($_GET['location']) && isset($_GET['water_quality'])){
	$variables_from_module = [
	  "location" => $_GET['location'],
	  "water_quality" => (int)$_GET['water_quality']
    ];
}else{
    $variables_from_module = [
	  "location" => "ERROR",
	  "water_quality" => 0
    ];
}


/**
 * Returns an authorized API client.
 * @return Google_Client the authorized client object
 */
function getClient()
{
    $client = new Google_Client();
    $client->setApplicationName('Water Quality Detector'); // Enter your application name.
    $client->setScopes('https://www.googleapis.com/auth/spreadsheets');
    $client->setAuthConfig('credentials.json');
    $client->setAccessType('offline');
    $client->setPrompt('select_account consent');

    // Load previously authorized token from a file, if it exists.
    // The file token.json stores the user's access and refresh tokens, and is
    // created automatically when the authorization flow completes for the first
    // time.
    $tokenPath = 'token.json';
    if (file_exists($tokenPath)) {
        $accessToken = json_decode(file_get_contents($tokenPath), true);
        $client->setAccessToken($accessToken);
		print("Token Found!");
    }

    // If there is no previous token or it's expired.
    if ($client->isAccessTokenExpired()) {
        // Refresh the token if possible, else fetch a new one.
        if ($client->getRefreshToken()) {
            $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
        } else {
            // Request authorization from the user.
            $authUrl = $client->createAuthUrl();
			// Do not forget to refresh the page after getting the verification code and entering it to the account_verification_token.php.
            printf("Open the following link in your browser:<br><br>%s<br><br>", $authUrl); // <= Comment
            print 'Do not forget to refresh the page after getting the verification code and entering it to the account_verification_token.php.<br><br>Set the verification code in the account_verification_token.php file.'; // <= Comment
            // Set the verification code to create the token.json.
			$authCode = trim($GLOBALS['account_verification_token']);

            // Exchange authorization code for an access token.
            $accessToken = $client->fetchAccessTokenWithAuthCode($authCode);
            $client->setAccessToken($accessToken);

            // Check to see if there was an error.
            if (array_key_exists('error', $accessToken)) {
                throw new Exception(join(', ', $accessToken));
            }else{
				print("Successful! Refresh the page.");
			}
        }
        // Save the token to a file.
        if (!file_exists(dirname($tokenPath))) {
            mkdir(dirname($tokenPath), 0700, true);
        }
        file_put_contents($tokenPath, json_encode($client->getAccessToken()));
    }
    return $client;
}


// Get the API client and construct the service object.
$client = getClient();
$service = new Google_Service_Sheets($client);

// Enter your spreadsheetId:
$spreadsheetId = '[Spreadsheet_ID]';
// Enter the range (the first row) under which new values will be appended:
$range = 'A1:B1';
// Append recent findings (location and water quality) to the spreadsheet.
$values = [
    [$variables_from_module["location"], $variables_from_module["water_quality"]]
];
$body = new Google_Service_Sheets_ValueRange([
    'values' => $values
]);
$params = [
    'valueInputOption' => "RAW"
];
// Append if only requested!
if(isset($_GET['location'])){
    $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);
    printf("<br><br>%d cells appended.", $result->getUpdates()->getUpdatedCells());
}

account_verification_token.php

PHP
<?php $account_verification_token = "[Enter_Token]"; // Enter the verification code after authorization. ?>

Credits

Kutluhan Aktar

Kutluhan Aktar

82 projects • 310 followers
AI & Full-Stack Developer | @EdgeImpulse | @Particle | Maker | Independent Researcher

Comments

Related channels and tags
  • COVID-19
  • COVID-19
  • DFRobot
  • Environmental Sensing
  • Environmental Sensing
  • Google
  • Internet of Things
  • Internet of Things
  • Sensors
  • Sensors