15. Program UI’s & Apps

This week I learnt about the many different software tools and languages that can enable us to interface with our embedded systems.
For my final project, I built a simple interface that consist of a script that can retrieve data about my shelf status from ESP32, and populate them on Google Sheets.

This week's assignments (May 1 - May 7):

Group assignment:
- Compare as many tool options as possible.

Individual assignment:
- Write an application for the embedded board that I made, that interfaces a user with an input and/or output device(s)

Groupwork: Compare Tool options

Link to Groupwork

Final Project work: Write an application

This week, I decided I wanted to build an interface for the user to see the readings of my XiaoESP32 board on a Cloud database.

After searching the FabAcademy search engine, I found out that I should be able to enable an ESP32 microcontroller to read from / write on a Google Sheets File. (Source: BoHeatherBowman).

Following this very helpful Youtube tutorial, I managed to build a working interface.

These were the steps I took;
For the Cloud database

1. I created a new Google Sheets document, with necessary headers and appropriate Sheet name.
   
   
   
2. Created a new Google App Script project.
   
   
   
3. Added codes to App Script, starting with the doGet() function.
   
   
   
4. Deployed the app
   I deployed the GoogleSheets (made it public for the time being)
   I then deployed the URL with test parameter values.
   

For the Hardware side

1. I created a simple circuit consisting of 2 push buttons.
2. I wrote the code for ESP32 on Arduino, starting with necessary libraries: Wifi.h and HTTPClient.h. I made sure to include digitalRead of the push-button states, and a code for generating a URL that includes data to populate the Sheets. (Please refer to the bottom of this documentation for the full code).
   
   

Finally, I checked the GoogleSheets to see that data has been added successfully.

AppScript Code:

