IoT Pressure Sensor: MKR GSM + Arduino Cloud + Google Sheets

// There are 2 functions in this code
// doPost function - Transmits data from the Arduino Cloud Webhook. It runs when there is new data on the Arduino Cloud.
// sendEmail function - Sends emails based on values extracted from the sheet named "Data" in the Google Sheets file for the project. It runs once every minute based on the settings in the trigger setup. For instructions, refer to the sendEmail function portion of the project hub post.

// The majority of this code (aside from the sendEmail function) is modeled after the following project on the Arduino Project Hub
//      https://create.arduino.cc/projecthub/Arduino_Genuino/arduino-iot-cloud-google-sheets-integration-71b6bc?f=1

// This a link to a GitHub repository with the Google Script used for the above mentioned project on the Project Hub. 
// This link was copied from the desription of the project on the Project Hub.
//      https://github.com/arduino/arduino-iot-google-sheet-script/blob/master/Code.gs


// get active spreasheet
var ss = SpreadsheetApp.getActiveSpreadsheet();

// get sheet named RawData
var sheet = ss.getSheetByName("RawData");
var sd = ss.getSheetByName("Data");
var sref =  ss.getSheetByName("References");

var MAX_ROWS = 1440;     // max number of data rows to display
// 3600s / cloud_int(30s) * num_ore(12h) = (60*60*12)/30 = (3600*12)/30 = 1440 readings in 12 hours at 30 second update interval
// (60*24)/15 = 96 readings in a 24 hour period at 15 minute update interval
// 15 days * 96 readings/day = 1440 readings
// 90 days * 96 readings/day = 8640 readings
// 365 days * 96 readings/day = 35040 readings
var HEADER_ROW = 1;     // row index of header
var TIMESTAMP_COL = 1;  // column index of the timestamp column

