ZENAMART.gs

//This script is provided by Ahmed Shehab<[email protected]> under BSD License

//helpers
function flush(){
  SpreadsheetApp.flush();
}

function get_cuurent_sheet_name() {
  var key = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  return key;
}
function back_to_dashboard(){
 var dashboard = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard")
SpreadsheetApp.setActiveSheet(dashboard)
}

//code
function exportSheetAsJSON() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var numCols = rows.getNumColumns();
  var values = rows.getValues();
  
  var output = "";
  output += "{\""+sheet.getName()+"\" : {\n";
  var header = values[0];
  for (var i = 1; i < numRows; i++) {
    if (i > 1) output += " , \n";
    var row = values[i];
    output += "\""+row[0]+"\" : {";
    for (var a = 1;a<numCols;a++){
      if (a > 1) output += " , ";
         output += "\""+header[a]+"\" : \""+row[a]+"\"";
    }
    output += "}";
    //Logger.log(row);
  }
  output += "\n}}";
  Logger.log(output);
  
  DriveApp.createFile(sheet.getName()+".json", output, MimeType.PLAIN_TEXT);
  
};



function list_total_sales(){
  var output=new Array()
  extract_desired_sheets().forEach(function(sheet) {
    output.push(sheet.getRange("J7").getValue())
  });
  return output
}

function massage_date_str(){
  var MONTHS=["Jan","Feb","Mar", "Apr","May", "Jun","Jul", "Aug","Sep","Oct","Nov","Dec"]
  //sheet,sheet,sheet
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  // Nov-01-19
  var latest_sheet=sheets[sheets.length-1].getName()
  // Nov 01, 2019
  var parsed_latest_sheet=latest_sheet.toString().replace(/[-]{1}/,' ').replace(/[-]/g,', 20')
  var date_from_sheet_name=new Date(parsed_latest_sheet)
  var d=date_from_sheet_name.getDate()+1
  var m=MONTHS[date_from_sheet_name.getMonth()]
  var y=date_from_sheet_name.getYear()
  var latest_sheet_arr= new Array()
  latest_sheet_arr.push(d.toFixed(),m,y.toFixed())
  var date_to_convert=latest_sheet_arr.toString().replace(/[,]{1}/,' ')
  return date_to_convert

}
function auto_create_sheet(){
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var lotto_to_copy="E2:E26"
  var lotto_to_paste="D2:D26"
  var ranges_to_clear=["I11:N18", "M2:M5", "J3:J6"]
  //always set active sheet to last
  // todo: auto cronjob triggers events at 11 pm everyday
  SpreadsheetApp.setActiveSheet(sheets[sheets.length-1])

  var tab_name = Utilities.formatDate(new Date(massage_date_str()),"GMT-6", "MMM-dd-yy");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var current_tab_name=ss.getName()
  //var last_tab_name=Utilities.formatDate(current_tab_name, "GMT-6", "MMM-dd-yy")
  ss.duplicateActiveSheet();
  ss.renameActiveSheet(tab_name);
  var sheet = ss.getSheetByName(tab_name);
  
  sheet.getRange(lotto_to_copy).copyTo(sheet.getRange(lotto_to_paste))
  sheet.getRange(lotto_to_copy).clearContent()
  for ( var i=0; i<ranges_to_clear.length; i++ )
    sheet.getRange(ranges_to_clear[i]).clearContent()
    force_update_dashboard()
}

function cleanup_for_exxon(){
var excludes=["Dashboard","Utils","Nov-09-19"]
var readyArray=new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i++)
    if (excludes.indexOf(sheets[i].getName())<=-1)
      //console.log(sheets[i].getName())
      //readyArray.push(sheets[i])
   //return readyArray
      SpreadsheetApp.getActive().deleteSheet(sheets[i]);
}

function list_total_expense(){
  var output = new Array()
    extract_desired_sheets().forEach(function(sheet) {
      output.push(sheet.getRange("N24").getValue())
    });
  return output

}
function list_total_cash(){
  var output = new Array()
    extract_desired_sheets().forEach(function(sheet) {
      output.push(sheet.getRange("M2").getValue())
    });
  return output 

}

function list_sheet_names(){
  var output = new Array()
    extract_desired_sheets().forEach(function(sheet) {
      output.push(sheet.getName())
    });
  return output

}


function extract_desired_sheets(){
var excludes=["Dashboard","Utils","Guide"]
var readyArray=new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i++)
    if (excludes.indexOf(sheets[i].getName())<=-1)
   readyArray.push(sheets[i])
   return readyArray
}


function force_update_dashboard(){
  var dashboard = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard")
  var dict={
    "B7":"=list_sheet_names()",
    "C7":"=list_total_sales()",
    "D7":"=list_total_expense()",
    "E7":"=list_total_cash()"
  }
  Object.keys(dict).forEach(function(cell){
    dashboard.getRange(cell).clear()
    //13sec extra to give chance for sheet to pickup on change
    flush(); 
    dashboard.getRange(cell).setValue(dict[cell])
    
  })

    

}

//events
function onOpen(e) {
  // Add a custom menu to the spreadsheet.
  SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp, or FormApp.
      .createMenu("⚙ Super Menu ⚙ " )
      .addItem('⚡Create Daily Template', 'auto_create_sheet')
      .addItem('⨠ Dashboard', 'back_to_dashboard')
      .addSeparator()
      .addItem('⟳ Refresh', 'force_update_dashboard')
      .addToUi();
  flush();
}
function onEdit(){
  flush();
}


//trigers
function createTimeDrivenTriggers() {
  // Trigger every Day 1 CST AM to create a new sheet with ending inventory
  ScriptApp.newTrigger('auto_create_sheet')
      .timeBased()
      .everyHours(24)
      .create();
}