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.

 

Clipboard01

 

And here it is with some phony testing data.

Clipboard01

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s