function doGet(e) { 
  Logger.log(JSON.stringify(e));
  var result = 'Ok';
  if (e.parameter == 'undefined') {
    result = 'No Parameters';
  }
  else {
    var sheet_id = "1cFDzRNi2d91h343LMrNMls-1d2gmEBkTPrZ5-cP9vVY";  // the Spreadsheet ID.
    var sheet_name = "LocationID";  // Sheet Name in Google Sheets.

    var sheet_open = SpreadsheetApp.openById(sheet_id);
    var sheet_target = sheet_open.getSheetByName(sheet_name);

    var newRow = sheet_target.getLastRow() + 1;

    var rowDataLog = [];

    var Data_for_H4;
    var Data_for_I4;
    var Data_for_J4;
    var Data_for_K4;
    var Data_for_L4;
    var Data_for_M4;

    var Curr_Date = Utilities.formatDate(new Date(), "Asia/Tokyo", 'dd/MM/yyyy');
    rowDataLog[0] = Curr_Date;  // Date will be written in column A (in the "DHT11 Sensor Data Logger" section).
    Data_for_H4 = Curr_Date;  // Date will be written in column H4 (in the "Latest DHT11 Sensor Data" section).

    var Curr_Time = Utilities.formatDate(new Date(), "Asia/Tokyo", 'HH:mm:ss');
    rowDataLog[1] = Curr_Time;  // Time will be written in column B (in the "DHT11 Sensor Data Logger" section).
    Data_for_I4 = Curr_Time;  // Time will be written in column I4 (in the "Latest DHT11 Sensor Data" section).

    var sts_val = '';

    for (var param in e.parameter) {
      Logger.log('In for loop, param=' + param);
      var value = stripQuotes(e.parameter[param]); //"param" is a parameter from ESP32
      Logger.log(param + ':' + e.parameter[param]);
      switch (param) {
        case 'sts':
          sts_val = value;
          break;

        case 'srs': // srs: Sensor Reading Status
          rowDataLog[2] = value;  // Sensor Reading Status will be written in column C (in the "DHT11 Sensor Data Logger" section).
          Data_for_J4 = value;  // Sensor Reading Status will be written in column J4 (in the "Latest DHT11 Sensor Data" section).
          result += ', Sensor Reading Status Written on column C';
          break;

        case 'Shelf_ID':
          rowDataLog[3] = value;  // The state of Shelf_ID will be written in column D (in the "DHT11 Sensor Data Logger" section).
          Data_for_K4 = value;  // The state of Switch_1 will be written in column K4 (in the "Latest DHT11 Sensor Data" section).
          result += ', Shelf_ID Written on column D';
          break;

        case 'Slot_ID':
          rowDataLog[4] = value;  // The state of Switch_1 will be written in column E (in the "DHT11 Sensor Data Logger" section).
          Data_for_L4 = value;  // The state of Switch_1 will be written in column L4 (in the "Latest DHT11 Sensor Data" section).
          result += ', Shelf_ID Written on column E';
          break;

        default:
          result += ", unsupported parameter";
      }
    }

    // Conditions for writing data received from ESP32 to Google Sheets.
    if (sts_val == 'write') {
      // Writes data to the "DHT11 Sensor Data Logger" section.
      Logger.log(JSON.stringify(rowDataLog));
      var newRangeDataLog = sheet_target.getRange(newRow, 1, 1, rowDataLog.length);
      // getRange(row, column, numRows, numColumns)
      newRangeDataLog.setValues([rowDataLog]);

      // Write the data to the "Latest DHT11 Sensor Data" section.
      // if ( shelf = 1, slot = 2, get Range H5;M5)

      var RangeDataLatest = sheet_target.getRange('H4:M4');
      RangeDataLatest.setValues([[Data_for_H4, Data_for_I4, Data_for_J4, Data_for_K4, Data_for_L4, Data_for_M4]]);

      return ContentService.createTextOutput(result);
    }

    // Conditions for sending data to ESP32 when ESP32 reads data from Google Sheets.
    if (sts_val == 'read') {
      // Use the line of code below if you want ESP32 to read data from columns H4 to M4 (Date,Time,Sensor Reading Status,Shelf_ID,Slot_ID,ResistanceValue).
      // var all_Data = sheet_target.getRange('H4:M4').getDisplayValues();

      // Use the line of code below if you want ESP32 to read data from columns K4 to M4 (Sensor Reading Status,Temperature,Shelf_ID,SlotID,ResistanceValue).
      var all_Data = sheet_target.getRange('K4:M4').getValues();
      return ContentService.createTextOutput(all_Data);
    }
  }
}
function stripQuotes( value ) {
  return value.replace(/^["']|['"]$/g, "");
}

Arduino Code

#include "WiFi.h"
#include <HTTPClient.h>

#define On_Board_LED_PIN  D6// Defining LED PIN on the ESP32 Board.
#define Shelf_ID_Test D10 //button1
#define Slot_ID_Test D9 //button2
#define ResistanceValue D0// Defines the input pin

//----------------------------------------SSID and PASSWORD of my WiFi network.
const char* ssid = "networkD507";  //--> Your wifi name
const char* password = "Jadore88Pinot"; //--> Your wifi password
//----------------------------------------

// Google script Web_App_URL.
String Web_App_URL = "https://script.google.com/macros/s/AKfycbxP24qntDqSS1fODqmATCoY9ABT22DDce1jY-1gQRuy7Cr_-11rhOGZxmc7OjzyLLv0/exec";

String Status_Read_Sensor = "";
String Shelf_State = "";
String Slot_State ="";
int ResistanceValue_State;
String ResistanceValue_State2 = "";

