Google Sheets, Script for logging data in yearly workbook; with monthly log sheets. Updated 12/31/2023

BME280 Data Logging

const sheet_id = "Google Sheet id";

function doGet(e) {
  const dtstamp = e.parameter.dtstamp;
  const temp = e.parameter.temp;
  const heatindex = e.parameter.heatindex;
  const humidity = e.parameter.humidity;
  const dewpoint = e.parameter.dewpoint;
  const pressure = e.parameter.pressure;
  const diff = e.parameter.diff;

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

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

  var now = new Date();
  var month = now.getMonth();
  var monthName = getMonthNames();
  var year = now.getFullYear();
  var endOfMonth = new Date(year, month + 1, 0, 23, 45); // Set end time to 23:45 of last day

  function getMonthNames() {
    var months = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
    var monthName = months[new Date().getMonth()];
    return monthName;
  }

  var sheetName = monthName + " " + now.getFullYear();
  var ss = SpreadsheetApp.openById(sheet_id);
  var sheet = ss.getSheetByName(sheetName);

  function createNewSheet() {
    var month = now.getMonth();
    var monthName = getMonthNames()[month];
    console.log(sheetName);
    var sheet = SpreadsheetApp.getActive().insertSheet(sheetName);
    console.log(sheet);
    sheet.appendRow(headers);
  }

  function logData(dtstamp, temp, heatindex, humidity, dewpoint, pressure, diff) {
    var sheet = SpreadsheetApp.openById(sheet_id).getSheetByName(sheetName);
    if (!sheet) {
      sheet = SpreadsheetApp.openById(sheet_id).insertSheet(sheetName);
      //  Add headers when sheet is created   //added 12/8/2023
      sheet.appendRow(headers);
    }
    if (now < endOfMonth) {
      // Append data only if before end of month
      sheet.appendRow([dtstamp, temp, heatindex, humidity, dewpoint, pressure, diff]);
    }
  }

  function testForEndOfMonth() {
    if (now.getDate() === 1) {
      createNewSheet();
    }
    logData(dtstamp, temp, heatindex, humidity, dewpoint, pressure, diff);
  }

  function createNewWorkbook() {
    var year = new Date().getFullYear();
    var workbookName = "Weather Data - " + year;

    // Check if a workbook already exists for the year
    var existingWorkbook = DriveApp.getFilesByName(workbookName).next();

    if (existingWorkbook) {
      // Open the existing workbook
      SpreadsheetApp.openById(existingWorkbook.getId());
      return;
    }

    // Create a new workbook
    var newWorkbook = SpreadsheetApp.create(workbookName);

    // Add a new sheet for each month
    for (var i = 0; i < 12; i++) {
      var monthName = getMonthNames()[i];
      newWorkbook.insertSheet(monthName + " " + year);
    }

    // Open the new workbook
    SpreadsheetApp.openById(newWorkbook.getId());
  }

  testForEndOfMonth();
}



/*

	Arduino function that sends data to Google Sheet
	
	Library include:
	
	#include <HTTPClient.h>
	
        global variable:

        String GOOGLE_SCRIPT_ID = "removed for security"; //Deployment id
	

void googleSheet()
{
  
    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();
    
}

*/