function doPost(e) {  
  var cloudData = JSON.parse(e.postData.contents); // this is a json object containing all info coming from IoT Cloud
  console.log(cloudData);
  //var webhook_id = cloudData.webhook_id; // really not using these three
  //var device_id = cloudData.device_id;
  //var thing_id = cloudData.thing_id;
  var values = cloudData.values; // this is an array of json objects
  console.log(values);
  
  // Store names and values from the values array
  // Each incoming property has a: 
  //      name which will become the column names
  //      value which will be written into the rows below the column header 
  var incLength = values.length;
  var incNames = [];
  var incValues = [];
  for (var i = 0; i < incLength; i++) {
    incNames[i] = values[i].name;
    incValues[i] = values[i].value;
  }
  
  console.log(incNames);
  console.log(incValues);
  
  // read timestamp of incoming message
  var timestamp = values[0].updated_at;          // format: yyyy-MM-ddTHH:mm:ss.mmmZ
  var date = new Date(Date.parse(timestamp)); 
  
  /*
  This if statement is due to the fact that duplicate messages arrive from the cloud!
  If that occurs, the timestamp is not read correctly and date variable gets compromised.
  Hence, execute the rest of the script if the year of the date is well defined and it is greater
  then 2018 (or any other year before)
  */
  if (date.getYear() > 2018) {
  
    // discard all messages that arrive 'late'
    if (sheet.getRange(HEADER_ROW+1, 1).getValue() != '') { 
      // HEADER_ROW + 1 = Row #2 & Column #1 --> this is the location of the most recent timestamp in the sheet
      // If the most recent timestamp is not blank = (''), then compare the current time to the timestamp of the incoming data
      // If the most recent timestamp is blank, it is most likely the first time the script is run.
      //      In this case skip this if statement and proceed to write in the column headers and data. 
      //      It doesnt matter if the data is arriving late (current time vs timestamp of incoming data).
      var now = new Date(); // now
      var COMM_TIME = 120; // Note: changed to 120 to allow more messages to come through, previously set at 5 seconds & worked ok // rough overestimate of communication time between cloud and app
      if (now.getTime() - date.getTime() > COMM_TIME * 1000) {
        // If the difference between the current time and the timestamp is greater than 5 seconds, discard the data. 
        // When the condition in this If statement evaluates true, the funtion will stop due to the return statement.
        return; // "The return statement stops the execution of a function and returns a value from that function."
      }
    }
    
    // This section writes values to the header row based on the names of the incoming properties
    //      In other words, this section creates the column names
    
    // Assign a name to the cell that is in the header row & in the first column = timestamp
    sheet.getRange(HEADER_ROW, 1).setValue('timestamp');
    
    for (var i = 0; i < incLength; i++) {
      var lastCol = sheet.getLastColumn(); // at the very beginning this should return 1 // second cycle -> it is 2
      // column 1 is the timestamp column. 
      if (lastCol == 1) {
        // write the column names beginning with the column after lastCol == 1 which is the timestamp column
        // incNames is an array containing the names of all of the incoming properties
        // If lastCol == 1, write the value from 'i'th location in the incNames array to the header row in column #2 = lastCol + 1 
        sheet.getRange(HEADER_ROW, lastCol + 1).setValue(incNames[i]);
      } else {
        // evaluated if the lastCol != 1
        // check if the name is already in header
        var found = 0;
        for (var col = 2; col <= lastCol; col++) {
          // starting with column 2, iterate through all of the columns up to the lastCol evaluating the if statement enclosed
          if (sheet.getRange(HEADER_ROW, col).getValue() == incNames[i]) {
            // the condition of this If statement compares the value in the header row & column # = col to the 'i'th value in the array of incoming property names
            // This if statement is evaulated for each iteration of the for loop that it is enclosed in. 
            //     The condition is evaluated for all of the columns from column #2 to the last column. 
            //     It is checking to see if the 'i'th value in the incNames array exists in any of the columns in the header row.
            //     If the 'i'th value in the incNames array finds a match to any of the values in the header row, set found = 1 & exit the for loop with the break statment. 
            found = 1;
            break; // "The break statement breaks the loop and continues executing the code after the loop"
          } // close if statement evaluated for each iteration of the for loop that it is enclosed in.
        } // close for loop to check the 'i'th value in the incNames array to the values in the header row. 
        if (found == 0) {
          // This If statemnt will be evaluated after the preceeding for loop has completed. 
          //       If found == 0 it means that the 'i'th value in the incNames array did not match any of the existing values in the header row. 
          //       If found == 0, write the 'i'th value in the incNames array to the column after the last column.
          //       If new properties are added to the incoming data over time, the existing columns will not be impacted. The new property will be added to the column after the last column.
          sheet.getRange(HEADER_ROW, lastCol+1).setValue(incNames[i]);
        } // close if statement
      } // close else, since this is the end of the code block inside the main for loop, the next i will be evaluated up to i = incLength
      // The block of code inside this for loop is evaluated for each value at location i in the incNames array. 
      //     The values of i range from 0 to incLength (the number of values in the names array)
      //     In JavaScript the index in arrays starts at 0. In other words, the 1st value in the array is at location = 0.
    } // close main for loop used to write column names (assigning values from names array to header row)
    
    // redefine last coloumn and last row since new names could have been added
    var lastCol = sheet.getLastColumn();
    var lastRow = sheet.getLastRow();
    
    // delete last row to maintain constant the total number of rows
    if (lastRow > MAX_ROWS + HEADER_ROW - 1) { 
      sheet.deleteRow(lastRow);
    }
    
    // insert new row after deleting the last one
    sheet.insertRowAfter(HEADER_ROW);
    
    // reset style of the new row, otherwise it will inherit the style of the header row
    var range = sheet.getRange('A2:Z2');
    //range.setBackground('#ffffff');
    range.setFontColor('#000000');
    range.setFontSize(10);
    range.setFontWeight('normal');
    
    // write the timestamp
    sheet.getRange(HEADER_ROW+1, TIMESTAMP_COL).setValue(date).setNumberFormat("yyyy-MM-dd HH:mm:ss");
    
    // write values in the respective columns
    for (var col = 1+TIMESTAMP_COL; col <= lastCol; col++) {
      // for loop to assign the value from incValues to the approrpriate column 
     
      // This block of code was replaced by an if statement checking for blank values after the incoming data is populated.
      //      Copy previous values 
      //      This is to avoid empty cells if not all properties are updated at the same time
      //      sheet.getRange(HEADER_ROW+1, col).setValue(sheet.getRange(HEADER_ROW+2, col).getValue());
      
      for (var i = 0; i < incLength; i++) {
        // for loop to identify the appropriate column to assign the value 
        
        // get the value in the header row in column = col
        var currentName = sheet.getRange(HEADER_ROW, col).getValue(); 
        
        if (currentName == incNames[i]) {
          // When the condition of this If statement evaluates as true, the 'i'th value in the incValues array will be assigned to row #2 (HEADER_ROW + 1) in column = col
          
          // turn boolean values into 0/1, otherwise google sheets interprets them as labels in the graph
          if (incValues[i] == true) {
            incValues[i] = 1;
          } else if (incValues[i] == false) {
            incValues[i] = 0;
          } // close else if to convert boolean values to 0/1
                    
          sheet.getRange(HEADER_ROW+1, col).setValue(incValues[i]);
        } // close if checking for a match of the value in the header row for column = col to the 'i'th value in the incNames array 
      } // close for loop run for each value in the incNames array
      
      if(sheet.getRange(HEADER_ROW+1, col).getValue() == ''){
        // If statement to check for blank values.
        // If the condition is true the previous value will be copied.
        //    Option 1: copy the previous value if the incoming value is blank. This typicaly works, but sometimes the change in the value will not be captured in the transfer due to filtering of incoming messages to avoid duplicates. This can result in an old value getting carried forward
        //      sheet.getRange(HEADER_ROW+1, col).setValue(sheet.getRange(HEADER_ROW+2, col).getValue());
        //    Option 2: Insert a dash if the incoming value is blank.
        sheet.getRange(HEADER_ROW+1, col).setValue("-");
      } // close If statment used to copy the previous value if the value is blank. Since the values are only sent when they change, this carries forward the values that didn't change.
      
    } // close for loop run for each column from col #2 (TIMESTAMP_COL + 1) to the lastCol
  
  } // close if statment with the condtion (date.getYear() > 2018), used to eliminate dupicate messages from the Arduino Cloud
} // close doPost function