void setup() {  // put your setup code here, to run once:

  Serial.begin(9600);
  Serial.println();
  delay(1000);

  pinMode(Shelf_ID_Test, INPUT_PULLUP);
  pinMode(Slot_ID_Test, INPUT_PULLUP);

  //----------------------------------------Set Wifi to STA mode
  Serial.println();
  Serial.println("-------------");
  Serial.println("WIFI mode : STA");
  WiFi.mode(WIFI_STA);
  Serial.println("-------------");

  //----------------------------------------Connect to Wi-Fi (STA).
  Serial.println();
  Serial.println("------------");
  Serial.print("Connecting to ");
  Serial.println(ssid);
  WiFi.begin(ssid, password);

  //:::::::::::::::::: The process of connecting ESP32 with WiFi Hotspot / WiFi Router.
  // The process timeout of connecting ESP32 with WiFi Hotspot / WiFi Router is 20 seconds.
  // If within 20 seconds the ESP32 has not been successfully connected to WiFi, the ESP32 will restart.

  int connecting_process_timed_out = 20; //--> 20 = 20 seconds.
  connecting_process_timed_out = connecting_process_timed_out * 2;
  while (WiFi.status() != WL_CONNECTED) {
    Serial.print(".");
    digitalWrite(On_Board_LED_PIN, HIGH);
    delay(250);
    digitalWrite(On_Board_LED_PIN, LOW);
    delay(250);
    if (connecting_process_timed_out > 0) connecting_process_timed_out--;
    if (connecting_process_timed_out == 0) {
      delay(1000);
      ESP.restart();
    }
  }

  digitalWrite(On_Board_LED_PIN, LOW);

  Serial.println();
  Serial.println("WiFi connected");
  Serial.println("------------");

  delay(100);

  Serial.println();
  Serial.println("Begin Input");
  Serial.println();
  delay(1000);  
}

void loop() { 

  Read_Shelf_State();  // Call the "Read_Shelf_State()" subroutine.
  Get_ResistanceValue();  // Call the "Get_ResistanceValue()" subroutine.

  // This is the condition for sending or writing data to Google Sheets.
    if (WiFi.status() == WL_CONNECTED) {
      digitalWrite(On_Board_LED_PIN, HIGH);

      // Create a URL for sending or writing data to Google Sheets.
      String Send_Data_URL = Web_App_URL + "?sts=write";
      Send_Data_URL += "&srs=" + Status_Read_Sensor;
      Send_Data_URL += "&Shelf_ID=" + Shelf_State;
      Send_Data_URL += "&Slot_ID=" + Slot_State;
      Send_Data_URL += "&ResistanceValue=" + ResistanceValue_State2;

      Serial.println();
      Serial.println("-----");
      Serial.println("Send data to Google Spreadsheet...");
      Serial.print("URL : ");
      Serial.println(Send_Data_URL);

      //::::::::::::::::::The process of sending or writing data to Google Sheets.
      // Initialize HTTPClient as "http".
      HTTPClient http;

      // HTTP GET Request.
      http.begin(Send_Data_URL.c_str());
      http.setFollowRedirects(HTTPC_STRICT_FOLLOW_REDIRECTS);

      // Gets the HTTP status code.
      int httpCode = http.GET(); 
      Serial.print("HTTP Status Code : ");
      Serial.println(httpCode);

      // Getting response from google sheets.
      String payload;
      if (httpCode > 0) {
        payload = http.getString();
        Serial.println("Payload : " + payload);    
      }

      http.end();

      digitalWrite(On_Board_LED_PIN, LOW);
      Serial.println("-------------");
    }

  delay(10000);
}

Reflections:

This week I started to understand some basic protocols and programming codes for building UIs that communicate with my microcontroller board. Being new to Software Development, this topic proved to be very challenging, especially as I had almost no instructor support!

The next and major step in my Final Project would be to connect this program with the I2C master/child relationship I deployed in Week 11.

When I have more time, I would like to learn about how to make use of Servers, Memory,
And software languages such as Javascript, Python, etc

Useful links:

• Codes
  
• ESP32 Home
  
• Combine Google Assistant with IFTTT
  
• FireBase Documentation
  

Assignment Checklist:

• Linked to the group assignment page
• Documented my process
• Explained the UI that I made and how I did it
• Explained how my application communicates with my embedded microcontroller board
• Explained any problems I encountered and how I fixed them
• Included original source code (or a screenshot of the app code if that’s not possible)
• Included a ‘hero shot’ of my application running & communicating with my board