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