Google Sheets Trickery for Logger

There are a number of needs to get the datalogger to serve as a repository.  This is a collection of Google Scripts for reference to help in the development of the Google Sheet side of the project.

Delete redundant spreadsheets

This was linked to graphic inserted on the spreadsheet for ease of access.  This deletes all the sheets in the file except index [0].

function deleteRedundantSheets() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
Logger.log(sheets.length);
for (i = 1; i < sheets.length; i++) {
  Logger.log(i)
  ss.deleteSheet(sheets[i]);
}
}

Midnight Data operations

This script copies the spreadsheet to the next index over and renames it to the previous days date. I could have pulled the date from the data on the sheet, but just had javascript do it for me. I know my code is clunky but at least it’s functional.

function midnightRestart() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);
  ss.duplicateActiveSheet();
  var first = SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);
  var formatToday = Utilities.formatDate(new Date(), "EST", "YYYYMMdd");
  first.setName(formatToday);
// Now that the new sheet has been created, clear the first columns for new incoming data
  var range = first.getRange("A2:D2000");
  range.clear();
// rename sheet at index 1 to yesterdays date rather than copy of index 0
  var second = SpreadsheetApp.setActiveSheet(ss.getSheets()[1]);

//Redundant error handling for fault tolerance  
  try {
    var yesterDate = yesterdaysDate();
    second.setName(yesterDate);
  } 
  catch(e) {
    try {
      second.setName(yesterDate + " Backup");
    }
    catch(e){
      appendDate = Utilities.formatDate(new Date(), "EST", "HHmmSS");
      second.setName(yesterDate + " Backup " + appendDate);
      }
  }
  SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);
}
function yesterdaysDate() {
  date = subDaysFromDate(new Date(),1)
  Logger.log(date)
  var datestring = Utilities.formatDate(date, "EST", "YYYYMMdd");
  Logger.log(datestring);
  return datestring;
}
function subDaysFromDate(date,d){
  // d = number of day ro substract and date = start date
  var result = new Date(date.getTime()-d*(24*3600*1000));
  return result
}

 

This will give a general idea of the layout of the sheet and backup/error handling of the midnight processing.

 

 

And here it is with some phony testing data.

Advertisements