function sendEmail (){
  var emailAddress = sd.getRange("V3").getValue();

  var lastPressure = sd.getRange("K3").getValue(); // pressure at last update
  var lastUpdate = sd.getRange("A3").getValue(); // datetime of last update
  var ssLink = "https://docs.google.com/spreadsheets/d/1XwCir2Llw8RvGPGgZI3Yk6U5a3LeIfUACNuO1Gr_LFQ/edit#gid=1123486497"; 
  
  var triggerAlertCount = sd.getRange("L6").getValue();
  var triggerMonitor = sd.getRange("M3").getValue();
  var dtLastStatusChange = sd.getRange("P3").getValue();

  var dtLastDeviceUpdate1 = sd.getRange("S3").getValue();
  var dtLastDeviceUpdate2 = sd.getRange("S4").getValue();
  
  var emailSentNoUpdate = sd.getRange("T3").getValue();
  var emailSentStartedReading = sd.getRange("U3").getValue();
  
  var message = "Last Device Update: " + "\n" + lastUpdate + "\n\n" + " Last Pressure: " + "\n\t" + lastPressure.toFixed(2) + " psi" + "\n\n" + " Link to Spreadsheet: " + "\n\t" + ssLink;
  
  if(triggerMonitor == 0){
    sd.getRange("L5").setValue(0);
    sd.getRange("L6").setValue(0);
  }
  
  if(triggerMonitor == -1 && triggerAlertCount <= 4){
    sd.getRange("L3").setValue(lastUpdate); // emailSent
    sd.getRange("L5").setValue(-1);
    sd.getRange("L6").setValue(triggerAlertCount + 1);
    var subject = "Status Change Alert - Outside Setpoints";
    MailApp.sendEmail(emailAddress, subject, message);
  }
  
  if(triggerMonitor == 1 && triggerAlertCount <= 4){
    sd.getRange("L3").setValue(lastUpdate); // emailSent
    sd.getRange("L5").setValue(1); 
    sd.getRange("L6").setValue(triggerAlertCount + 1);
    var subject = "Status Change Alert - Normal";
    MailApp.sendEmail(emailAddress, subject, message);
  }
  
  if(emailSentNoUpdate == 0 && dtLastDeviceUpdate1 > 60 && dtLastDeviceUpdate2 > 60){
    sd.getRange("T3").setValue(1); // emailSentNoUpdate
    sd.getRange("U3").setValue(0); // emailSentStartedReading
    sd.getRange("T4").setValue(now.getTime()); // emailSentNoUpdate
    var subject = "Alert - Over 60 minutes Since Last Device Update";
    MailApp.sendEmail(emailAddress, subject, message);
  }
  
  if(emailSentNoUpdate == 1 && dtLastDeviceUpdate1 < 60){  // removed the following from the condition: && dtLastDeviceUpdate2 > 60
    sd.getRange("T3").setValue(0); // emailSentNoUpdate
    sd.getRange("U3").setValue(1); // emailSentStartedReading
    sd.getRange("U4").setValue(now.getTime()); // emailSentStartedReading
    var subject = "Alert - Device Started Updating";
    MailApp.sendEmail(emailAddress, subject, message);
  }

}