Perpetual data logging to Google Sheets

BME280 Logging Script.gs

const sheet_id = "1hBT_nM_UehFrAa7o_lUW-eZEOpnFSgUZqsDaygV7XgQ";

const headers = ['dtstamp', 'temp', 'heatindex', 'humidity', 'dewpoint', 'pressure', 'diff'];

const now = new Date();
const sheetName = `${getMonthNames(now.getMonth())} ${now.getFullYear()}`;
const ss = SpreadsheetApp.openById(sheet_id);
let sheet = ss.getSheetByName(sheetName);

function doGet(e) {  
  
  //Change var name = e.paraameter.value for values to be logged
    
  var dtstamp = e.parameter.dtstamp;
  var temp = e.parameter.temp;
  var heatindex = e.parameter.heatindex;
  var humidity = e.parameter.humidity;
  var dewpoint = e.parameter.dewpoint;
  var pressure = e.parameter.pressure;
  var diff = e.parameter.diff;

  // data = var to be appended to every row of Goole Sheet.
  const data = [dtstamp, temp, heatindex, humidity, dewpoint, pressure, diff];

  // Logs data to the console
  console.log(dtstamp, temp, heatindex, humidity, dewpoint, pressure, diff);

  //Checks for end of the month; if true creates new sheet. 
  if (isEndOfMonth(now)) {
    createNewSheet(sheetName, ss, data);
  } else {
    logData(sheet, data);
  }
}


//Retreves name of month.
function getMonthNames(index) {
  const months = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
  return index !== undefined ? months[index] : months;
}

//Finds date for the end of the month
function isEndOfMonth(date) {
  const endOfMonth = new Date(date.getFullYear(), date.getMonth() + 1, 0);
  return date.getDate() === endOfMonth.getDate();
}

//Creates new sheet with correct month.
function createNewSheet(sheetName, ss, data) {
  let sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
    sheet.appendRow(headers);
  }
  sheet.appendRow(data);
}

//If not end of month date, opens sheet, writes data and apends data to row.
function logData(sheet, data) {
  if (!sheet) {
    const ss = SpreadsheetApp.openById(sheet_id);
    sheet = ss.insertSheet(sheetName);
    sheet.appendRow(headers);
  }
  sheet.appendRow(data);
}

ESP32 Code snippet for sending data to Google Sheets:

// Global
String GOOGLE_SCRIPT_ID = "Put Deployment Id between quotes xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"; // Copy Deployment Id when deploying script.

// Loop


getDateTime();

//Executes 15 Minute routine and one Five Minute Rountine.
if ((MINUTE % 15 == 0) && (SECOND == 0))
{

  flag = 1;

  Serial.println("");
  Serial.println("Fifthteen minute routine");
  Serial.println(dtStamp);

  getWeatherData();

  lastUpdate = dtStamp;   //store dtstamp for use on dynamic web page
  updateDifference();  //Get Barometric Pressure difference
  logtoSD();   //Output toLittleFS  --Log toLittleFS on 15 minute interval.
  delay(10);  //Be sure there is enoughLittleFS write time
  googleSheet();
  webInterface();
  speak();

  watchdogCounter = 0;
  
  flag = 0;

}

//Function
void googleSheet() // Sends data to Google Sheets
{

char fahr[7];
dtostrf(temperature, 6, 1, fahr); 

char heatindex[7];
dtostrf(heat, 6, 1, heatindex); 

char humid[7]; 
dtostrf(hum, 6, 1, humid); 

char dewpoint[7]; 
dtostrf(dew,6, 1, dewpoint); 

char barometric[8]; 
dtostrf(currentPressure, 7, 3, barometric);

char diff[7]; 
dtostrf(difference, 6, 3, diff); 

String data = "&dtstamp="             +  dtStamp

            + "&temp="                +  fahr

            + "&heatindex="           +  heatindex

            + "&humidity="            +  humid

            + "&dewpoint="            +  dewpoint

            + "&pressure="            +  barometric

            + "&diff="                +  diff;

String urlFinal = "https://script.google.com/macros/s/"+GOOGLE_SCRIPT_ID+"/exec?"+data;
Serial.print("POST data to spreadsheet:");
urlFinal.replace(" ", "%20");
Serial.println(urlFinal);
HTTPClient http;
http.begin(urlFinal.c_str());
http.addHeader("Content-Type", "application/x-www-form-urlencoded");  //Specify content-type header
http.setFollowRedirects(HTTPC_STRICT_FOLLOW_REDIRECTS);
int httpCode = http.GET(); 
Serial.print("HTTP Status Code: ");
Serial.println(httpCode);

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

http.end();

}