-2

I have a code that I use to log temperatures into Google sheets..Code written in Appsscript. In this code, i want to add a function in the case 'temperature' that can send mail in case the incoming temperature to be logged is more than 8.. How can I do that? Someone help in that kind of code?

function doGet(e) { 
  Logger.log( JSON.stringify(e) );
  var result = 'Ok';
  if (e.parameter == 'undefined') {
    result = 'No Parameters';
  }
  else {
    var sheet_id = '1hKXZEIx160TwXi5yUztGBDijNaGgAOjKWSmY-Mowo9M';  // Spreadsheet ID
    //var sheetActive = SpreadsheetApp.openById("ID");
    //var sheet = sheetActive.getSheetByName("Name");
    var sheetActive = SpreadsheetApp.openById(sheet_id);
    var sheet = sheetActive.getSheetByName("Theatre");
    //var sheet = SpreadsheetApp.openById(sheet_id).getActiveSheet().getSheetByName("Sheet3");
    var newRow = sheet.getLastRow() + 1;                        
    var rowData = [];
    var Curr_Date = new Date();
    rowData[0] = Curr_Date; // Date in column A
    var Curr_Time = Utilities.formatDate(Curr_Date, "Africa/Nairobi", 'HH:mm:ss');
    rowData[1] = Curr_Time; // Time in column B
    for (var param in e.parameter) {
      Logger.log('In for loop, param=' + param);
      var value = stripQuotes(e.parameter[param]);
      Logger.log(param + ':' + e.parameter[param]);
      switch (param) {
        case 'temperature':
          rowData[2] = value; // Temperature in column C
          result = 'Temperature Written on column C'; 
          // if(){
          // }
          break;
        // case 'humidity':
        //   rowData[3] = value; // Humidity in column D
        //   result += ' ,Humidity Written on column D'; 
        //   break;  
        default:
          result = "unsupported parameter";
      }
    }
    Logger.log(JSON.stringify(rowData));
    var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
    newRange.setValues([rowData]);
  }
  return ContentService.createTextOutput(result);
}
function stripQuotes( value ) {
  return value.replace(/^["']|['"]$/g, "");
}
MutwiriJosh
  • 1

1 Answers1

0

Email Sent if value greater than 8

function doGet(e) { 
  Logger.log( JSON.stringify(e) );
  var result = 'Ok';
  if (e.parameter == 'undefined') {
    result = 'No Parameters';
  }
  else {
    var sheet_id = '1hKXZEIx160TwXi5yUztGBDijNaGgAOjKWSmY-Mowo9M';  // Spreadsheet ID
    //var sheetActive = SpreadsheetApp.openById("ID");
    //var sheet = sheetActive.getSheetByName("Name");
    var sheetActive = SpreadsheetApp.openById(sheet_id);
    var sheet = sheetActive.getSheetByName("Theatre");
    //var sheet = SpreadsheetApp.openById(sheet_id).getActiveSheet().getSheetByName("Sheet3");
    var newRow = sheet.getLastRow() + 1;                        
    var rowData = [];
    var Curr_Date = new Date();
    rowData[0] = Curr_Date; // Date in column A
    var Curr_Time = Utilities.formatDate(Curr_Date, "Africa/Nairobi", 'HH:mm:ss');
    rowData[1] = Curr_Time; // Time in column B
    for (var param in e.parameter) {
      Logger.log('In for loop, param=' + param);
      var value = stripQuotes(e.parameter[param]);
      Logger.log(param + ':' + e.parameter[param]);
      switch (param) {
        case 'temperature':
          Logger.log(
          rowData[2] = value; // Temperature in column C
          result = 'Temperature Written on column C';  
           Logger.log(`Date: ${new Date()} Value:${value}`);//Please add this
           if(parseInt(value) > 8){
             GmailApp.sendEmail('recipient emal','Subject',"message");
           }
          break;
        // case 'humidity':
        //   rowData[3] = value; // Humidity in column D
        //   result += ' ,Humidity Written on column D'; 
        //   break;  
        default:
          result = "unsupported parameter";
      }
    }
    Logger.log(JSON.stringify(rowData));
    var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
    newRange.setValues([rowData]);
  }
  return ContentService.createTextOutput(result);
}
function stripQuotes( value ) {
  return value.replace(/^["']|['"]$/g, "");
}

GmailApp.sendEmail()

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Worked perfectly. Thank you – MutwiriJosh Nov 12 '21 at 17:59
  • Then I noted it sent once and the temperature was over 9 severally from my google sheet record; Coz I really expected this to send every time the data is being posted and it's more than 9 – MutwiriJosh Nov 12 '21 at 18:20
  • I don't understand what you are saying – Cooper Nov 12 '21 at 18:22
  • My nodeMCU sent values where temperature was over 9 severally. But the email came only once...I thought it will be coming if every post is more than 9 – MutwiriJosh Nov 12 '21 at 18:54
  • Please add the row I have added to the code `Logger.log(`Date: ${new Date()} Value:${value}`);` and provide me with the logging information when you receive data. – Cooper Nov 12 '21 at 19:04
  • I did add it and still working perfectly, but noted that sometimes, the mail skips sending, lets say once – MutwiriJosh Nov 19 '21 at 20:36
  • Like for example, lets take the attached logged data when the temps are over 9 ;11:20:21 7.56 11:19:20 9.13 11:18:20 10.63 11:17:19 10.69 11:16:19 10.56 11:15:19 10.44 11:14:19 10.25 11:13:18 10.13 11:12:18 9.94 11:11:19 9.81 11:10:21 9.63 11:09:17 9.44 11:08:17 9.25 11:07:17 9.06 11:06:16 8.88 In this I have received two mails at 11.09 and 11:13...Can you add something on this....My posting to excel on NodeMCU code is after every one minute – MutwiriJosh Nov 19 '21 at 20:40
  • Sorry, please help... I am here – MutwiriJosh Nov 19 '21 at 22:36